mysql gen lock_MySQL 的next-lock 锁

叁歲伎倆 2023-01-11 13:18 178阅读 0赞

结论：

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

\+---------------+-----------+------------+---------------------+---------+------+-------+-------+-----------+---------------+

\+---------------+-----------+------------+---------------------+---------+------+-------+-------+-----------+---------------+

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

mysql gen lock_MySQL 的next-lock 锁

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

相关【MySQL】MySQL的锁机制

相关 MySQL中的锁（表锁、行锁）

相关 MySQL的锁

相关【MySql】- 3.MySQL的锁

相关 mysql gen lock_MySQL 的next-lock 锁

相关 ssh key gen

相关 MySQL中的锁(表锁、行锁)

相关 JVM划分Eden Space、Survivor Space、Tenured Gen，Perm Gen

相关 gen lua

相关 gen_grant_exec.sql