MySQL中next

136次閱讀

共計 5743 個字符，預計需要花費 15 分鐘才能閱讀完成。

這篇文章主要講解了“MySQL 中 next-lock 鎖的應用”，文中的講解內容簡單清晰，易于學習與理解，下面請大家跟著丸趣 TV 小編的思路慢慢深入，一起來研究和學習“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

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)