MySQL中next

44次阅读

共计 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)