List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:February 23 2012 10:07am
Subject:Re: Removing Data Duplicacy
View as plain text  
----- Original Message -----

> From: "Adarsh Sharma" <adarsh.sharma@stripped>

> Thanks Johan, but i mentioned before that adding auto increment
> column doesn't solve the issue & it causes errors in the multi
> threaded application.

If it causes errors, you have other problems than this. 

> Multiple clients calls this insert procedure simultaneously, so it
> fails the transactions if two or more clients reads the same ID
> value.

Maybe I'm missing something, but both auto_increment and last_insert_id() are threadsafe,
so the normal procedure for handling this is an auto_increment on table2.c_id, then 

* insert into table2 
* select last_insert_id() into page_id 
* insert into table1 using page_id 

This will guarantee both unique numbering *and* corresponding IDs without the need for
locks or transactions. Transactions may be a good idea for other reasons, but that's
another discussion. 

If you *really* will not add an auto_increment to table2, then create pk_table with an
autoincrement and use that to grab the page_id. Forget this whole locking history, you do
not need it with auto_increment and last_insert_id. 

> I need to insert row simultaneously with the same C_ID into two
> tables ( table 1 & table 2).

There's no such thing as simultaneous. 

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

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

Nope, won't work, for the very simple reason that you're not using an auto_increment, so
there simply *is* no last_insert_id(). Go read the documentation before you keep arguing,

You've had roughly the same answer from several people who've been doing this stuff for
ages. If you keep ignoring that advice, I'm simply going to ignore this whole thread until
you come up with a damn good technical reason why an auto_increment isn't an option. We're
trying to help you find a solution, but I do have better things to do than hit my head
against a wall repeatedly. 

Bier met grenadyn 
Is als mosterd by den wyn 
Sy die't drinkt, is eene kwezel 
Hy die't drinkt, is ras een ezel 

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