mysql锁总结脑图_关于mysql中的锁总结

最新推荐文章于 2022-05-31 15:00:40 发布

知道创宇云安全

最新推荐文章于 2022-05-31 15:00:40 发布

阅读量158

点赞数

文章标签： mysql锁总结脑图

本文链接：https://blog.csdn.net/weixin_42503069/article/details/113393566

版权

一、锁的基本信息：

共享锁(s)：又称读锁。允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。

排他锁(Ｘ)：又称写锁。允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。

大家通常以为排他锁锁住一行数据后，其他事务就不能读取和修改该行数据，其实不是这样的。排他锁指的是一个事务在一行数据加上排他锁后，其他事务不能再在其上加其他的锁。mysql InnoDB引擎默认的修改数据语句：update,delete,insert都会自动给涉及到的数据加上排他锁，select语句默认不会加任何锁类型，如果加排他锁可以使用select …for update语句，加共享锁可以使用select … lock in share mode语句。所以加过排他锁的数据行在其他事务种是不能修改数据的，也不能通过for update和lock in share mode锁的方式查询数据，但可以直接通过select …from…查询数据，因为普通查询没有任何锁机制。

意向共享锁(IS)：事务打算给数据行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排他锁(IX)：事务打算给数据行加排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就请求的锁授予该事务；反之，如果两者两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁(X)；对于普通SELECT语句，InnoDB不会加任何锁

下面语句都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)

select * from table where ? lock in share mode;

select * from table where ? for update;

insert into table values (…);

update table set ? where ?;

delete from table where ?; ##总结：

1.排它锁X遇到所有的其它锁，都冲突；2.意向锁(IX\IS)之间都兼容；3.共享锁与(or意向)共享锁(S\IS)之间兼容，与意向排它锁IX冲突。

二、行锁与索引：

(1)InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！

在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。

(2)由于MySQL的行锁是针对索引加的锁(主键是自动加了索引的，但加了索引的字段不是主键,所以加了索引但不是主键的字段上的数据是可以重复的)，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。应用设计的时候要注意这一点。

(3)当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB都会使用行锁来对数据加锁。

(4)即便在条件中使用了索引字段，但是否使用索引来检索数据是由MySQL通过判断不同执行计划的代价来决定的，如果MySQL认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下InnoDB将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查SQL的执行计划，以确认是否真正使用了索引。

比如，在tab_with_index表里的name字段有索引，但是name字段是varchar类型的，检索值的数据类型与索引字段不同，虽然MySQL能够进行数据类型转换，但却不会使用索引，从而导致InnoDB使用表锁。通过用explain检查两条SQL的执行计划，我们可以清楚地看到了这一点：

mysql> explain select * from jjj where a=30; #没有索引，rows是全部行；

mysql> explain select * from jjj where id=30; #有索引，rows是仅一行！

三、间隙锁(Next-Key锁)：

1)当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁 (Next-Key锁)。

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,…,100,101，下面的SQL：Select * from emp where empid > 100 for update;是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101(这些记录并不存在)的“间隙”加锁。

还要特别说明的是，InnoDB除了通过范围条件加锁时使用间隙锁外，如果使用相等条件请求给一个不存在的记录加锁，InnoDB也会使用间隙锁！比如上面的查询中，如修改为Select * from emp where empid > 105 for update;(105是不存在的)，那么当你插入empid为大于105如200的记录是不允许的。

2)InnoDB使用间隙锁的目的，一方面是为了防止幻读，以满足相关隔离级别的要求，对于上面的例子，要是不使用间隙锁，如果其他事务插入了empid大于100的任何记录，那么本事务如果再次执行上述语句，就会发生幻读；另外一方面，是为了满足其恢复和复制的需要。

3)在实际应用开发中，尤其是并发插入比较多的应用，我们要尽量优化业务逻辑，尽量使用相等条件来访问更新数据，避免使用范围条件。

四、事务的隔离性：

事务的隔离性(真正的隔离性)：一个事务所做的修改对另一个事务来说是不可见的，就好像是串行执行；官方测试，RR比RC的性能要好。

Innodb没有库级别与页级别的锁；

1、脏读的概念测试：

Read uncommitted \ Read committed \Repeatable read \ serializable (RR是真正符合隔离要求的)

Truncate table b;

Set tx_isolation=’ READ-UNCOMMITTED’;

use test; insert into b(2,2); #第一个窗口

select * from b;

#打开另一个b窗口，会发现能查到没有提交的数据(没有提交的是脏数据)，破坏了隔离性的要求；

2、不可重复读测试(RC级别时，b一个窗口提交了一个update更新,执行了begin的a窗口本来是停留在b窗口提交前的状态，第二次查询时却发现数据变了，破坏了事务隔离性的要求)：

mysql> set tx_isolation='REPEATABLE-READ'; ##a\b窗口都要执行，这里测试RR级别；

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> flush privileges; ##不执行发现会不生效。

Query OK, 0 rows affected (0.01 sec)

mysql> show variables like '%iso%';

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

| Variable_name | Value |

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

| transaction_isolation | REPEATABLE-READ |

| tx_isolation | REPEATABLE-READ |

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

2 rows in set (0.00 sec)

mysql> update jjj set a=42 where id=15; a窗口执行,RR级别不影响修改数据；

mysql> insert into jjj value(21,21); a窗口执行,RR级别不影响修改数据；

mysql> commit; a窗口执行提交；

mysql> select * from jjj where id=15; #b窗口,会发现数据没有变化(现实了可重复读)

mysql> select * from jjj; #b窗口,会发现没有id为21的数据(避免了幻读)。

此时，会发现给jjj的表加了意向排它锁IX和record lock：

##最高级别的serializable测试(serializable最严格，如果a窗口use bstest;begin; 那么b窗口当insert\update数据时，直接锁住)：

mysql> show variables like '%iso%';

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

| Variable_name | Value |

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

| transaction_isolation | SERIALIZABLE |

| tx_isolation | SERIALIZABLE |

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

2 rows in set (0.00 sec)

mysql> insert into jjj value(22,22);

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

mysql> update jjj set a=35 where id=15;

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

3、幻读：即在一个事务中读到了之前查询时不存在的数据(如有insert并提交的数据，在RC、RR中不能避免幻读)；

五、行锁：

1)行锁的三个模式：1.Record Lock :锁定的是30这个记录本身；

2.Gap Lock :锁定一个范围，如在查询范围的10-30(不包括30本身)，即禁止插入10-30之间的记录，如20，这样就解决了幻读问题；

3.Next-key Lock : Gap Lock + Record Lock,即锁定一个范围，且锁定记录本身，如下例：

mysql> begin;

mysql> update bstest.jjj set a=2222 where id>22;

这里的多条Record已属于next-key lock

无论是RR还是Rc的隔离级别的update或insert，只要用到了索引，只会根据索引进行加锁，没有用到索引则会对所有的记录加锁,因为是全表查找(RR的隔离级别才会，RC则不影响其它的修改)

会话1(RR的隔离级别，a列没有加锁)：

mysql> show variables like '%iso%';

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

| Variable_name | Value |

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

| transaction_isolation | REPEATABLE-READ |

| tx_isolation | REPEATABLE-READ |

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

2 rows in set (0.00 sec)

mysql> update bstest.jjj set a=383 where a=38888;

会话2(RR的隔离级别，a列没有加锁)：

mysql> show variables like '%iso%';

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

| Variable_name | Value |

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

| transaction_isolation | REPEATABLE-READ |

| tx_isolation | REPEATABLE-READ |

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

2 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update bstest.jjj set a=15 where a=40; ## 锁住了，事实上整张表都加上了X lock,无法进行insert 等操作；

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

会话1(隔离级别RC,其它同上，a没有索引)：

mysql> show variables like '%iso%';

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

| Variable_name | Value |

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

| transaction_isolation | READ-COMMITTED |

| tx_isolation | READ-COMMITTED |

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

2 rows in set (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> update bstest.jjj set a=222 where a=2;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

会话2(RC或RR都行，其它同上)：

mysql> begin;

mysql> update bstest.jjj set a=15 where a=40;

Query OK, 1 row affected (0.00 sec) #其它行a列正常修改，没有被锁住，insert等操作也没有影响；

Rows matched: 1 Changed: 1 Warnings: 0

mysql> show variables like '%iso%';

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

| Variable_name | Value |

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

| transaction_isolation | READ-COMMITTED |

| tx_isolation | READ-COMMITTED |

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

Xbackup存在问题：细节上(且备份后的数据量与原文件大小一样)。还是官方的mysqldump比较可靠,且备份的数据量小(逻辑备份)。

六、关于自增列有关的锁：

事物回滚后，自增值不会跟着回滚，导致自增值不连续，但是这个值连续也没什么意义、

##自增有关的参数：

auto_increment_increment = 1

auto_increment_offset = 1

1)如果插入前能确定行数的，就是simple inserts(在SQL运行完之前，确定了自增值之后，就可以释放自增锁了)

insert into table_1 values(NULL, 1), (NULL, 2);

2)如果插入前不能确定行数的，就是bulk inserts (在SQL执行完之后，AI锁才释放)

insert into table_1 select * from table_2;

innodb_autoinc_lock_mode={0|1|2}

(innodb_autoinc_lock_mode 是read-only 的，需要修改后重启MySQL实例)

0 传统方式

在SQL语句执行完之后，AI锁才释放

例如：当insert ... select ... 数据量很大时(比如执行10分钟)，那在这个SQL执行完毕前，其他事物是不能插入的(AI锁未释放)

这样可以保证在这个SQL语句内插入的数据，自增值是连续的，因为在这个10分钟内，AI自增锁是被这个SQL持有的，且没有释放

1 默认参数( 大部分情况设置为1 )

◾ bulk inserts，同传统方式一样，对于bulk inserts 的方式，和0 - 传统方式一样，在SQL执行完之后，AI锁才释放

◾ simple inserts，并发方式，在SQL运行完之前，确定了自增值之后，就可以释放自增锁了

因为bulk inserts 不知道要插入多少行，所以只能等insert结束后，才知道N 的值，然后一次性(ai + N)

而simple inserts 知道插入的行数(M)，所以可以先(ai + M) ，然后将锁释放掉，给别的事物用，然后自己慢慢插入数据

◾ 所有自增都可以并发方式( 不同于Simple inserts的方式)

◾ 同一SQL语句自增可能不连续