MySQL innodb存儲(chǔ)引擎使用與Oracle相同的行鎖機(jī)制，對如何查看系統(tǒng)中存在的行鎖情況在下面的實(shí)驗(yàn)中將會(huì)看到。下面是測試過程:
session 1：更新記錄
創(chuàng)新互聯(lián)專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于網(wǎng)站設(shè)計(jì)、網(wǎng)站制作、汝州網(wǎng)絡(luò)推廣、成都小程序開發(fā)、汝州網(wǎng)絡(luò)營銷、汝州企業(yè)策劃、汝州品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎(jiǎng)；創(chuàng)新互聯(lián)為所有大學(xué)生創(chuàng)業(yè)者提供汝州建站搭建服務(wù)，24小時(shí)服務(wù)熱線：13518219792，官方網(wǎng)址：www.cdcxhl.com
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：也更新相同的記錄,出現(xiàn)等待
mysql> set autocommit=off;
Query OK, 0 rows affected (0.00 sec)
mysql> update t1 set email='abc' where id=0;
session 3：查看系統(tǒng)等待事件:
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) --這么長時(shí)間完成更新操作
Rows matched: 4 Changed: 4 Warnings: 0
session 3：再次查看系統(tǒng)等待事件
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)
查詢會(huì)話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尚沒有提交的時(shí)候,可以執(zhí)行下列命令，查看一些事務(wù)阻塞信息
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 --這里可以看到等待者正在執(zhí)行的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
重慶分公司
重慶分公司
