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 

declare page_id bigint;
start transaction;

select ifnull(max(c_id)+1,1) into page_id from table2;

*insert ignore into table1

*insert ignore into table2



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

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