MySQL中next-lock鎖的應用

這篇文章主要講解了“中next-lock鎖的應用”，文中的講解內容簡(jiǎn)單清晰，易于學(xué)習與理解，下面請大家跟著(zhù)小編的思路慢慢深入，一起來(lái)研究和學(xué)習“MySQL中next-lock鎖的應用”吧！

結論：
a. Mysql 由于 next-lock 鎖的存在，按更新語(yǔ)句的where條件的索引來(lái)鎖定一個(gè)范圍的記錄。
如果索引不存在，那么會(huì )鎖住整個(gè)表的記錄。如果該條件是primary key，那么就是變?yōu)樾墟i。
b. next-lock是為了避免幻想讀，所以在默認的 repeated read 的狀況下會(huì )有這種情況。
c. 對于 read commited 的隔離級別，不會(huì )使用next-lock鎖，而是使用行鎖，這也是oracle的方式，
這種情況下，對并發(fā)的支持比 repeated read 要好些。


1. 設置會(huì )話(huà)級別的隔離級別為可重復讀
set tx_isolation='repeatable-read';


2. 檢查：會(huì )話(huà)級別已經(jīng)修改，但是全局級別還是沒(méi)有改的。
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. 在該會(huì )話(huà)里開(kāi)啟一個(gè)事務(wù)，更新表的一行記錄
root@test 12:19:12>begin;
Query OK, 0 rows affected (0.00 sec)
 
由于這個(gè)表里沒(méi)有索引，所以由于next-lock的存在，所以即使只有更新一行，但是實(shí)際上已經(jīng)對所有的記錄上鎖了。
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. 驗證： 開(kāi)啟另一個(gè)會(huì )話(huà)，嘗試更新另外一條數據，發(fā)現一直等鎖，直到超時(shí)報錯。
root@test 12:19:39>update test set name='bb11' where id=2;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


insert 同樣因為有鎖，而發(fā)生等待，直到報錯。
root@test 12:20:02>insert into test values (3,'cc');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


5. 進(jìn)行檢查鎖的信息：只有事務(wù)的信息，并沒(méi)有鎖的信息。即使在read-committed的隔離級別下也沒(méi)有鎖的信息。
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)
狀態(tài)信息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. 進(jìn)一步驗證：INNODB_LOCKS 表的數據，只有等鎖的語(yǔ)句正在執行的期間才會(huì )顯示，Lock wait timeout 之前才會(huì )顯示，這一點(diǎn)和Oracle的v$locked_object的視圖信息是不一致的，
這個(gè)視圖會(huì )顯示當前哪些會(huì )話(huà)在哪些對象上上了鎖。
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)


--此時(shí)也能看出在等待那個(gè)鎖，那個(gè)session
root@information_schema 04:41:40>select * from INNODB_LOCK_WAITS \G
*************************** 1. row ***************************
requesting_trx_id: 151933866    (請求鎖的事務(wù)ID)e
requested_lock_id: 151933866:250:3:5
  blocking_trx_id: 151933845     (阻塞鎖的事務(wù)ID)
 blocking_lock_id: 151933845:250:3:5  
1 row in set (0.00 sec)


7. 綜述如果有語(yǔ)句執行超時(shí)，要判斷哪個(gè)線(xiàn)程，哪個(gè)語(yǔ)句鎖住了數據，可以用下面的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)