数据库之锁与事务

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/qq_37221991/article/details/83270696

前言:

我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式。同时数据库又是个高并发的应用,同一时间会有大量的并发访问,如果加锁过度,会极大的降低并发处理能力。所以对于加锁的处理,可以说就是数据库对于事务处理的精髓所在。这里通过分析MySQL中InnoDB引擎的加锁机制,来抛砖引玉,让读者更好的理解,在事务处理中数据库到底做了什么。

1. MySQL中锁的种类

MySQL中锁的种类很多,有常见的表锁和行锁,也有新加入的Metadata Lock等等。

表锁是对一整张表加锁,虽然可分为读锁和写锁,但毕竟是锁住整张表,会导致并发能力下降,一般是做ddl处理时使用。

行锁则是锁住数据行,这种加锁方法比较复杂,但是由于只锁住有限的数据,对于其它数据不加限制,所以并发能力强,MySQL一般都是用行锁来处理并发事务。这里主要讨论的也就是行锁,行级锁分为共享锁和排他锁两种。

1.1 共享锁(Share Lock)

共享锁又称读锁,是读取操作创建的锁。其他用户可以并发读取数据,但任何事务都不能对数据进行修改(获取数据上的排他锁),直到已释放所有共享锁。

如果事务T对数据A加上共享锁后,则其他事务只能对A再加共享锁,不能加排他锁。获准共享锁的事务只能读数据,不能修改数据。

用法

SELECT ... LOCK IN SHARE MODE;

在查询语句后面增加LOCK IN SHARE MODE,Mysql会对查询结果中的每行都加共享锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请共享锁,否则会被阻塞。其他线程也可以读取使用了共享锁的表,而且这些线程读取的是同一个版本的数据。

1.2 排他锁(eXclusive Lock)

排他锁又称写锁,如果事务T对数据A加上排他锁后,则其他事务不能再对A加任任何类型的封锁。获准排他锁的事务既能读数据,又能修改数据。

用法

SELECT ... FOR UPDATE;

在查询语句后面增加FOR UPDATE,Mysql会对查询结果中的每行都加排他锁,当没有其他线程对查询结果集中的任何一行使用排他锁时,可以成功申请排他锁,否则会被阻塞。

对于insert、update、delete,InnoDB会自动给涉及的数据加排他锁(X);对于一般的Select语句,InnoDB不会加任何锁,事务可以通过以下语句给显示加共享锁或排他锁。

共享锁:SELECT ... LOCK IN SHARE MODE;

排他锁:SELECT ... FOR UPDATE;

2. 事务的四种隔离级别

在数据库操作中,为了有效保证并发读取数据的正确性,提出的事务隔离级别。我们的数据库锁,也是为了构建这些隔离级别存在的。

**图片**

  • 未提交读(Read Uncommitted):允许脏读,也就是可能读取到其他会话中未提交事务修改的数据

  • 提交读(Read Committed):只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)

  • 可重复读(Repeated Read):可重复读。在同一个事务内的查询都是事务开始时刻一致的,InnoDB默认级别。在SQL标准中,该隔离级别消除了不可重复读,但是还存在幻象读

  • 串行读(Serializable):完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞

2.1 未提交读(Read uncommitted)

未提交读(READ UNCOMMITTED)是最低的隔离级别。通过名字我们就可以知道,在这种事务隔离级别下,一个事务可以读到另外一个事务未提交的数据。

2.1.1 未提交读的数据库锁情况(实现原理)

事务在读数据的时候并未对数据加锁。

事务在修改数据的时候只对数据增加行级共享锁。

2.1.2 现象:

事务1读取某行记录时,事务2也能对这行记录进行读取、更新(因为事务一并未对数据增加任何锁)

当事务2对该记录进行更新时,事务1再次读取该记录,能读到事务2对该记录的修改版本(因为事务二只增加了共享读锁,事务一可以再增加共享读锁读取数据),即使该修改尚未被提交。

事务1更新某行记录时,事务2不能对这行记录做更新,直到事务1结束。(因为事务一对数据增加了共享读锁,事务二不能增加排他写锁进行数据的修改)

2.1.3 举例

下面还是借用我在数据库的读现象浅析一文中举的例子来说明在未提交读的隔离级别中两个事务之间的隔离情况。

**图片**

事务一共查询了两次,在两次查询的过程中,事务二对数据进行了修改,并未提交(commit)。但是事务一的第二次查询查到了事务二的修改结果。在数据库的读现象浅析中我们介绍过,这种现象我们称之为脏读。

所以,未提交读会导致脏读

2.2 提交读(Read committed)

提交读(READ COMMITTED)也可以翻译成读已提交,通过名字也可以分析出,在一个事务修改数据过程中,如果事务还没提交,其他事务不能读该数据。

2.2.1 提交读的数据库锁情况

事务对当前被读取的数据加 行级共享锁(当读到时才加锁),一旦读完该行,立即释放该行级共享锁;

事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加 行级排他锁,直到事务结束才释放。

2.2.2 现象:

事务1在读取某行记录的整个过程中,事务2都可以对该行记录进行读取(因为事务一对该行记录增加行级共享锁的情况下,事务二同样可以对该数据增加共享锁来读数据。)。

事务1读取某行的一瞬间,事务2不能修改该行数据,但是,只要事务1读取完改行数据,事务2就可以对该行数据进行修改。(事务一在读取的一瞬间会对数据增加共享锁,任何其他事务都不能对该行数据增加排他锁。但是事务一只要读完该行数据,就会释放行级共享锁,一旦锁释放,事务二就可以对数据增加排他锁并修改数据)

事务1更新某行记录时,事务2不能对这行记录做更新,直到事务1结束。(事务一在更新数据的时候,会对该行数据增加排他锁,知道事务结束才会释放锁,所以,在事务二没有提交之前,事务一都能不对数据增加共享锁进行数据的读取。所以,提交读可以解决脏读的现象)

2.2.3 举例

**图片**

在提交读隔离级别中,在事务二提交之前,事务一不能读取数据。只有在事务二提交之后,事务一才能读数据。

但是从上面的例子中我们也看到,事务一两次读取的结果并不一致,所以提交读不能解决不可重复读的读现象。

简而言之,提交读这种隔离级别保证了读到的任何数据都是提交的数据,避免了脏读(dirty reads)。但是不保证事务重新读的时候能读到相同的数据,因为在每次数据读完之后其他事务可以修改刚才读到的数据。

另外我们要注意到,id是有索引的,如果是没有索引的age呢?

update user set sex='m' where age = 15;

那么MySQL会给整张表的所有数据行的加行锁

但在实际使用过程当中,MySQL做了一些改进,在MySQL Server过滤条件,发现不满足后,会调用unlock_row方法,把不满足条件的记录释放锁 (违背了二段锁协议的约束)。这样做,保证了最后只会持有满足条件记录上的锁,但是每条记录的加锁操作还是不能省略的。可见即使是MySQL,为了效率也是会违反规范的。(参见《高性能MySQL》中文第三版p181)

这种情况同样适用于MySQL的默认隔离级别RR。所以对一个数据量很大的表做批量修改的时候,如果无法使用相应的索引,MySQL Server过滤数据的的时候特别慢,就会出现虽然没有修改某些行的数据,但是它们还是被锁住了的现象。

2.3 可重复读(Repeatable reads)

可重复读(REPEATABLE READS),由于提交读隔离级别会产生不可重复读的读现象。所以,比提交读更高一个级别的隔离级别就可以解决不可重复读的问题。这种隔离级别就叫可重复读(这名字起的是不是很任性!!)

2.3.1 可重复读的数据库锁情况

事务在读取某数据的瞬间(就是开始读取的瞬间),必须先对其加 行级共享锁,直到事务结束才释放;

事务在更新某数据的瞬间(就是发生更新的瞬间),必须先对其加 行级排他锁,直到事务结束才释放。

2.3.2 现象

事务1在读取某行记录的整个过程中,事务2都可以对该行记录进行读取(因为事务一对该行记录增加行级共享锁的情况下,事务二同样可以对该数据增加共享锁来读数据。)。

事务1在读取某行记录的整个过程中,事务2都不能修改该行数据(事务一在读取的整个过程会对数据增加共享锁,直到事务提交才会释放锁,所以整个过程中,任何其他事务都不能对该行数据增加排他锁。所以,可重复读能够解决不可重复读的读现象)

事务1更新某行记录时,事务2不能对这行记录做更新,直到事务1结束。(事务一在更新数据的时候,会对该行数据增加排他锁,知道事务结束才会释放锁,所以,在事务二没有提交之前,事务一都能不对数据增加共享锁进行数据的读取。所以,提交读可以解决脏读的现象)

2.3.3 举例

**图片**

在上面的例子中,只有在事务一提交之后,事务二才能更改该行数据。所以,只要在事务一从开始到结束的这段时间内,无论他读取该行数据多少次,结果都是一样的。

从上面的例子中我们可以得到结论:可重复读隔离级别可以解决不可重复读的读现象。但是可重复读这种隔离级别中,还有另外一种读现象他解决不了,那就是幻读。看下面的例子:

**图片**

上面的两个事务执行情况及现象如下:

1.事务一的第一次查询条件是age BETWEEN 10 AND 30;如果这是有十条记录符合条件。这时,他会给符合条件的这十条记录增加行级共享锁。任何其他事务无法更改这十条记录。

2.事务二执行一条sql语句,语句的内容是向表中插入一条数据。因为此时没有任何事务对表增加表级锁,所以,该操作可以顺利执行。

3.事务一再次执行SELECT * FROM users WHERE age BETWEEN 10 AND 30;时,结果返回的记录变成了十一条,比刚刚增加了一条,增加的这条正是事务二刚刚插入的那条。

所以,事务一的两次范围查询结果并不相同。这也就是我们提到的幻读。

2.4 可序列化(Serializable)

可序列化(Serializable)是最高的隔离级别,前面提到的所有的隔离级别都无法解决的幻读,在可序列化的隔离级别中可以解决。

我们说过,产生幻读的原因是事务一在进行范围查询的时候没有增加范围锁(range-locks:给SELECT 的查询中使用一个“WHERE”子句描述范围加锁),所以导致幻读。

可序列化的数据库锁情况
事务在读取数据时,必须先对其加 表级共享锁 ,直到事务结束才释放;

事务在更新数据时,必须先对其加 表级排他锁 ,直到事务结束才释放。

2.4.1 现象

事务1正在读取A表中的记录时,则事务2也能读取A表,但不能对A表做更新、新增、删除,直到事务1结束。(因为事务一对表增加了表级共享锁,其他事务只能增加共享锁读取数据,不能进行其他任何操作)

事务1正在更新A表中的记录时,则事务2不能读取A表的任意记录,更不可能对A表做更新、新增、删除,直到事务1结束。(事务一对表增加了表级排他锁,其他事务不能对表增加共享锁或排他锁,也就无法进行任何操作)

虽然可序列化解决了脏读、不可重复读、幻读等读现象。但是序列化事务会产生以下效果:

1.无法读取其它事务已修改但未提交的记录。

2.在当前事务完成之前,其它事务不能修改目前事务已读取的记录。

3.在当前事务完成之前,其它事务所插入的新记录,其索引键值不能在当前事务的任何语句所读取的索引键范围中。

四种事务隔离级别从隔离程度上越来越高,但同时在并发性上也就越来越低。之所以有这么几种隔离级别,就是为了方便开发人员在开发过程中根据业务需要选择最合适的隔离级别。

参考文章
http://www.hollischuang.com/archives/943
https://tech.meituan.com/innodb_lock.html
http://www.hollischuang.com/archives/923

展开阅读全文

数据库锁,事务使用正确?帮忙优化。

08-09

今天要写一个积分还礼的东西,通过存储过程实现,rn存储过程名ExchangeScore 传入用户ID,和兑换所需积分,用到的锁,还有事务,rn本人水平有限,请高手帮忙,看看代码数据库锁防止并发修改时启到作用了吗?rn我写的代码很是拙劣,请再高手帮忙优化一下,感激不尽!rn代码如下:[code=SQL]rnCREATE PROCEDURE ExchangeScorern @UserID int,rn @Socre bigintrnASrnBEGINrn SET NOCOUNT ON;rn declare @returnStr varchar(20)rn declare @ScoreTotal bigintrn declare @ChinaChessScore bigint --将从QPChinaChessDB 库的积分rn declare @GameScoreScore bigint rn declare @SanDaiScore bigintrn declare @SparrowDZScore bigintrn declare @WaKengScore bigintrn set @ChinaChessScore=isnull((SELECT Score FROM QPChinaChess.dbo.GameScoreInfo WITH (UPDLOCK) where UserID=@UserID),0)rn set @GameScoreScore=isnull((SELECT Score FROM QPGameScore.dbo.GameScoreInfo WITH (UPDLOCK) where UserID=@UserID),0)rn set @SanDaiScore=isnull((SELECT Score FROM QPSanDai.dbo.GameScoreInfo WITH (UPDLOCK) where UserID=@UserID),0)rn set @SparrowDZScore=isnull((SELECT Score FROM QPSparrowDZ.dbo.GameScoreInfo WITH (UPDLOCK) where UserID=@UserID),0)rn set @WaKengScore=isnull((SELECT Score FROM QPWaKeng.dbo.GameScoreInfo WITH (UPDLOCK) where UserID=@UserID),0)rn set @ScoreTotal=(@ChinaChessScore+@GameScoreScore+@SanDaiScore+@SparrowDZScore+@WaKengScore)rn if @ScoreTotal<@Socre rn beginrn set @returnStr='您的积分还不够,继续努力吧!'rn endrn elsern beginrn declare @errorSum intrn declare @runTotal tinyintrn declare @avgScore int --平均 从每个数据库删除的积分rn declare @remaScore int --余数 rn rn if @Socre%5=0 rn beginrn set @avgScore=Convert(int,@Socre/5)rn set @remaScore=0rn endrn elsern beginrn set @avgScore=Convert(int,@Socre/5)rn set @remaScore=Convert(int,@Socre%5)rn endrn begin tran rn if @Socre<=@ScoreTotalrn beginrn set @errorSum=0rn set @Socre=@Socre-@ChinaChessScorern update QPChinaChess.dbo.GameScoreInfo set Score =0 where UserID=@UserIDrn set @errorSum=@errorSum+@@errorrn if @Socre>0 rn beginrn set @Socre=@Socre-@GameScoreScorern update QPGameScore.dbo.GameScoreInfo set Score=0 where UserID=@UserIDrn set @errorSum=@errorSum+@@errorrn if @Socre>0 rn begin rn set @Socre=@Socre-@SanDaiScorern update QPSanDai.dbo.GameScoreInfo set Score=0 where UserID=@UserIDrn set @errorSum=@errorSum+@@errorrn if @Socre>0 rn beginrn set @Socre=@Socre-@SparrowDZScore rn update QPSparrowDZ.dbo.GameScoreInfo set Score=0 where UserID=@UserIDrn set @errorSum=@errorSum+@@errorrn if @Socre>0 rn beginrn set @WaKengScore=@WaKengScore-@Socrern set @Socre=@Socre-@Socre rn update QPWaKeng.dbo.GameScoreInfo set Score=@WaKengScore where UserID=@UserIDrn set @errorSum=@errorSum+@@errorrn endrn end rn endrn endrn endrn if @Socre<>0 or @WaKengScore<0 or @errorSum<>0rn begin rn rollback tranrn set @returnStr='很遗憾,出错了,请重试。'rn end rn elsern beginrn commit tranrn set @returnStr='兑换成功,已从您的积分中扣除相应积分。'rn endrn endrn select @returnStr as ResultrnENDrnGOrn[/code] 论坛

一个事务与锁的问题

10-10

我在“大本营”中看到一篇“ 也谈SQL SERVER 的锁”的文章(DeD(原作)),有些内容不懂,请各位解释。rnrn我不懂的内容如下:rnrnrn两个用户同时保存新增的数据,我们的程序开始是这样处理rn cn.BeginTransrn cn.Execute "insert into tableA ....."rn Set rs = cn.Execute("select count(*) from tableA where ...")rn If rs.RecordCount > 0 Thenrn '表A 的字段A不能从复rn cn.RollbackTransrn Elsern cn.CommitTransrn End Ifrnrn当SQL SERVER 在执行INSERT 命令时如果我们不添加任何参数时 数据库默认申请一个 IX 锁 给表Arn这时候我们来分析上面的程序,当第一个用户执行 cn.Execute "insert into tableA ....." Connectionrn向数据库申请了一个 IX 锁 给表A ,与此同时当第二个用户执行 cn.Execute "insert into tableA ....." Connection 也向数据库也成功地申请了一个 IX 锁 给表A ,但是当执行 rnSet rs = cn.Execute("select count(*) from tableA where ...") rn这一句的时候就会有问题产生,我们假设第一个用户先一步执行 ,由于SELECT命令需要向数据库申请一个rnS 锁给表A,但是由于这时候表A已经存在一个IX锁并且属于另外一个连接因此他只好在此等候。紧接着第二个rn用户也执行rnSet rs = cn.Execute("select count(*) from tableA where ...") rn他也会向数据库申请一个S 锁给表A ,这时候数据就会自动结束较晚申请IX锁的连接同时回滚这个事务rnrnrnrn请问:为何第二个事务会失败回滚,而不会象第一个事务一样等待,可能我对S、IS、IX等类型的锁理解不够。 论坛

没有更多推荐了,返回首页