MySQLの排他処理

https://blog.amedama.jp/entry/2015/09/19/012009

Exclusion Control

  • Pessimistic locking
  • Pessimistic locking without select for
  • Optimistic locking

Settings

$ mysql -u root -e "show variables like 'version'"
+---------------+--------+
| Variable_name | Value  |
+---------------+--------+
| version       | 5.6.26 |
+---------------+--------+
$ mysql -u root -e "select @@tx_isolation"
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
$ mysql -u root -e "show variables like 'default_storage_engine'"
+------------------------+--------+
| Variable_name          | Value  |
+------------------------+--------+
| default_storage_engine | InnoDB |
+------------------------+--------+
mysql> prompt prompt1> 
PROMPT set to 'prompt1> '
prompt1>

mysql> prompt prompt2> 
PROMPT set to 'prompt2> '
prompt2>

No exclusion control

table

mysql> create table accounts (
    ->     id Integer auto_increment,
    ->     name Text not null,
    ->     cash Integer not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.03 sec)

mysql> desc accounts;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| name  | text    | NO   |     | NULL    |                |
| cash  | int(11) | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

data

mysql> insert into accounts (name, cash) values ('foo', 100);
Query OK, 1 row affected (0.00 sec)

confirm

prompt1> begin;
Query OK, 0 rows affected (0.00 sec)

prompt1> select * from accounts where name like 'foo';
+----+------+------+
| id | name | cash |
+----+------+------+
|  1 | foo  |  100 |
+----+------+------+
1 row in set (0.00 sec)


prompt2> begin;
Query OK, 0 rows affected (0.00 sec)

prompt2> select * from accounts where name like 'foo';
+----+------+------+
| id | name | cash |
+----+------+------+
|  1 | foo  |  100 |
+----+------+------+
1 row in set (0.00 sec)

update

prompt1> update accounts set cash = 150 where name like 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

prompt2> update accounts set cash = 200 where name like 'foo';
...


prompt1> commit;
Query OK, 0 rows affected (0.00 sec)

prompt2> update accounts set cash = 200 where name like 'foo';
Query OK, 1 row affected (8.78 sec)
Rows matched: 1  Changed: 1  Warnings: 0

prompt2> commit;
Query OK, 0 rows affected (0.00 sec)

result

mysql> select * from accounts where name like 'foo';
+----+------+------+
| id | name | cash |
+----+------+------+
|  1 | foo  |  200 |
+----+------+------+
1 row in set (0.00 sec)

Pessimistic locking

table

mysql> create table accounts (
    ->     id Integer auto_increment,
    ->     name Text not null,
    ->     cash Integer not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> desc accounts;
+-------+---------+------+-----+---------+----------------+
| Field | Type    | Null | Key | Default | Extra          |
+-------+---------+------+-----+---------+----------------+
| id    | int(11) | NO   | PRI | NULL    | auto_increment |
| name  | text    | NO   |     | NULL    |                |
| cash  | int(11) | NO   |     | NULL    |                |
+-------+---------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

data

mysql> insert into accounts (name, cash) values ('foo', 100);
Query OK, 1 row affected (0.00 sec)

query

prompt1> begin;
Query OK, 0 rows affected (0.00 sec)

prompt1> select * from accounts where name like 'foo' for update;
+----+------+------+
| id | name | cash |
+----+------+------+
|  1 | foo  |  100 |
+----+------+------+
1 row in set (0.00 sec)

prompt2> begin;
Query OK, 0 rows affected (0.00 sec)

prompt2> select * from accounts where name like 'foo' for update;
...

prompt1> update accounts set cash = 150 where name like 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

prompt1> commit;
Query OK, 0 rows affected (0.01 sec)

prompt2> select * from accounts where name like 'foo' for update;
+----+------+------+
| id | name | cash |
+----+------+------+
|  1 | foo  |  150 |
+----+------+------+
1 row in set (13.00 sec)

prompt2> update accounts set cash = 250 where name like 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

prompt2> commit;
Query OK, 0 rows affected (0.00 sec)

result

mysql> select * from accounts where name like 'foo';
+----+------+------+
| id | name | cash |
+----+------+------+
|  1 | foo  |  250 |
+----+------+------+
1 row in set (0.00 sec)

Pessimistic locking without select for

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update accounts set cash = cash + 100 where name like 'foo';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from accounts where name like 'foo';
+----+------+------+
| id | name | cash |
+----+------+------+
|  1 | foo  |  350 |
+----+------+------+
1 row in set (0.00 sec)

Optimistic locking

table

mysql> create table accounts (
    ->     id Integer auto_increment,
    ->     version Integer not null,
    ->     name Text not null,
    ->     cash Integer not null,
    ->     primary key (id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> desc accounts;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| version | int(11) | NO   |     | NULL    |                |
| name    | text    | NO   |     | NULL    |                |
| cash    | int(11) | NO   |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

data

mysql> insert into accounts (version, name, cash) values (1, 'foo', 100);
Query OK, 1 row affected (0.00 sec)

query

prompt1> begin;
Query OK, 0 rows affected (0.00 sec)

prompt1> select * from accounts where name like 'foo';
+----+---------+------+------+
| id | version | name | cash |
+----+---------+------+------+
|  1 |       1 | foo  |  100 |
+----+---------+------+------+
1 row in set (0.00 sec)

prompt2> begin;
Query OK, 0 rows affected (0.00 sec)

prompt2> select * from accounts where name like 'foo';
+----+---------+------+------+
| id | version | name | cash |
+----+---------+------+------+
|  1 |       1 | foo  |  100 |
+----+---------+------+------+
1 row in set (0.00 sec)

prompt1> update accounts set version = 2, cash = 150 where name like 'foo' and version = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

prompt2> update accounts set version = 2, cash = 200 where name like 'foo' and version = 1;
...

prompt1> commit;
Query OK, 0 rows affected (0.00 sec)

prompt2> update accounts set version = 2, cash = 200 where name like 'foo' and version = 1;
Query OK, 0 rows affected (12.56 sec)
Rows matched: 0  Changed: 0  Warnings: 0

prompt2> rollback;
Query OK, 0 rows affected (0.00 sec)

prompt2> begin;
Query OK, 0 rows affected (0.00 sec)

prompt2> select * from accounts where name like 'foo';
+----+---------+------+------+
| id | version | name | cash |
+----+---------+------+------+
|  1 |       2 | foo  |  150 |
+----+---------+------+------+
1 row in set (0.00 sec)

prompt2> update accounts set version = 3, cash = 250 where name like 'foo' and version = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

prompt2> commit;
Query OK, 0 rows affected (0.00 sec)

result

mysql> select * from accounts where name like 'foo';
+----+---------+------+------+
| id | version | name | cash |
+----+---------+------+------+
|  1 |       3 | foo  |  250 |
+----+---------+------+------+
1 row in set (0.00 sec)

NOTE:

  • using pessimistic locking (select ... for update) without index of coulmn (in previous example, it's name) locks whole records since RDBMS doesn't know the specific record.
  • In this case, it is necessary to be careful because operations from another transaction are blocked up to rows which are not supposed to be processed in parallel.