List:General Discussion« Previous MessageNext Message »
From:Adarsh Sharma Date:February 22 2012 11:33am
Subject:Removing Data Duplicacy
View as plain text  
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




Thread
Removing Data DuplicacyAdarsh Sharma22 Feb
  • Re: Removing Data DuplicacyJohan De Meersman22 Feb
    • Re: Removing Data DuplicacyArthur Fuller22 Feb
      • Re: Removing Data DuplicacyJohnny Withers22 Feb
      • Re: Removing Data DuplicacyAdarsh Sharma23 Feb
        • Re: Removing Data DuplicacyJohan De Meersman23 Feb
Re: Removing Data DuplicacyJohnny Withers22 Feb
  • Re: Removing Data DuplicacyJohan De Meersman22 Feb