您现在的位置是：网站首页> 编程资料编程资料

Mysql InnoDB的锁定机制实例详解_Mysql_

2023-05-27 368人已围观

简介 Mysql InnoDB的锁定机制实例详解_Mysql_

1.InnoDB的锁定机制

InnoDB存储引擎支持行级锁，支持事务处理，事务是有一组SQL语句组成的逻辑处理单元，他的ACID特性如下：

原子性(Atomicity): 事务具有原子不可分割的特性，要么一起执行，要么都不执行。
一致性(Consistency): 在事务开始和事务结束时，数据都保持一致状态。
隔离性(Isolation): 在事务开始和结束过程中，事务保持着一定的隔离特性，保证事务不受外部并发数据操作的影响。
持久性(Durability): 在事务完成后，数据将会被持久化到数据库中。

并发事务能提高数据库资源的利用率，提高了数据库的事务吞吐量，但并发事务也存在一些问题，主要包括：

更新丢失(Lost Update): 两个事务更新同一条数据，但第二个事务中途失败退出，导致两个修改都失效了；因为此时数据库没有执行任何锁操作，并发事务并没有被隔离。(现代数据库已经不存在这种问题)
脏读(Dirty Reads): 一个事务读了某行数据，但是另一个事务已经更新了这行数据，这是非常危险的，很可能导致所有的操作被回滚。
不可重复读: 一个事务对一行数据重复读取两次(多次)，可是得到了不同的结果,在两次读取过程中，有可能存在另一个事务对数据进行了修改。
幻读:事务在操作过程中进行两次查询，第二次查询结果包含了第一次没有出现的数据。出现幻读的主要原因是两次查询过程中另一个事务插入新的数据。

数据库并发中的“更新丢失”通常应该是完全避免的，但防止更新丢失数据，并不能单靠数据库事务控制来解决，需要应用程序对要更新的数据加必要的锁来解决，而以上出现的数据库问题都必要由数据库提供一定的事务隔离机制来解决。为了避免数据库事务并发带来的问题，在标准SQL规范中定义了4个事务的隔离级别，不同的隔离级别对事务处理不一样。

数据库隔离级别的比较

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读 (Read uncommitted)	最低级别，只能保证不读取物理上损坏的数据	是	是	是
已提交读 (Read committed)	语句级	否	是	是
可重复读 (Repeatable read)	事务级	否	否	是
可序列化 (Serializable)	最高级别，事务级	否	否	否

InnoDB存储引擎实现了4中行锁，分别时共享锁(S)、排他锁(X)、意向共享锁(IS)、意向排他锁(IX)。

共享锁：大家都能读，但是不能改，只有其中一个独占共享锁时候才能改；
排它锁：我要改，你们都不能改，也不能读(但可以MVCC快照读)

理解意向锁

意向锁不会和行级的S和X锁冲突，只会和表级的S和X锁冲突

意向锁是为了避免遍历全部行锁

考虑这个例子：

事务A锁住了表中的一行，让这一行只能读，不能写。

之后，事务B申请整个表的写锁。

如果事务B申请成功，那么理论上它就能修改表中的任意一行，这与A持有的行锁是冲突的。

数据库需要避免这种冲突，就是说要让B的申请被阻塞，直到A释放了行锁。

数据库要怎么判断这个冲突呢？

step1：判断表是否已被其他事务用表锁锁表

step2：判断表中的每一行是否已被行锁锁住。

注意step2，这样的判断方法效率实在不高，因为需要遍历整个表。

于是就有了意向锁。

在意向锁存在的情况下，事务A必须先申请表的意向共享锁，成功后再申请一行的行锁。

在意向锁存在的情况下，上面的判断可以改成

step1：不变

step2：发现表上有意向共享锁，说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。

1.1通过索引检索数据，上共享锁，行锁(如果不通过索引，会使用表锁)

 1.1通过索引检索数据，上共享锁，行锁 SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0; Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> select * from test; mysql> select * from test; -------------------------------------------------------------------------------- +----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level | +----+-------+-------+-------+ +----+-------+-------+-------+ | 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 | | 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 | | 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 | +----+-------+-------+-------+ +----+-------+-------+-------+ 3 rows in set (0.00 sec) 3 rows in set (0.00 sec) -------------------------------------------------------------------------------- 对主键索引上共享锁,其他事务也能获取到共享锁 mysql> select * from test where id=1 lock in share mode; +----+------+-------+-------+ | id | name | money | level | +----+------+-------+-------+ | 1 | tom | 100 | 1 | +----+------+-------+-------+ 1 row in set (0.01 sec) -------------------------------------------------------------------------------- 事务B也能继续加共享锁 mysql> select * from test where id=1 lock in share mode; +----+------+-------+-------+ | id | name | money | level | +----+------+-------+-------+ | 1 | tom | 100 | 1 | +----+------+-------+-------+ 1 row in set (0.01 sec) 但无法更新，因为事务A也加了共享锁 mysql> update test set level=11 where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MORE: 无法加排它锁 select *from test where id=1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 可以更新未加锁的，比如 mysql> update test set level=11 where id=2; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -------------------------------------------------------------------------------- 事务A也无法更新，因为事务B加了共享锁 mysql> update test set level=11 where id=1; ERROR 1205 (HY000): Lock wait timeout excee ded; try restarting transaction -------------------------------------------------------------------------------- 任意一个释放共享锁，则独占共享锁的事务可以更新 mysql> commit; Query OK, 0 rows affected (0.00 sec) -------------------------------------------------------------------------------- 事务B释放锁，事务A独占，可以更新了 mysql> update test set level=11 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

1.2通过索引检索数据，上排他锁，行锁

 1.2通过索引检索数据，上排他锁，行锁 SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0; Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> select * from test; mysql> select * from test; -------------------------------------------------------------------------------- +----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level | +----+-------+-------+-------+ +----+-------+-------+-------+ | 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 | | 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 | | 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 | +----+-------+-------+-------+ +----+-------+-------+-------+ 3 rows in set (0.00 sec) 3 rows in set (0.00 sec) -------------------------------------------------------------------------------- 对主键索引上排他锁,其他事务也能获取到共享锁 mysql> select *from test where id=1 for update; +----+------+-------+-------+ | id | name | money | level | +----+------+-------+-------+ | 1 | tom | 100 | 1 | +----+------+-------+-------+ 1 row in set (0.01 sec) -------------------------------------------------------------------------------- 事务B则不能继续上排它锁，会发生等待 mysql> select *from test where id=1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MORE: 也不能更新，因为更新也是上排它锁 mysql> update test set level=2 where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 也不能上共享锁 mysql> select * from test where level=1 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -------------------------------------------------------------------------------- 事务A可以更新 mysql> update test set level=11 where id=1; Query OK, 1 row affected (0.08 sec) Rows matched: 1 Changed: 1 Warnings: 0 -------------------------------------------------------------------------------- 释放排它锁 mysql> commit; Query OK, 0 rows affected (0.00 sec) -------------------------------------------------------------------------------- 事务A释放锁，事务B就可以加排它锁了 mysql> select * from test where id=1 for update; +----+------+-------+-------+ | id | name | money | level | +----+------+-------+-------+ | 1 | tom | 100 | 1 | +----+------+-------+-------+ 1 row in set (0.00 sec)

1.3通过索引更新数据，也是上排他锁，行锁

对于 update,insert,delete 语句会自动加排它锁

 1.3通过索引更新数据，也是上排他锁，行锁 SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0; Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) mysql> select * from test; mysql> select * from test; -------------------------------------------------------------------------------- +----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level | +----+-------+-------+-------+ +----+-------+-------+-------+ | 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 | | 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 | | 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 | +----+-------+-------+-------+ +----+-------+-------+-------+ 3 rows in set (0.00 sec) 3 rows in set (0.00 sec) -------------------------------------------------------------------------------- 更新id=1的行，就给该行上了排它锁，其他事务 无法更新该行 mysql> update test set level=11 where id=1; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 -------------------------------------------------------------------------------- 事务B则不能更新id=1的行，会发生等待 mysql> update test set level=21 where id=1; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction MORE: 也不能上排它锁 mysql> select *from test where id=1 for update; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 也不能上共享锁 mysql> select * from test where level=1 lock in share mode; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction -------------------------------------------------------------------------------- 释放排它锁 mysql> commit; Query OK, 0 rows affected (0.00 sec) -------------------------------------------------------------------------------- 事务A释放锁，事务B就可以加排它锁了 mysql> select * from test where id=1 for update; +----+------+-------+-------+ | id | name | money | level | +----+------+-------+-------+ | 1 | tom | 100 | 11| +----+------+-------+-------+ 1 row in set (0.00 sec)

2.1脏读

 //脏读 //2.1脏读 SessionA SessionB mysql> set autocommit=0; mysql> set autocommit=0; Query OK, 0 rows affected (0.02 sec) Query OK, 0 rows affected (0.02 sec) set session transaction isolation set session transaction isolation level read uncommitted; level read uncommitted; Query OK, 0 rows affected (0.00 sec) Query OK, 0 rows affected (0.00 sec) mysql> select * from test; mysql> select * from test; -------------------------------------------------------------------------------- +----+-------+-------+-------+ +----+-------+-------+-------+ | id | name | money | level | | id | name | money | level | +----+-------+-------+-------+ +----+-------+-------+-------+ | 1 | tom | 100 | 1 | | 1 | tom | 100 | 1 | | 2 | jack | 200 | 2 | | 2 | jack | 200 | 2 | | 3 | lucas | 300 | 3 | | 3 | lucas | 300 | 3 | +----+-------+-------+-------+ +----+-------+-------+-------+ 3 rows in set (0.00 sec) 3 rows in set (0.00 sec) -----------------------------------------------------------------------------
                提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                
                
                                            上一篇：浅谈Mysql连接数据库时host和user的匹配规则_Mysql_
                                                                下一篇：mysql从一张表查询批量数据并插入到另一表中的完整实例_Mysql_

您现在的位置是：网站首页> 编程资料编程资料

Mysql InnoDB的锁定机制实例详解_Mysql_

1.InnoDB的锁定机制

理解意向锁

相关内容

点击排行

本栏推荐

猜你喜欢