[prev in list] [next in list] [prev in thread] [next in thread] 

List:       mysql
Subject:    Re: Removing Data Duplicacy
From:       Johan De Meersman <vegivamp () tuxera ! be>
Date:       2012-02-23 10:07:39
Message-ID: a4318016-523a-42df-bdb0-d6eb76cba645 () zimbra
[Download RAW message or body]


----- Original Message -----

> From: "Adarsh Sharma" <adarsh.sharma@orkash.com>

> 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, please. 

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 



[prev in list] [next in list] [prev in thread] [next in thread]