|
MySQL innodb存储引擎使用与oracle相同的行锁机制，对如何查看系统中存在的行锁情况在下面的实验中，将可以看到。下面是测试过程: session 1:更新记录 [coolcode] 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 [/coolcode]
session 2也更新相同的记录,出现等待 [coolcode] mysql> set autocommit=off; Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set email='abc' where id=0; [/coolcode]
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:提交记录 [coolcode] mysql> commit; Query OK, 0 rows affected (0.01 sec) [/coolcode]
session 2:update立刻完成 [coolcode] 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 [/coolcode]
session 3:再次查看系统等待事件 [coolcode] 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) [/coolcode]
查询会话session 1,session 2的连接ID [coolcode] 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 [/coolcode]
在上面的session 1尚没有提交的时候,可以执行下列命令，查看一些事务阻塞信息 [coolcode] 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 [/coolcode]
当出现行锁时，谁等待谁？在哪里得到此信息，如何kill阻塞者,不知道在Mysql数据库里怎么操作？ |
|
|
来自： pengx > 《MySQL/Oracle》
