----- Original Message ----- > From: "Adarsh Sharma" <adarsh.sha...@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
