mysql 主从 lock_mysql 主从同步权限mysql 行锁的实现

mysql锁的方式根据隔离级别不同而不同，因为默认隔离级别为repeatable-read可重复读，我们普遍理解为mysql锁实现方式为行锁，行锁就是利用索引实现完成的，mysql的支持的隔离级别有四种，这网上很多介绍，平常用的最多的也就是read-committed和repeatable-read两个，今天就对这两个隔离级别下锁的实现做下对比

首先我们就用默认级别repeatable-read的隔离级别来做测试：

mysql> CREATE TABLE `t1` (

-> `id` int(11) NOT NULL AUTO_INCREMENT,

-> `age1` int(11) DEFAULT NULL,

-> `age2` int(11) DEFAULT NULL,

-> PRIMARY KEY (`id`),

-> KEY `age1` (`age1`)

-> ) ENGINE=InnoDB;

mysql> select * from t1;

+----+------+------+

| id | age1 | age2 |

+----+------+------+

| 1 | 1 | 2 |

| 2 | 1 | 2 |

| 3 | 1 | 2 |

| 4 | 1 | 4 |

| 5 | 1 | 2 |

| 6 | 2 | 3 |

+----+------+------+

创建了一个表t1,插入了部分数据，建立了age1的辅助索引，现在开两个session来做测试：

session1session2

mysql> begin;Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set age2=55 where age1=1 and id=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set age2=55 where age1=1 and id=3;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

rollbackrollback看到这各位看官肯定觉得没问题，就应该是这样的啊，行锁嘛就是利用索引找到对应行进行加锁就好了，不急不急继续测试

session1session2

mysql> begin;Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set age2=55 where age1=1 and age2=4;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set age2=55 where age1=1 and id=1;

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

rollbackrollback咦.............这是咋回事呢？这更新的明显不是同一行为什么会被阻塞呢？我这暴脾气再用insert做下测试

session1session2

mysql> begin;Query OK, 0 rows affected (0.00 sec)

mysql> update t1 set age2=111 where age1=2 ;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1(age1,age2) values(1,33);

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

rollbackrollback..............插入数据和更新数据不是同一行业被阻塞了！好吧，来看下锁等待信息

mysql> select * from innodb_lock_waits G;

*************************** 1. row ***************************

wait_started: 2016-04-07 15:35:00

wait_age: 00:00:08

wait_age_secs: 8

locked_table: `sbtest`.`t1`

locked_index: PRIMARY

locked_type: RECORD

waiting_trx_id: 33333

waiting_trx_started: 2016-04-07 15:35:00

waiting_trx_age: 00:00:08

waiting_trx_rows_locked: 1

waiting_trx_rows_modified: 0

waiting_pid: 3

waiting_query: update t1 set age2=55 where age1=1 and id=1

waiting_lock_id: 33333:33:3:2

waiting_lock_mode: X

.....................................

为了节约版面后面截就删掉了，这是上面两个update不同行被阻塞时的记录

mysql> select * from innodb_lock_waits G;

*************************** 1. row ***************************

wait_started: 2016-04-07 15:40:43

wait_age: 00:00:26

wait_age_secs: 26

locked_table: `sbtest`.`t1`

locked_index: age1

locked_type: RECORD

waiting_trx_id: 33343

waiting_trx_started: 2016-04-07 15:40:43

《mysql 主从同步权限mysql 行锁的实现》总结了关于电脑技术教程，对于我们来确实能学到不少知识。

waiting_trx_age: 00:00:26

waiting_trx_rows_locked: 1

waiting_trx_rows_modified: 1

waiting_pid: 3

waiting_query: insert into t1(age1,age2) values(1,33)

waiting_lock_id: 33343:33:4:7

waiting_lock_mode: X,GAP

.......................................

这个是insert时候的记录，第一个可以看出来是在等primary的X锁，但是根据记录我们可以查询出两个update的主键值应该是不同，session1查出来的id应该为4，session2直接指定的id为1，这明显不会一行啊，再来看看结合insert的锁等待情况，这个锁是在age1索引上，但是lock_mode多了一个GAP，这玩意是啥？好吧，只有挠着头皮去官方文档里找了，别问我为啥挠头，我不会告诉你我很爱汉字：

更多:mysql 主从同步权限mysql 行锁的实现

https://www.002pc.comhttps://www.002pc.com/mysql/1425.html

你可能感兴趣的行锁,mysql,实现

No alive nodes found in your cluster

0踩

赏

0 赞