MySQL内next-lock锁的应用

       这篇文章主要讲解了“MySQL中next-lock锁的应用”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“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)
  
 
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)