MySQL 的next-lock 鎖
結論：
a. Mysql 由於 next-lock 鎖的存在，按更新語句的where條件的索引來鎖定一個範圍的記錄。
如果索引不存在，那麼會鎖住整個表的記錄。如果該條件是primary key，那麼就是變為行鎖。
b. next-lock是為了避免幻想讀，所以在預設的 repeated read 的狀況下會有這種情況。
c. 對於 read commited 的隔離級別，不會使用next-lock鎖，而是使用行鎖，這也是oracle的方式，
這種情況下，對併發的支援比 repeated read 要好些。
1. 設定會話級別的隔離級別為可重複讀
set tx_isolation='repeatable-read';
2. 檢查：會話級別已經修改，但是全域性級別還是沒有改的。
root@test 12:11:56>show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
root@test 12:12:06>show global variables like '%iso%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
3. 在該會話裡開啟一個事務，更新表的一行記錄
root@test 12:19:12>begin;
Query OK, 0 rows affected (0.00 sec)
由於這個表裡沒有索引，所以由於next-lock的存在，所以即使只有更新一行，但是實際上已經對所有的記錄上鎖了。
root@test 12:19:20>update test set name='aa11' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
4. 驗證： 開啟另一個會話，嘗試更新另外一條資料，發現一直等鎖，直到超時報錯。
root@test 12:19:39>update test set name='bb11' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert 同樣因為有鎖，而發生等待，直到報錯。
root@test 12:20:02>insert into test values (3,'cc');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
5. 進行檢查鎖的資訊：只有事務的資訊，並沒有鎖的資訊。即使在read-committed的隔離級別下也沒有鎖的資訊。
select * from innodb_trx\G
*************************** 2. row ***************************
trx_id: 151933845
trx_state: RUNNING
trx_started: 2016-08-18 12:19:33
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 31
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 3
trx_rows_modified: 1
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
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
root@information_schema 12:31:26>select * from INNODB_LOCKS;
Empty set (0.00 sec)
狀態資訊Innodb_current_row_locks能顯示出鎖的資訊。
root@test 01:43:13>show status like '%lock%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Com_lock_tables | 0 |
| Com_lock_tables_for_backup | 0 |
| Com_lock_binlog_for_backup | 0 |
| Com_show_slave_status_nolock | 0 |
| Com_unlock_binlog | 0 |
| Com_unlock_tables | 0 |
| Handler_external_lock | 2 |
| Innodb_deadlocks | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_current_row_locks | 2 |
| Innodb_row_lock_time | 12002 |
| Innodb_row_lock_time_avg | 6001 |
| Innodb_row_lock_time_max | 6001 |
| Innodb_row_lock_waits | 2 |
| Innodb_s_lock_os_waits | 100 |
| Innodb_s_lock_spin_rounds | 3455 |
| Innodb_s_lock_spin_waits | 1550 |
| Innodb_x_lock_os_waits | 48 |
| Innodb_x_lock_spin_rounds | 5238 |
| Innodb_x_lock_spin_waits | 1539 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 53578 |
| Key_blocks_used | 7 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
| Table_locks_immediate | 126 |
| Table_locks_waited | 0 |
+------------------------------------------+-------+
30 rows in set (0.00 sec)
6. 進一步驗證：INNODB_LOCKS 表的資料，只有等鎖的語句正在執行的期間才會顯示，Lock wait timeout 之前才會顯示，這一點和Oracle的v$locked_object的檢視資訊是不一致的，
這個檢視會顯示當前哪些會話在哪些物件上上了鎖。
root@information_schema 02:44:02>select * from INNODB_LOCKS \G
*************************** 1. row ***************************
lock_id: 151933860:250:3:1
lock_trx_id: 151933860
lock_mode: X
lock_type: RECORD
lock_table: `test`.`test`
lock_index: GEN_CLUST_INDEX
lock_space: 250
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: 151933845:250:3:1
lock_trx_id: 151933845
lock_mode: X
lock_type: RECORD
lock_table: `test`.`test`
lock_index: GEN_CLUST_INDEX
lock_space: 250
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.00 sec)
--此時也能看出在等待那個鎖，那個session
root@information_schema 04:41:40>select * from INNODB_LOCK_WAITS \G
*************************** 1. row ***************************
requesting_trx_id: 151933866 (請求鎖的事務ID)e
requested_lock_id: 151933866:250:3:5
blocking_trx_id: 151933845 (阻塞鎖的事務ID)
blocking_lock_id: 151933845:250:3:5
1 row in set (0.00 sec)
7. 綜述如果有語句執行超時，要判斷哪個執行緒，哪個語句鎖住了資料，可以用下面的sql
select a.lock_table,a.lock_type,a.lock_space,c.trx_mysql_thread_id,
d.Command,d.Info, d.State,d.Time, d.Rows_sent, d.Rows_examined
from INNODB_LOCKS a, INNODB_LOCK_WAITS b, innodb_trx c, processlist d
where a.lock_trx_id=b.blocking_trx_id
and a.lock_trx_id=c.trx_id
and c.trx_mysql_thread_id=d.id
+---------------+-----------+------------+---------------------+---------+------+-------+-------+-----------+---------------+
| lock_table | lock_type | lock_space | trx_mysql_thread_id | Command | Info | State | Time | Rows_sent | Rows_examined |
+---------------+-----------+------------+---------------------+---------+------+-------+-------+-----------+---------------+
| `test`.`test` | RECORD | 250 | 31 | Sleep | NULL | | 10848 | 2 | 2 |
+---------------+-----------+------------+---------------------+---------+------+-------+-------+-----------+---------------+
1 row in set (0.00 sec)
至於這個執行緒執行的具體的sql語句，現在還沒有發現在哪找得到，應該是slow sql 可以找到吧。
a. Mysql 由於 next-lock 鎖的存在，按更新語句的where條件的索引來鎖定一個範圍的記錄。
如果索引不存在，那麼會鎖住整個表的記錄。如果該條件是primary key，那麼就是變為行鎖。
b. next-lock是為了避免幻想讀，所以在預設的 repeated read 的狀況下會有這種情況。
c. 對於 read commited 的隔離級別，不會使用next-lock鎖，而是使用行鎖，這也是oracle的方式，
這種情況下，對併發的支援比 repeated read 要好些。
1. 設定會話級別的隔離級別為可重複讀
set tx_isolation='repeatable-read';
2. 檢查：會話級別已經修改，但是全域性級別還是沒有改的。
root@test 12:11:56>show variables like '%iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.00 sec)
root@test 12:12:06>show global variables like '%iso%';
+---------------+----------------+
| Variable_name | Value |
+---------------+----------------+
| tx_isolation | READ-COMMITTED |
+---------------+----------------+
3. 在該會話裡開啟一個事務，更新表的一行記錄
root@test 12:19:12>begin;
Query OK, 0 rows affected (0.00 sec)
由於這個表裡沒有索引，所以由於next-lock的存在，所以即使只有更新一行，但是實際上已經對所有的記錄上鎖了。
root@test 12:19:20>update test set name='aa11' where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
4. 驗證： 開啟另一個會話，嘗試更新另外一條資料，發現一直等鎖，直到超時報錯。
root@test 12:19:39>update test set name='bb11' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
insert 同樣因為有鎖，而發生等待，直到報錯。
root@test 12:20:02>insert into test values (3,'cc');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
5. 進行檢查鎖的資訊：只有事務的資訊，並沒有鎖的資訊。即使在read-committed的隔離級別下也沒有鎖的資訊。
select * from innodb_trx\G
*************************** 2. row ***************************
trx_id: 151933845
trx_state: RUNNING
trx_started: 2016-08-18 12:19:33
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 3
trx_mysql_thread_id: 31
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 0
trx_lock_structs: 2
trx_lock_memory_bytes: 360
trx_rows_locked: 3
trx_rows_modified: 1
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
trx_is_read_only: 0
trx_autocommit_non_locking: 0
2 rows in set (0.00 sec)
root@information_schema 12:31:26>select * from INNODB_LOCKS;
Empty set (0.00 sec)
狀態資訊Innodb_current_row_locks能顯示出鎖的資訊。
root@test 01:43:13>show status like '%lock%';
+------------------------------------------+-------+
| Variable_name | Value |
+------------------------------------------+-------+
| Com_lock_tables | 0 |
| Com_lock_tables_for_backup | 0 |
| Com_lock_binlog_for_backup | 0 |
| Com_show_slave_status_nolock | 0 |
| Com_unlock_binlog | 0 |
| Com_unlock_tables | 0 |
| Handler_external_lock | 2 |
| Innodb_deadlocks | 0 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_current_row_locks | 2 |
| Innodb_row_lock_time | 12002 |
| Innodb_row_lock_time_avg | 6001 |
| Innodb_row_lock_time_max | 6001 |
| Innodb_row_lock_waits | 2 |
| Innodb_s_lock_os_waits | 100 |
| Innodb_s_lock_spin_rounds | 3455 |
| Innodb_s_lock_spin_waits | 1550 |
| Innodb_x_lock_os_waits | 48 |
| Innodb_x_lock_spin_rounds | 5238 |
| Innodb_x_lock_spin_waits | 1539 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 53578 |
| Key_blocks_used | 7 |
| Performance_schema_locker_lost | 0 |
| Performance_schema_rwlock_classes_lost | 0 |
| Performance_schema_rwlock_instances_lost | 0 |
| Qcache_free_blocks | 1 |
| Qcache_total_blocks | 1 |
| Table_locks_immediate | 126 |
| Table_locks_waited | 0 |
+------------------------------------------+-------+
30 rows in set (0.00 sec)
6. 進一步驗證：INNODB_LOCKS 表的資料，只有等鎖的語句正在執行的期間才會顯示，Lock wait timeout 之前才會顯示，這一點和Oracle的v$locked_object的檢視資訊是不一致的，
這個檢視會顯示當前哪些會話在哪些物件上上了鎖。
root@information_schema 02:44:02>select * from INNODB_LOCKS \G
*************************** 1. row ***************************
lock_id: 151933860:250:3:1
lock_trx_id: 151933860
lock_mode: X
lock_type: RECORD
lock_table: `test`.`test`
lock_index: GEN_CLUST_INDEX
lock_space: 250
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
*************************** 2. row ***************************
lock_id: 151933845:250:3:1
lock_trx_id: 151933845
lock_mode: X
lock_type: RECORD
lock_table: `test`.`test`
lock_index: GEN_CLUST_INDEX
lock_space: 250
lock_page: 3
lock_rec: 1
lock_data: supremum pseudo-record
2 rows in set (0.00 sec)
--此時也能看出在等待那個鎖，那個session
root@information_schema 04:41:40>select * from INNODB_LOCK_WAITS \G
*************************** 1. row ***************************
requesting_trx_id: 151933866 (請求鎖的事務ID)e
requested_lock_id: 151933866:250:3:5
blocking_trx_id: 151933845 (阻塞鎖的事務ID)
blocking_lock_id: 151933845:250:3:5
1 row in set (0.00 sec)
7. 綜述如果有語句執行超時，要判斷哪個執行緒，哪個語句鎖住了資料，可以用下面的sql
select a.lock_table,a.lock_type,a.lock_space,c.trx_mysql_thread_id,
d.Command,d.Info, d.State,d.Time, d.Rows_sent, d.Rows_examined
from INNODB_LOCKS a, INNODB_LOCK_WAITS b, innodb_trx c, processlist d
where a.lock_trx_id=b.blocking_trx_id
and a.lock_trx_id=c.trx_id
and c.trx_mysql_thread_id=d.id
+---------------+-----------+------------+---------------------+---------+------+-------+-------+-----------+---------------+
| lock_table | lock_type | lock_space | trx_mysql_thread_id | Command | Info | State | Time | Rows_sent | Rows_examined |
+---------------+-----------+------------+---------------------+---------+------+-------+-------+-----------+---------------+
| `test`.`test` | RECORD | 250 | 31 | Sleep | NULL | | 10848 | 2 | 2 |
+---------------+-----------+------------+---------------------+---------+------+-------+-------+-----------+---------------+
1 row in set (0.00 sec)
至於這個執行緒執行的具體的sql語句，現在還沒有發現在哪找得到，應該是slow sql 可以找到吧。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/674865/viewspace-2123686/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【MySQL】MySQL中的鎖MySql
- MySQL的鎖MySql
- mysql鎖之死鎖MySql
- 【MySQL】MySQL中的鎖機制MySql
- MySQL的共享鎖和獨佔鎖MySql
- MySQL 中的鎖有哪些型別，MySQL 中加鎖的原則MySql型別
- 【問答分享第一彈】MySQL鎖總結:MySQL行鎖、表鎖、排他鎖、共享鎖的特點MySql
- mysql 鎖MySql
- [Mysql]鎖MySql
- MySQL鎖MySql
- MYSQL意向鎖的作用MySql
- MYSQL中的那些鎖MySql
- Mysql中的鎖機制——MyISAM表鎖MySql
- MySQL鎖：InnoDB行鎖需要避免的坑MySql
- MySQL MyISAM引擎的讀鎖與寫鎖MySql
- MySql(三) MySql中的鎖機制MySql
- MySQL鎖（四）行鎖的加鎖規則和案例MySql
- MySQL 死鎖和鎖等待MySql
- Mysql中S 鎖和 X 鎖的區別MySql
- MYSQL引擎的鎖對比MySql
- mysql myisam的鎖機制MySql
- mysql 鎖的慢日誌MySql
- 迷失在Mysql的鎖世界~MySql
- MySQL中鎖的分類MySql
- MySQL -- 表鎖MySql
- MySQL -- 行鎖MySql
- MySQL表鎖MySql
- MySQL 四 鎖MySql
- MySQL 行鎖MySql
- MySQL鎖分析MySql
- MySQL 鎖bug?MySql
- MySQL全域性鎖、表鎖以及行鎖MySql
- MySQL鎖(樂觀鎖、悲觀鎖、多粒度鎖)MySql
- MySQL 全域性鎖和表鎖MySql
- MySQL鎖：03.InnoDB行鎖MySql
- MySQL 悲觀鎖與樂觀鎖的詳解MySql
- MySQL/InnoDB中，樂觀鎖、悲觀鎖、共享鎖、排它鎖、行鎖、表鎖、死鎖概念的理解MySql
- Mysql鎖之行級鎖和表級意向鎖MySql
- 檢視MySQL鎖等待的原因MySql