0

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!

teomor
  • 144
  • 1
  • 8
  • you could test out your theory by starting two console sessions and input your transaction in each of them step by step, in the order you think it would create the issue. You could even try the same thing with different isolation levels to see if changes anything. – didierc Apr 17 '13 at 19:07
  • Right :) That didn't cross my mind at all. I forgot about the whole console approach. Thanks! – teomor Apr 18 '13 at 12:44

2 Answers2

1

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.

didierc
  • 14,572
  • 3
  • 32
  • 52
0

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
Quassnoi
  • 413,100
  • 91
  • 616
  • 614
  • Yes. I know. Thanks. I just wanted to make sure that I have a real reason for concern, because whoever made the script may have done this several times throughout the whole application :( – teomor Apr 16 '13 at 18:31