CODEWITHSUNDEEP
mysqltransactionsincrementisolation-leveltransaction-isolation
Cui Pengfei 崔鹏飞
Cui Pengfei 崔鹏飞

Reputation: 8305

MySql: correct transaction isolation level to use for incrementing a number

Suppose there is a table in DB like this:

id                                       code
a8e09395-771c-4c6b-bb49-4921eeaf3927    2018-1
726b1390-b502-11e8-96f8-529269fb1459    2018-2
7a7ac7a6-b502-11e8-96f8-529269fb1459    2018-3
81758ea6-b502-11e8-96f8-529269fb1459    2019-1

Suppose there are multiple clients writing to this table.

For the "code" column, we want to ensure that it follows a strict "year-nth of this year" pattern.

What is the correct transaction isolation level that all the clients should be using?

----update----2018-09-11 11:31:24---------

START TRANSACTION;

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SET @code = (SELECT CODE
             FROM hey
             WHERE id = 123);

UPDATE hey
SET code = @code + 1
WHERE id = 123;

COMMIT;

Did a quick test with the above transaction.

I started 2 consoles, then ran the above code, I ran them both to the line that reads the code column.

Then make one of them update the code column, it'll wait for lock.

Then I make the other one update the code column, it'll deadlock and rollback.

Now the first one's lock is resolved and it can commit.

So looks like this transaction isolation can prevent them from stepping on each other's toes, correct?

Upvotes: 1

Views: 421

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

You need to solve this with locking.

It doesn't matter what transaction isolation level.

In one session:

mysql1> begin;
mysql1> select max(code) from mytable where code like '2018-%' for update;

Output:

+-----------+
| max(code) |
+-----------+
| 2017-3    |
+-----------+

In a second session, try the same select for update. It pauses, waiting on the lock held by the first session's transaction.

mysql2> begin;
mysql2> select max(code) from mytable where code like '2018-%' for update;
(waits for lock)

In the first session, use the value the select returned to calculate the next value. Then insert the next row and commit.

mysql1> insert into mytable values (uuid(), '2018-4');
mysql1> commit;

The second session returns immediately after the commit in the first session. It correctly returns the new max code:

+-----------+
| max(code) |
+-----------+
| 2017-4    |
+-----------+

Now the second session has the lock, and it can insert the next row, without worrying that any other session sneaks in between the select and the insert.

Any transaction isolation will work if you use FOR UPDATE to lock the rows and ensure the transactions work serially.

Upvotes: 1

Related Questions