List:General Discussion« Previous MessageNext Message »
From:Adarsh Sharma Date:February 23 2012 4:56am
Subject:Re: Removing Data Duplicacy
View as plain text  
Thanks Johan,  but i mentioned before that adding auto increment column 
doesn't solve the issue & it causes errors in the multi threaded 
Multiple clients calls this insert procedure simultaneously, so it fails 
the transactions if two or more clients reads the same ID value.
I need to  insert row simultaneously with the same C_ID into two tables 
( table 1 & table 2).

Please let me know if the below solution is genuine or not, I update my 
procedure & create a table that contains only ID.

*Solution 1 *: *Create a PK Table & Lock it when calling the Insert 

create table *pk_table*( c_id bigint unsigned NOT NULL) ENGINE=InnoDB ;
insert into pk_table values (219846925);          ( Last ID inserted in 
the Table1 & Table 2);

*Modified Insert Procedure :-*

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_table`(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;
*lock table pk_table write;*

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

insert ignore into table1

insert ignore into table2

*UPDATE pk_table set c_id=LAST_INSERT_ID(c_id+1);*

unlock tables;


Best Regards.

Arthur Fuller wrote:
> I agree with the testicular remedy, but in the case of the iron 
> codpiece, I can think of another approach which may work for you. It 
> still uses Select, but reads a one-row table, so it shouldn't hurt 
> performance much. The table serves no other purpose than storing the 
> next available PK; call the table NextPK, say. The algorithm might go 
> like this:
> 1. Lock the table NextPK.
> 2. Select its value.
> 3. Update the column with current value + 1.
> 4. Unlock the table.
> 5. Do your inserts.
> The lock will be very brief, perhaps brief enough to satisfy your 
> requirement.
> -- 
> Arthur
> Cell: 647.710.1314
> Only two businesses refer to their clientele as users: drug dealing 
> and software development.
> -- Arthur Fuller

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