CODEWITHSUNDEEP
mysqlconcurrencyinserttransactionsisolation
teomor
teomor

Reputation: 164

MySQL transactions and concurrent inserts

I've got this script made by someone and I looked over it and found something I'm not sure is safe:

Inside a transaction there are 2 consecutive inserts, in 2 different tables, both with auto incremented primary keys. They are supposed to be the same (the primary key generated from the first insert = the primary key generated from the second insert).

Don't ask me why, it's just how the script was made.

I'm new to transactions and I can't quite figure out if there is a concurrency issue here. I am thinking about the possibility of another thread running in the same time and ending up with keys being generated something like:

Thread #1:    Table_A ID: 3                                        Table_B ID: 4
Thread #2:                      Table_A ID: 4     Table_B ID: 3

I'm pretty sure (I've only ready transaction-related documentation today for the first time) that a transaction would not protect against this, but I just want to be sure I got it right.

Thanks!

Upvotes: 0

Views: 1981

Answers (2)

didierc
didierc

Reputation: 14750

You need to put both connections in serializable transaction isolation level in order to avoid the scenario you are describing, either by setting the tx_isolation on each connection with:

SET @@tx_isolation = SERIALIZABLE;

or 

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

or by setting the global isolation level with:

SET @@global.tx_isolation = SERIALIZABLE;

or

SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

which is inherited by any subsequently opened connection. At that level, transactions will block on any queries if another transaction is already in progress, ie. a transaction has already emitted a query (read or write) on the same tables.

See the mysql documentation for further details.

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425823

Your scenario is definitely possible.

If the primary keys are supposed to be the same in both tables, you may override the AUTO_INCREMENT in the second table and explicitly insert the value:

INSERT
INTO    a (id)
VALUES  (NULL) -- this inserts an auto incremented value

INSERT
INTO    b (id)
SELECT  LAST_INSERT_ID() -- this inserts the last value inserted into a in this session

Upvotes: 0

Related Questions