ネクストキーロックの検証

gistfile1.md

ネクストキーロックの検証

準備

テーブルの作成

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

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接続をするターミナルを２つ用意すること

$ 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

ターミナル1

delete from test_next_key_lock where col2 = 28;

ターミナル2

delete from test_next_key_lock where col2 = 48;

ターミナル1

• insertがロック待ちになること

insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,49,'2010-04-06');

ターミナル2

• 下記エラーとなること
• 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');

ターミナル1

• insert文の結果が返ること

Query OK, 1 row affected (33.01 sec)

rollback

• ターミナル1、2共に行う

rollback;

実技2　存在するデータのdelete & insert

ターミナル1

delete from test_next_key_lock where col2 = 25;

ターミナル2

delete from test_next_key_lock where col2 = 45;

ターミナル1

• insertがロック待ちになること

insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,48,'2010-04-06');

ターミナル2

• 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');

ターミナル1

• insert文の結果が返ること

Query OK, 1 row affected (33.01 sec)

rollback

• ターミナル1、2共に行う

rollback;

実技3　存在しないデータのdelete & insert (unique index)

ターミナル1

• test_next_key_lockの定義変更

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;

ターミナル2

delete from test_next_key_lock where col2 = 48;

ターミナル1

• insertがロック待ちになること

insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,48,'2010-04-06');

ターミナル2

• 下記エラーとなること
• 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');

ターミナル1

• insert文の結果が返ること

Query OK, 1 row affected (33.01 sec)

rollback

• ターミナル1、2共に行う

rollback;

実技4　存在するデータのdelete & insert(unique index)

ターミナル1

delete from test_next_key_lock where col2 = 25;

ターミナル2

delete from test_next_key_lock where col2 = 45;

ターミナル1

• 正常に終了すること

insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,48,'2010-04-06');

ターミナル2

• 正常に終了すること

insert into test_next_key_lock (col1, col2,rec_date) values ( 'a' ,28,'2010-04-06');

ターミナル1

• データの確認

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)

ターミナル2

• データの確認

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

• ターミナル1、2共に行う

rollback;