CODEWITHSUNDEEP
mysqldatabase
Piyush Pandey
Piyush Pandey

Reputation: 306

Concurrent Inserts in mySQL

I have 3 table - TB1, TB2 and r_tb1_tb2 (innoDB)

TB1 Hold the details of the users (will be inserted)
- id (primary, unique)
- name

TB2 Holds the details of the course the users can take (static table)
- id (primary, unique)
- name of the course

r_tb1_tb2 hold the relation between the 2 tables
- rID
- user_id (from table 1)
- course_id (reference to table 2)

When I insert a new row in TB1, I get the id of the last inserted row. And use that to insert another row in r_tb1_tb2

I can forsee that this may result to erroneous entries in case of simultaneous instances of inserts in tb1.

Can someone please point to the best practices for such simultaneous updates.

Thanks in advance.

Upvotes: 2

Views: 697

Answers (2)

Charif DZ
Charif DZ

Reputation: 14721

last_insert_id return value according the the user:

enter image description here

Upvotes: 1

e4c5
e4c5

Reputation: 53734

last_insert_id has built in protection for this

The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.

(emphasis theirs)

Thus if two different users are taking action on your site that results in records being inserted into T1, the last_insert_ids for those users will be different because they are using two different connections (clients in the conext above)

Upvotes: 3

Related Questions