session1: session2:
set autocommit=0; set autocommit=0;
mysql> create table t(id int ,name varchar(30),primary key(id))engine=innodb;
mysql> select * from t where id=5 for update;
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B0485
trx_state: RUNNING
trx_started: 2015-09-17 18:24:12
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 100508
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
session2:
insert into t values(7,'huang'); --被阻塞
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B04AF
trx_state: LOCK WAIT
trx_started: 2015-09-17 18:24:54
trx_requested_lock_id: B04AF:0:9257:1
trx_wait_started: 2015-09-17 18:24:54
trx_weight: 2
trx_mysql_thread_id: 100510
trx_query: insert into t values(7,'huang')
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: B0485
trx_state: RUNNING
trx_started: 2015-09-17 18:24:12
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 100508
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
2 rows in set (0.00 sec)
*************************** 1. row ***************************
lock_id: B04AF:0:9257:1
lock_trx_id: B04AF
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 9257
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: B0485:0:9257:1
lock_trx_id: B0485
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 9257
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.00 sec)
*************************** 1. row ***************************
requesting_trx_id: B04AF
requested_lock_id: B04AF:0:9257:1
blocking_trx_id: B0485
blocking_lock_id: B0485:0:9257:1
1 row in set (0.00 sec)
session2:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session1:
drop table t; --被阻塞
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B04AF
trx_state: RUNNING
trx_started: 2015-09-17 18:24:54
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 1
trx_mysql_thread_id: 100510
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 1
trx_lock_memory_bytes: 376
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
session1: session2:
commit;
Query OK, 0 rows affected (1 min 5.83 sec)
mysql> create table t(id int ,name varchar(30),primary key(name))engine=innodb;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from t where name='huang' for update;
Empty set (0.00 sec)
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B0585
trx_state: RUNNING
trx_started: 2015-09-17 18:27:03
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 100508
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
session2:
insert into t values(1,'yong');--被阻塞
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B060C
trx_state: LOCK WAIT
trx_started: 2015-09-17 18:28:19
trx_requested_lock_id: B060C:0:9257:1
trx_wait_started: 2015-09-17 18:28:19
trx_weight: 2
trx_mysql_thread_id: 100510
trx_query: insert into t values(1,'yong')
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: B0585
trx_state: RUNNING
trx_started: 2015-09-17 18:27:03
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 100508
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
2 rows in set (0.00 sec)
*************************** 1. row ***************************
lock_id: B060C:0:9257:1
lock_trx_id: B060C
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 9257
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: B0585:0:9257:1
lock_trx_id: B0585
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 9257
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.00 sec)
*************************** 1. row ***************************
requesting_trx_id: B060C
requested_lock_id: B060C:0:9257:1
blocking_trx_id: B0585
blocking_lock_id: B0585:0:9257:1
1 row in set (0.00 sec)
session2:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session1: session2:
set autocommit=0; set autocommit=0;
mysql> create table t(id int ,name varchar(30),primary key(id))engine=innodb;
mysql> select * from t where id=5 for update;
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B0485
trx_state: RUNNING
trx_started: 2015-09-17 18:24:12
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 100508
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
session2:
insert into t values(7,'huang'); --被阻塞
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B04AF
trx_state: LOCK WAIT
trx_started: 2015-09-17 18:24:54
trx_requested_lock_id: B04AF:0:9257:1
trx_wait_started: 2015-09-17 18:24:54
trx_weight: 2
trx_mysql_thread_id: 100510
trx_query: insert into t values(7,'huang')
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: B0485
trx_state: RUNNING
trx_started: 2015-09-17 18:24:12
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 100508
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
2 rows in set (0.00 sec)
*************************** 1. row ***************************
lock_id: B04AF:0:9257:1
lock_trx_id: B04AF
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 9257
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: B0485:0:9257:1
lock_trx_id: B0485
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 9257
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.00 sec)
*************************** 1. row ***************************
requesting_trx_id: B04AF
requested_lock_id: B04AF:0:9257:1
blocking_trx_id: B0485
blocking_lock_id: B0485:0:9257:1
1 row in set (0.00 sec)
session2:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
session1:
drop table t; --被阻塞
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B04AF
trx_state: RUNNING
trx_started: 2015-09-17 18:24:54
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 1
trx_mysql_thread_id: 100510
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 1
trx_lock_memory_bytes: 376
trx_rows_locked: 0
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
session1: session2:
commit;
Query OK, 0 rows affected (1 min 5.83 sec)
mysql> create table t(id int ,name varchar(30),primary key(name))engine=innodb;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from t where name='huang' for update;
Empty set (0.00 sec)
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B0585
trx_state: RUNNING
trx_started: 2015-09-17 18:27:03
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 100508
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
1 row in set (0.00 sec)
Empty set (0.00 sec)
Empty set (0.00 sec)
session2:
insert into t values(1,'yong');--被阻塞
session3:
mysql> select * from information_schema.innodb_trx\G select * from information_schema.innodb_locks\G select * from information_schema.innodb_lock_waits\G
*************************** 1. row ***************************
trx_id: B060C
trx_state: LOCK WAIT
trx_started: 2015-09-17 18:28:19
trx_requested_lock_id: B060C:0:9257:1
trx_wait_started: 2015-09-17 18:28:19
trx_weight: 2
trx_mysql_thread_id: 100510
trx_query: insert into t values(1,'yong')
trx_operation_state: inserting
trx_tables_in_use: 1
trx_tables_locked: 1
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
*************************** 2. row ***************************
trx_id: B0585
trx_state: RUNNING
trx_started: 2015-09-17 18:27:03
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 2
trx_mysql_thread_id: 100508
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 376
trx_rows_locked: 1
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 10000
2 rows in set (0.00 sec)
*************************** 1. row ***************************
lock_id: B060C:0:9257:1
lock_trx_id: B060C
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 9257
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: B0585:0:9257:1
lock_trx_id: B0585
lock_mode: X
lock_type: RECORD
lock_table: `test`.`t`
lock_index: `PRIMARY`
lock_space: 0
lock_page: 9257
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.00 sec)
*************************** 1. row ***************************
requesting_trx_id: B060C
requested_lock_id: B060C:0:9257:1
blocking_trx_id: B0585
blocking_lock_id: B0585:0:9257:1
1 row in set (0.00 sec)
session2:
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
因为rr用的是next-key lock (即就是record lock + next-key lock)所以,上面的因为有gap lock的原因,所以会有阻塞。