Dear all,
I have two tables in my database Table 1 & Table2. My applcation inserts
data in these two tables by an muti threaded application in which many
connections are made to these tables and data is inserted. I cannot make
c_id column primary key auto_increment due to business logic. My
procedure inserts data after adding 1 to previous id.
Today I noticed some duplicacy in my c_id column which is not possible,
because other column values are different on same c_id.
It means many clients read max c_id & insert it with next c_id in the table.
CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_table2`(url_level
tinyint,table2_size int,master_id bigint,table2_type varchar(50),
page_m_date varchar(70),anchortext varchar(150),url_heading
varchar(250),page_url varchar(500),source mediumtext,content_data
mediumtext,page_category varchar(150),table2_language
varchar(10),url_title varchar(250), referred_url varchar(500), keyword
varchar(500))
begin
declare page_id bigint;
start transaction;
select ifnull(max(c_id)+1,1) into page_id from table2;
*insert ignore into table1
(c_id,link_level,content_size,url_id,content_type,page_modified_date,anchor_text,heading,crawled_page_url,source_code,title,referring_url)
values
(page_id,url_level,table2_size,master_id,table2_type,page_m_date,anchortext,ifnull(url_heading,anchortext),page_url,source,url_title,referred_url);*
*insert ignore into table2
(c_id,link_level,category,content_language,url_id,heading,crawled_page_url,content,publishing_date,keywords)
values
(page_id,url_level,page_category,table2_language,master_id,ifnull(url_heading,anchortext),page_url,content_data,page_m_date,keyword);*
commit;
end
I need multiple client select cid from 2 tables & insert data with
adding 1 to previous C_id in isolated manner.
I think I need to use lock statements in my procedure.
Can anyone suggest how to solve the issue.
Thanks in Advance
