mysql gen 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)

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/，如需转载，请注明出处，否则将追究法律责任。