next-key lock 实验1

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的原因,所以会有阻塞。


  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值