Mysql innodb行锁测试

session 1:更新记录

mysql> set autocommit=off;
Query OK, 0 rows affected (0.01 sec)

mysql> update t1 set email='test@test.com' where id=0;
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4   Changed: 4   Warnings: 0

session 2也更新相同的记录,出现等待

mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set email='abc' where id=0;

session 3:查看系统等待事件:

mysql> show status like '%lock%';
+-------------------------------+---------+
| Variable_name                          | Value     |
+-------------------------------+---------+
| Com_lock_tables                       | 0           |
| Com_unlock_tables                    | 0           |
| Innodb_row_lock_current_waits | 1           | --这里
| Innodb_row_lock_time               | 0           |
| Innodb_row_lock_time_avg         | 0           |
| Innodb_row_lock_time_max         | 0           |
| Innodb_row_lock_waits              | 1           |
| Key_blocks_not_flushed            | 0           |
| Key_blocks_unused                    | 14497     |
| Key_blocks_used                       | 0           |
| Qcache_free_blocks                  | 1           |
| Qcache_total_blocks                 | 1           |
| Table_locks_immediate              | 2070991 |
| Table_locks_waited                  | 2           |
+-------------------------------+---------+
14 rows in set (0.01 sec)

session 1:提交记录

mysql> commit;
Query OK, 0 rows affected (0.01 sec)

session 2:update立刻完成

mysql> update t1 set email='abc' where id=0;
Query OK, 4 rows affected (2 min 43.44 sec)
Rows matched: 4   Changed: 4   Warnings: 0

session 3:再次查看系统等待事件

mysql> show status like '%lock%';
+-------------------------------+---------+
| Variable_name                          | Value     |
+-------------------------------+---------+
| Com_lock_tables                       | 0           |
| Com_unlock_tables                    | 0           |
| Innodb_row_lock_current_waits | 0           |   --这里为0
| Innodb_row_lock_time               | 163436   |
| Innodb_row_lock_time_avg         | 163436   |
| Innodb_row_lock_time_max         | 163436   |
| Innodb_row_lock_waits              | 1           |
| Key_blocks_not_flushed            | 0           |
| Key_blocks_unused                    | 14497     |
| Key_blocks_used                       | 0           |
| Qcache_free_blocks                  | 1           |
| Qcache_total_blocks                 | 1           |
| Table_locks_immediate              | 2070991 |
| Table_locks_waited                  | 2           |
+-------------------------------+---------+
14 rows in set (0.01 sec)

查询会话session 1,session 2的连接ID

session 1:
mysql> status;
--------------
mysql   Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)

Connection id:               15

session 2:
mysql> status;
--------------
mysql   Ver 12.22 Distrib 4.0.24, for pc-solaris2.10 (i386)

Connection id:               13

在上面的session 1尚没有提交的时候,可以执行下列命令，查看一些事务阻塞信息

mysql> show innodb status\G;
------------
TRANSACTIONS
------------
Trx id counter 0 3852351
Purge done for trx's n:o < 0 3852350 undo n:o < 0 0
History list length 11
Total number of lock structs in row lock hash table 7
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 15
MySQL thread id 18, query id 2071119 localhost root
show innodb status
---TRANSACTION 0 3852350, ACTIVE 6 sec, OS thread id 14 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 320
MySQL thread id 13, query id 2071118 localhost test Updating         --这里可以看到等待者
update t1 set email='abc' where id=0               --这里可以看到等待者正在执行的SQL
------- TRX HAS BEEN WAITING 6 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 32782 n bits 1056 index `idx_t1_id` of table `dc_test/t1` trx id 0 3852350 lock_mode X waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
  0: len 4; hex 80000000; asc        ;; 1: len 6; hex 00000196fe5d; asc         ];;

------------------
---TRANSACTION 0 3852348, ACTIVE 391 sec, OS thread id 12
7 lock struct(s), heap size 1024, undo log entries 4
MySQL thread id 15, query id 2071117 localhost test

当出现行锁时，谁等待谁？在哪里得到此信息，由于mysql采用的是线程机制，如何kill阻塞者，不知道在Mysql数据库里怎么操作？