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.