create table test_next_key_lock (
id int(8) not null AUTO_INCREMENT,
col1 char(1) not null,
col2 int(8),
rec_date datetime,
PRIMARY KEY (id),
key (col2),
key (rec_date)
)ENGINE=InnoDB DEFAULT CHARSET=utf8
;
insert into test_next_key_lock (col1, col2,rec_date)
values
( 'b' ,35,'2010-04-07'),
( 'a' ,40,'2010-04-08'),
( 'c' ,20,'2010-04-09'),
( 'b' ,25,'2010-04-10'),
( 'a' ,50,'2010-04-11'),
( 'c' ,10,'2010-04-12'),
( 'b' ,45,'2010-04-13'),
( 'a' ,15,'2010-04-14'),
( 'c' ,30,'2010-04-15')
;
$ mysql
autocommitのoff ターミナル1、2ともに
set autocommit=0;
ターミナルの確認
ターミナル1、2共に分離レベルがREPEATABLE-READであること(デフォルトの設定)
SELECT @@tx_isolation;
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
実技1 存在しないデータのdelete & insert
delete from test_next_key_lock where col2 = 28;
delete from test_next_key_lock where col2 = 48;
insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,49,'2010-04-06');
下記エラーとなること
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,29,'2010-04-06');
Query OK, 1 row affected (33.01 sec)
rollback;
実技2 存在するデータのdelete & insert
delete from test_next_key_lock where col2 = 25;
delete from test_next_key_lock where col2 = 45;
insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,48,'2010-04-06');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,28,'2010-04-06');
Query OK, 1 row affected (33.01 sec)
rollback;
実技3 存在しないデータのdelete & insert (unique index)
alter table test_next_key_lock drop key col2;
alter table test_next_key_lock add unique index (col2);
delete from test_next_key_lock where col2 = 28;
delete from test_next_key_lock where col2 = 48;
insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,48,'2010-04-06');
下記エラーとなること
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,28,'2010-04-06');
Query OK, 1 row affected (33.01 sec)
rollback;
実技4 存在するデータのdelete & insert(unique index)
delete from test_next_key_lock where col2 = 25;
delete from test_next_key_lock where col2 = 45;
insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,48,'2010-04-06');
insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,28,'2010-04-06');
select * from test_next_key_lock;
mysql> select * from test_next_key_lock;
+----+------+------+---------------------+
| id | col1 | col2 | rec_date |
+----+------+------+---------------------+
| 1 | b | 35 | 2010-04-07 00:00:00 |
| 2 | a | 40 | 2010-04-08 00:00:00 |
| 3 | c | 20 | 2010-04-09 00:00:00 |
| 5 | a | 50 | 2010-04-11 00:00:00 |
| 6 | c | 10 | 2010-04-12 00:00:00 |
| 7 | b | 45 | 2010-04-13 00:00:00 |
| 8 | a | 15 | 2010-04-14 00:00:00 |
| 9 | c | 30 | 2010-04-15 00:00:00 |
| 16 | a | 48 | 2010-04-06 00:00:00 |
+----+------+------+---------------------+
9 rows in set (0.00 sec)
select * from test_next_key_lock;
mysql> select * from test_next_key_lock;
+----+------+------+---------------------+
| id | col1 | col2 | rec_date |
+----+------+------+---------------------+
| 1 | b | 35 | 2010-04-07 00:00:00 |
| 2 | a | 40 | 2010-04-08 00:00:00 |
| 3 | c | 20 | 2010-04-09 00:00:00 |
| 4 | b | 25 | 2010-04-10 00:00:00 |
| 5 | a | 50 | 2010-04-11 00:00:00 |
| 6 | c | 10 | 2010-04-12 00:00:00 |
| 8 | a | 15 | 2010-04-14 00:00:00 |
| 9 | c | 30 | 2010-04-15 00:00:00 |
| 17 | a | 28 | 2010-04-06 00:00:00 |
+----+------+------+---------------------+
9 rows in set (0.00 sec)
rollback;