mysql----innoDB事务与锁测试的操作

2019-04-23  本文已影响0人  不过意局bugyj

关于事务与锁的解释在很多博客中都很详细了，这里大致记下一些简要内容（方便以后复习时对照思考），重点是操作案例，以表现事务与锁的操作的具体样例！
很多都是我自己的理解，有错望指出！
参考：mysql的锁（行锁、表锁）

1. 锁

1.1 锁的分类

按锁的粒度分类

注意： 行锁是针对索引的，加上行锁是在针对索引加锁，

可是我疑惑为什么加表锁能避免死锁，当a事务访问A表时，后续还要访问B表；事务b访问B表时要访问a表，这不就死锁了吗？？？
经查询，有知乎伙伴说，获取和释放锁的开销很小，可能事务一开始就将所有涉及的表都加上表锁，这样就不会出现死锁了！这好像才说得通！事实就是如此，如果一个事务真的涉及多张表，很容易产生死锁，所以最好将多张表一次性加上表锁。

按封锁类型分类

此外：mysql还是有意向锁，有数据库自动生成，不需要用户干预，这里了解下。顾名思义，意向锁即具有加某种锁的意向，所以也分为S和X，名为IS（intention S）和IX。意向锁都是表锁。

锁类型 S X IS IX
S 兼容 冲突 兼容 冲突
X 冲突 冲突 冲突 冲突
IS 兼容 冲突 兼容 兼容
IX 冲突 冲突 兼容 兼容
  1. 如果某事务要对条记录加锁，若与当前锁兼容则获取该锁，否则就要等待锁的释放。
  2. 对于sql，只有在 update、 delete、 insert会给所涉及的数据项添加X锁，对于select，数据库不会加锁。
  3. 当然我们可以显式地加上S锁和X锁：
 select * from test  where ... lock in share mode;

上述形式的使用场景可以用在某事务读取某数据后防止其他事务update和delete该数据，其他事务需要修改的话就要先加X锁，但因为该数据已加上S锁，所以需要等待。（即防止可重复读问题，但注意，如果本事务也对数据进行修改操作，就会发生死锁！）

 select * from test where ... for update;

间隙锁

当我们使用的是范围查找而不是等值条件，并且还要加上共享锁和互斥锁时，mysql会将所在范围内的记录都加上锁，在这个范围内不存在的记录，称为间隙，也会加上锁。比如：
select * from where id > 100 for update;这时候除了表中大于100的记录被加上锁，不存在的间隙也会被加上锁， 比如同时另外一个事务想插入一个id=101的记录，会插入失败。
这就让我想到了之前项目中为了保证注册时用户名的唯一性，必须先查找一遍以确定是否已经存在即将注册的用户名，然后再插入。可是极有可能再查找完确定不存在并返回结果的同时，另外一个事务突然插入同一个用户名，注册就会失败。这种现象即是幻读，这个就可以用间隙锁解决。
虽然间隙锁避免了我们为避免幻读而使事务隔离级别为序列化造成数据库的并发性严重降低的后果，但也要注意不要锁太大范围，即太大间隙。尽量优化业务逻辑，使用等值连接代替范围查找。

1.2 锁操作示例

示例主要是想通过实际操作对前面理论进行验证，顺序按照前面所述顺序来！

1.2.1 列锁

①列锁是针对索引的。 ②select不会加锁，所以不用获得锁
创建没有索引的表balance，然后插入两条数据。

create table balance (
id int,
balance int);

insert into balance values(1, 500),(2,300);

打开两个事务：


可以看到，第一个事务显式在id=1记录上加上行写锁，id=2也被锁住了，所以id没有索引时，会直接在表上加锁。
下面试试有索引的情况：

create index id_index on balance(id);

题外话，如果同时查询了一个索引一个非索引，比如：
select * from balance where id = 1 and balance = 500 for update;
第二行记录 1，300还会锁吗？答案是会的，因为上面的sql命中了id=1的索引，只在索引1上加锁，第二行索引也为一，所以会加上。

③update，insert，delete自动加写锁，索引规则与前面相同。

第一个事务只要提交，第二个事务就能出现查询结果！

1.2.2 表锁

TODO

1.2.3 间隙锁

间隙锁也符合索引那套规则，加锁不在索引，即锁表
再次创建索引：create index id_index on balance(id);
对id>1的间隙加锁，1能访问，2不能
现在插入一条id=3的记录insert into balance values(3, 1000)
直接在加不是间隙锁的事务中添加，被阻塞，因为3>1，在间隙中

虽然添加数据成功，但第二个事务普通的select都访问不到，这不是锁的缘故，而是因为前面提到过的MVCC的原因（下面会提到），后面的事务因为访问的是快照中的数据，此时快照中的数据并没有将新添加的数据3加入，所以访问不到！添加一方提交后，另一方才能访问到，这避免了即将提到的脏读。


没有间隙锁，普通的select（不与任何锁冲突）也无法访问到

加上间隙锁后，别的事务也无法添加指定范围内新的数据，这即避免了幻读。

2. 事务

2.1 事务的属性ACID

2.2 事务并发带来的一些问题

追加：不可重复读和幻读的区别还是有些模糊，有很多博客都是像上面那样说，但有人说不可重复读比较偏向于两次读，而幻读则偏向于一读一写，比如某事务先select判断某数据字段并不存在，但当添加这一字段的数据时又说有冲突，可是再次读取还是没有这一字段（MVCC）。这时候可能是其他事务在这一事务读取之后添加了这一字段，此为幻读。理解自： mysql 幻读的详解、实例及解决办法

2.3 事务的隔离级别

脏读 不可重复读 幻读
读提交 解决
可重复读 解决 解决
串行化 解决 解决 解决

参考的文章中说：
在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。
一种是在读取数据前，对其加锁，阻止其他事务对数据进行修改。这势必会造成性能上的不好的影响。----悲观锁
另一种是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。----乐观锁

2.4 事务操作示例

2.4.1 并发访问的问题实现

select @@transaction_isolation;
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL
 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}

修改为读未提交，后打开自动提交。


我们这里已经打开了

可以使用set auto_commit=0（1关闭）将其打开。

可以看到第二个事务读取到了第一个事务未提交的脏数据

第一个事务如果撤回，另一个事务就读取了完全错误的数据，这就是脏读。

感觉挺简单的，懒得实现了。

2.4.2事务的隔离级别

懂的，操作简单就不浪费时间了。

上一篇下一篇

猜你喜欢

热点阅读