Jump to content
  • Advertisement
Sign in to follow this  
CProgrammer

database design problem

This topic is 3723 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hi guys, I have the following problem. I have two tables: table1 has an id and a field called something like options_id. The second table is called 'options'. Now I have SQL statements that do the following when I want to create a new entry in table1: 1.) Insert data into options. 2.) Get the LAST_INSERT_ID 3.) Insert data into table1 and set options_id to the result of LAST_INSERT_ID All works fine, however in order to keep the tables clean Id like the entry in the options table to have the same id as the entry in table1. This is usually the case except if two requsts come simultaneously and one of the threads pauses right before step 3. I thought of locking the tables but that means no read access for all requests which is bad. Any ideas? -CProgrammer

Share this post


Link to post
Share on other sites
Advertisement
Run both modifications as part of a transaction in a 'serializable' isolation level.

In practice, run the SQL query:


SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION;
INSERT INTO options VALUES (...);
INSERT INTO table1 VALUES (..., LAST_INSERT_ID());
COMMIT;


Don't forget to disable auto-commit, if enabled.

Share this post


Link to post
Share on other sites
This looks interesting. So basically this blocks oter threads from writing but everyone can read, during the transaction?
Also once Icall SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Do I have to reset this mode back to normal somehow after COMMIT. Are there any side effects for other querys in this connection?

Share this post


Link to post
Share on other sites
Quote:
Original post by CProgrammer
This looks interesting. So basically this blocks oter threads from writing but everyone can read, during the transaction?


That's the idea.

Quote:
Also once Icall SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
Do I have to reset this mode back to normal somehow after COMMIT.


No. This version (unlike SET SESSION TRANSACTION or SET GLOBAL TRANSACTION) only applies to the next transaction.

Quote:
Are there any side effects for other querys in this connection?


No.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

We are the game development community.

Whether you are an indie, hobbyist, AAA developer, or just trying to learn, GameDev.net is the place for you to learn, share, and connect with the games industry. Learn more About Us or sign up!

Sign me up!