澳门新浦京娱乐场网站-www.146.net-新浦京娱乐场官网
做最好的网站

澳门新浦京娱乐场网站:谈死锁的督察剖判解决

1 背景

    MySQL的并发处理机制,有MVCC及锁机制来处理,上篇简要说明了 MVCC及隔离级别:mysql的并发处理机制_上篇 ,这篇来说说mysql下的锁。澳门新浦京娱乐场网站 1

    MySQL的并发处理机制,有MVCC及锁机制来处理,上篇简要说明了 MVCC及隔离级别:mysql的并发处理机制_上篇 ,这篇来说说mysql下的锁。澳门新浦京娱乐场网站 2

 

1.1 报警情况

最近整理笔记,打算全部迁移到EVERNOTE。整理到锁这一部分,里边刚好有个自己记录下来的案例,重新整理分享下给大家。澳门新浦京娱乐场网站 3

某日中午,收到报警短信,DB死锁异常,单分钟死锁120个。

死锁的xml文件如下:

 1 <deadlock-list>
 2 <deadlock victim="process810b00cf8">
 3 <process-list>
 4 <process id="process810b00cf8" taskpriority="0" logused="0" waitresource="RID: 13:1:1541136:62" waittime="7682" ownerId="3396587959" transactionname="UPDATE" lasttranstarted="2016-01-08T12:03:51.067" XDES="0xa99746d08" lockMode="U" schedulerid="41" kpid="17308" status="suspended" spid="108" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-01-08T12:03:51.067" lastbatchcompleted="2016-01-08T12:03:51.067" lastattention="1900-01-01T00:00:00.067" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="test-server" hostpid="1433" loginname="xinysu" isolationlevel="read committed (2)" xactid="3396587959" currentdb="13" lockTimeout="4294967295" clientoption1="671098976" clientoption2="390200">
 5 <executionStack>
 6 <frame procname="adhoc" line="7" stmtstart="214" stmtend="484" sqlhandle="0x020000003acf4f010561e479685209fb09a7fd15239977c60000000000000000000000000000000000000000">
 7 UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode </frame>
 8 </executionStack>
 9 <inputbuf>
10 declare @SeqCode varchar(60)
11 declare @ReturnNum bigint
12 set @SeqCode='CGJS20160106'
13 while(1=1)
14 begin
15 UPDATE FinanceReceiptNoRule SET NowSeqValue=@ReturnNum,ISRUNNING='0',LastWriteTime=GETDATE() WHERE IsRunning='1' AND SeqCode=@SeqCode
16 end </inputbuf>
17 </process>
18 <process id="process18fd5d8cf8" taskpriority="0" logused="248" waitresource="KEY: 13:72057594040090624 (b3ade7c5980c)" waittime="4" ownerId="3396522828" transactionname="user_transaction" lasttranstarted="2016-01-08T12:03:05.310" XDES="0x18c1db63a8" lockMode="U" schedulerid="57" kpid="16448" status="suspended" spid="161" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2016-01-08T12:03:58.737" lastbatchcompleted="2016-01-08T12:03:33.847" lastattention="2016-01-08T12:03:33.850" clientapp="Microsoft SQL Server Management Studio - 查询" hostname="test-server" hostpid="1433" loginname="xinysu" isolationlevel="read committed (2)" xactid="3396522828" currentdb="13" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200">
19 <executionStack>
20 <frame procname="adhoc" line="6" stmtstart="210" stmtend="400" sqlhandle="0x020000001b4f23368af7bba99098c10dec46585804f1b4ce0000000000000000000000000000000000000000">
21 Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' </frame>
22 </executionStack>
23 <inputbuf>
24 declare @SeqCode varchar(60)
25 declare @ReturnNum bigint
26 set @SeqCode='CGJS20160106'
27 while(1=1)
28 begin
29 Update dbo.FinanceReceiptNoRule Set [IsRunning]='1' where SeqCode=@SeqCode and IsRunning='0' 
30 end
31 </inputbuf>
32 </process>
33 </process-list>
34 <resource-list>
35 <ridlock fileid="1" pageid="1541136" dbid="13" objectname="fin_test.dbo.FinanceReceiptNoRule" id="lock51e8a3980" mode="X" associatedObjectId="72057594040025088">
36 <owner-list>
37 <owner id="process18fd5d8cf8" mode="X" />
38 </owner-list>
39 <waiter-list>
40 <waiter id="process810b00cf8" mode="U" requestType="wait" />
41 </waiter-list>
42 </ridlock>
43 <keylock hobtid="72057594040090624" dbid="13" objectname="fin_test.dbo.FinanceReceiptNoRule" indexname="PK_FINANCERECEIPTNORULE" id="lock7b2c6bc80" mode="U" associatedObjectId="72057594040090624">
44 <owner-list>
45 <owner id="process810b00cf8" mode="U" />
46 </owner-list>
47 <waiter-list>
48 <waiter id="process18fd5d8cf8" mode="U" requestType="wait" />
49 </waiter-list>
50 </keylock>
51 </resource-list>
52 </deadlock>
53 </deadlock-list>

表格结构跟模拟数据如下:

 1 --涉及表格:
 2 CREATE TABLE [dbo].[FinanceReceiptNoRule](
 3 [SeqCode] [varchar](60) NOT NULL,
 4 [NowSeqValue] [bigint] NULL,
 5 [SeqDate] [varchar](14) NOT NULL,
 6 [IsRunning] [varchar](1) NULL,
 7 [LastWriteTime] [datetime] NULL,
 8 [Prefix] [varchar](4) NULL
 9 ) ON [PRIMARY]
10 GO
11 --数据模拟
12 INSERT [dbo].[FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (N'TEST20150108', 1469, N'20150108', N'0', CAST(N'2015-01-08 05:05:49.163' AS DateTime), N'TEST')
13 GO
14 INSERT [dbo].[FinanceReceiptNoRule] ([SeqCode], [NowSeqValue], [SeqDate], [IsRunning], [LastWriteTime], [Prefix]) VALUES (N'TEST20150109', 1377, N'20150109', N'0', CAST(N'2015-01-09 04:50:26.610' AS DateTime), N'TEST')
15 GO
16  
17 ALTER TABLE [dbo].[FinanceReceiptNoRule] ADD CONSTRAINT [pk_FinanceReceiptNoRule] PRIMARY KEY NONCLUSTERED 
18 (
19 [SeqCode] ASC
20 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
21 GO

    温馨提示:下文有几个表格长度较长,右下角的博文导航目录会挡道,浏览时,可以点击 导航目录的左下角按钮收缩目录:澳门新浦京娱乐场网站 4

    温馨提示:下文有几个表格长度较长,右下角的博文导航目录会挡道,浏览时,可以点击 导航目录的左下角按钮收缩目录:澳门新浦京娱乐场网站 5

在如今这个云计算,大数据,移动互联网大行其道的时代,各种NoSQL数据库MongoDb、redis、HBase等使用的越来越广泛,大有替代关系型数据库的趋势。但是关系型数据库真的已经落伍了吗?答案是否定的。非关系型数据库不支持ACID属性,不支持事务,无法适应复杂查询的缺点。关系型数据库凭借其强一致性的特点,注定了在类似银行转账,订单支付等场景中,还是唯一的选择。众所周知,SQLSERVER通过锁来提供ACID属性,处理并发访问,本文尝试通过对锁机制的一些学习,让我们明白数据库查询超时,死锁等问题是如何产生的,及相关解决方法。

1.2 如何监控

捕获死锁有多种方式可以捕获,这里介绍2种:SQL SERVER Profiler工具跟Extended Events。Profiler相对比较耗资源,但是由于只监控死锁这一项,所以性能影响不是很大,其可视化界面较易上手;Extended Events耗费资源较少,实时记录到倒数第二个死锁,同时需要SQL语句来分析查询记录文件。

如何使用 Profiler监控?
打开 SSMS,点击<工具>,选择 <SQL Server Profiler>,如下图。

澳门新浦京娱乐场网站 6

登录到需要监控的DB实例,填写相应的跟踪属性,首先是<常规>页面,如下图。这里注意2个方面,第一,选择 <TSQL-Locks>模板,这个模板即可以用来监控死锁,也可以拿来观察 锁申请与释放情况,非常详细,有事没事可以多拿来看SELECT UPDATE DELETE等语句对锁的申请及释放情况;第二,监控结果存储,建议可以存放到某个表格中去,方便定期分析与统计。

澳门新浦京娱乐场网站 7

接着填写<事件选择>项,只需要选择 <deadlock graph> Events,其他都不需要打勾,最后点击运行就可以开始监控了。

 澳门新浦京娱乐场网站 8

可以用一个万年常用的例子来检查是否监控正常,开3个查询窗口,按照以下顺序执行则会发生资源占用及申请互斥导致死锁,执行完第5步,等待1-3s则发生死锁。脚本提供如下:

澳门新浦京娱乐场网站 9澳门新浦京娱乐场网站 10

 1 --session 1
 2 CREATE TABLE Test_DL(
 3 id int not null primary key ,
 4 name varchar(100));
 5 
 6 INSERT INTO Test_DL(id,name) select 1,'a';
 7 INSERT INTO Test_DL(id,name) select 2,'b';
 8 
 9 --session2 2 2 2 2 2 2 2 2 2 
10 BEGIN TRANSACTION
11 UPDATE Test_DL SET Name='a-test' WHERE ID=1
12 
13 --session3 3 3 3 3 3 3 3 3 3 
14 BEGIN TRANSACTION
15 UPDATE Test_DL SET Name='b-test' WHERE ID=2
16 
17 --session2 2 2 2 2 2 2 2 2 2 
18  SELECT * FROM Test_DL WHERE ID=2
19 
20 --session3 3 3 3 3 3 3 3 3 3
21  SELECT * FROM Test_DL WHERE ID=1

模拟死锁SQL

澳门新浦京娱乐场网站 11

监控到的死锁界面如下:

澳门新浦京娱乐场网站 12

如何使用Extended Events监控?

建立扩展事件监控的脚本如下:(扩展事件很赞,2012版支持可视化操作,感兴趣的可以上 MSDN了解:

1 CREATE EVENT SESSION [DeadLock] ON SERVER 
2 ADD EVENT sqlserver.xml_deadlock_report 
3 ADD TARGET package0.event_file(SET filename=N'F:eventsdeadlockdeadlock.xel',max_file_size=(20)),
4 ADD TARGET package0.ring_buffer(SET max_events_limit=(100),max_memory=(10240),occurrence_number=(50))
5 WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
6 GO

查询SQL如下,这里需要注意:查询是基于buffer还是基于filer分析,一般buffer存储的个数都是有限的,比如上文我们只分配了4M存储,file分析则是完整的,但是要看保留的文件个数。这里我们给出buffer的查询SQL如下,file的查询大家感兴趣的可以动手写下。

DECLARE @deadlock_xml XML
SELECT @deadlock_xml=(
                       SELECT 
                              ( 
                                SELECT
                                      CONVERT(XML, target_data)
                                FROM sys.dm_xe_session_targets st
                                JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
                                WHERE s.name = 'deadlock' AND st.target_name = 'ring_buffer'
                              ) AS [x]
                       FOR XML PATH('') , TYPE
                      )

SELECT 
dateadd(hour, 6,tb.col.value('@timestamp[1]','varchar(max)')) TimePoint,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[1]','VARCHAR(MAX)') statement_parameter_k,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[2]','VARCHAR(MAX)') statement_k,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[3]','VARCHAR(MAX)') statement_parameter,
tb.col.value('(data/value/deadlock/process-list/process/executionStack/frame)[4]','VARCHAR(MAX)') [statement],
tb.col.value('(data/value/deadlock/process-list/process/@waitresource)[1]','VARCHAR(MAX)') waitresource_k,
tb.col.value('(data/value/deadlock/process-list/process/@waitresource)[2]','VARCHAR(MAX)') waitresource,
tb.col.value('(data/value/deadlock/process-list/process/@isolationlevel)[1]','VARCHAR(MAX)') isolationlevel_k,
tb.col.value('(data/value/deadlock/process-list/process/@isolationlevel)[2]','VARCHAR(MAX)') isolationlevel,
tb.col.value('(data/value/deadlock/process-list/process/@waittime)[1]','VARCHAR(MAX)') waittime_k,
tb.col.value('(data/value/deadlock/process-list/process/@waittime)[2]','VARCHAR(MAX)') waittime,
tb.col.value('(data/value/deadlock/process-list/process/@clientapp)[1]','VARCHAR(MAX)') clientapp_k,
tb.col.value('(data/value/deadlock/process-list/process/@clientapp)[2]','VARCHAR(MAX)') clientapp,
tb.col.value('(data/value/deadlock/process-list/process/@hostname)[1]','VARCHAR(MAX)') hostname_k,
tb.col.value('(data/value/deadlock/process-list/process/@hostname)[2]','VARCHAR(MAX)') hostname
FROM @deadlock_xml.nodes('//event') as tb(col)

这个SQL可以查询的出非常详细的资源争夺情况,如果想要有效的使用扩展事件,建议大家详细查看下官网的xml语法(SQL SERVER对xml的支持也是棒棒哒,期待2016版中的json支持)

澳门新浦京娱乐场网站 13

是不是很清晰,一目了然,有了这个就可以去分析拉!

 

 

在开始谈有关锁的内容前,我们先看几个我们在日常开发中经常碰到的场景:

2 分析

根据xml文件内容或者扩展事件的监控内容,都可以整理为以下信息(开头的那个死锁分析):

澳门新浦京娱乐场网站 14

查看事务1及事务2的执行计划如下:

澳门新浦京娱乐场网站 15

 

 结合表格及执行计划,可以大致推测死锁过程:

会话1:

  • 根据主键SeqCode查找到键值所在的 索引页 Index_Page,找到该页上面的 keyhashvalue 键值行 Index_key,对Index_Page持有IU锁,对Index_key持有U锁;
  • 由于该表是堆表,bookmark lookup是通过 RID查找 ,即通过行标识符查找,找到RID所对应的行数据所在的 数据页  Data_Page,然后在该页面上找到RID指向槽号上的行数据,对该行数据持有U锁;
  • 这个时候,已经查找到了需要更新的行数据,可以把数据页 Data_Page上的IU锁 升级为IX锁,RID指向的行数据 从U锁升级为X锁,升级结束后,释放索引页跟键值行上面的 IU锁及U锁。
  • 则此时,会话1 持有 Data_Page 上的IX锁、RID行上的 X锁.

这个过程中,刚好会话2进行这样的锁申请:

  • 找出事务2中持有锁资源澳门新浦京娱乐场网站 16是哪个索引,可以根据sys.partitions 可以查看到72057594038910976是主键pk_FinanceReceiptNoRule,主键列是:SeqCode。
  • 根据主键SeqCode查找到键值所在的 索引页 Index_Page,找到该页上面的 键值行 Index_key,对Index_Page持有IU锁,对Index_key持有U锁;
  • 由于该表是堆表,bookmark lookup是通过 RID查找 ,即通过行标识符查找,找到RID所对应的行数据所在的 数据页  Data_Page,然后在该页面上找到RID指向槽号上的行数据,准备该行数据持有U锁,但是发现RID行上被会话1持有了X锁,导致其申请 U锁 Timeout。
  • 则此时 会话2 持有 Index_Page上的IU锁、Index_key上的U锁、Data_Page上的IU锁,请求 RID行的 U锁。

假设这个时候,会话1 中又执行了一次update操作(同一个事务中):

  • 根据主键SeqCode查找到键值所在的 索引页 Index_Page,找到该页上面的 键值行 Index_key,对Index_Page持有IU锁,准备对Index_key持有U锁,但是发现 Index_key被会话2持有了U锁。

那么这个时候死锁就产生了(详见下图):

  • 会话1 持有 Data_Page 上的IX锁、RID行上的 X锁,申请 Index_key 的U锁(等待会话2释放)
  • 会话2 持有 Index_Page上的IU锁、Index_key上的U锁、Data_Page上的IU锁,请求 RID行的 U锁(等待会话1释放)

澳门新浦京娱乐场网站 17



  1. 这个查询我索引该加的都加了,测试库刚同步的生产库,在测试环境查询秒出,为什么到了生产环境就会经常超时呢?

  2. 我的某个业务逻辑上线后一切正常,运行了几个月后,开始死锁越来越频繁了,但是数据量也不大,为什么呢?

3 解决

想法子除去RID查找,直接index就找到数据,就不会发生这个死锁,也就是,在主键上面重新建立聚集索引,丢弃原先的非聚集索引主键。因为这样排除了RID的U锁申请与持有,直接是保持X锁 直至事务结束,同时可以直接根据主键来修改键值所在的数据页,减少的RID查询行的时间。

修改后的执行计划如下:

澳门新浦京娱乐场网站 18

其锁申请释放的流程如下(详见截图):

  • 根据主键SeqCode查找到键值所在的 索引页 Index_Page,找到该页上面的 keyhashvalue 键值行 Index_key,对Index_Page持有IU锁,对Index_key持有U锁;
  • 由于该表已经是聚集索引表,主键所在的页上包含 行数据,则可以直接 对Index_Page持有IU锁升级为IX锁,对Index_key持有U锁升级为X锁,避免了RID逐个找行数据的锁申请

澳门新浦京娱乐场网站 19

 



以上两种情况或许是因为锁在其中"作怪"。下面我们就开始从一些锁的基本概念谈起,了解SQLSERVER的锁机制,死锁成因以及死锁的预防。为了让大家能对锁有一个更深入的了解,本文前半章谈的比较多的是一些基本概念。老鸟可以自动略过。

 

 

 

    如果转载,请注明博文来源: www.cnblogs.com/xinysu/   ,版权归 博客园 苏家小萝卜 所有。望各位支持!

    如果转载,请注明博文来源: www.cnblogs.com/xinysu/   ,版权归 博客园 苏家小萝卜 所有。望各位支持!

一.锁的一些基本概念

 

 

为什么需要锁?



在任何多用户的数据库中,必须有一套用于数据修改的一致的规则,当两个不同的进程试图同时修改同一份数据时,数据库管理系统(DBMS)负责解决它们之间潜在的冲突。任何关系数据库必须支持事务的ACID属性,所以在开始了解锁之前,首先简单了解一下数据库事务和事务的ACID属性。



  • 原子性(Atomicity):原子性意味着数据库中的事务执行是作为原子。即不可再分,整个语句要么执行,要么不执行。

  • 一致性(Consistency):在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏。(唯一约束,外键约束,Check约束等)和触发器设置.这一点是由SQL SERVER进行保证的.

  • 隔离性(Isolation):事务的执行是互不干扰的,一个事务不可能看到其他事务运行时,中间某一时刻的数据。

  • 持久性(Durability): 持久性表示在某个事务的执行过程中,对数据所作的所有改动都必须在事务成功结束前保存至某种物理存储设备。这样可以保证,所作的修改在任何系统瘫痪时不至于丢失。

 1 Innodb的锁

    在innodb中,有4种类型的锁:IX、X、IS及S锁,其说明如下:

 

类型 说明 场景
S 共享锁 针对于RS隔离级别的查询或者添加Lock in share mode的SELECT查询而产生的锁
X 排它锁 针对于update、delete、insert操作而产生的锁
IS 意向共享锁 表级别的锁,在添加S锁之前对表格添加IS锁
IX 意向排他锁 表级别的锁,在添加X锁之前对表格添加IX锁

 1 Innodb的锁

    在innodb中,有4种类型的锁:IX、X、IS及S锁,其说明如下:

 

类型 说明 场景
S 共享锁 针对于RS隔离级别的查询或者添加Lock in share mode的SELECT查询而产生的锁
X 排它锁 针对于update、delete、insert操作而产生的锁
IS 意向共享锁 表级别的锁,在添加S锁之前对表格添加IS锁
IX 意向排他锁 表级别的锁,在添加X锁之前对表格添加IX锁

理论上所有的事务之间应该是完全隔离的。但是实际上,要实现完全隔离的成本实在是太高(必须是序列化的隔离等级才能完全隔离)。所以, SQL Server通过锁,就像十字路口的红绿灯那样,告诉所有并发的连接,在同一时刻上,那些资源可以读取,那些资源可以修改。当一个事务需要访问的资源加了其所不兼容的锁,SQL Server会阻塞当前的事务来达成所谓的隔离性。直到其所请求资源上的锁被释放。

1.1 锁定兼容情况

  四个锁之间的兼容性,需要分成两种情况来讨论,锁粒度小于表级别的锁的兼容情况,表级的锁兼容情况。

  1. 锁粒度小于表级别的锁的兼容情况

澳门新浦京娱乐场网站 20

对于这两行锁的兼容说明如下:

  • 假设有一行数据,添加了行锁S锁,那么这个行数据,可以提供给其他事务进行S锁的申请和添加,但是不支持其他事务对这一行进行X锁的申请和添加。比如,事务A,对 pk100 这一行进行了 查询操作并添加了S锁,那么其他事务仍然可以对这一行数据进行查询,但是不能对这行数据进行 UPDATE 跟 DELETE 操作,会处于锁等待情况,直到该事务A结束并释放S锁;
  • 假设有一行数据,添加了X锁,那么这个行数据,不允许其他事务对这一行数据进行加锁。比如,事务A,对pk100这一行进行了UPDATE操作,那么其他事务在事务A没有结束之前,都无法对这一行数据申请 S锁。

表级的锁兼容情况

澳门新浦京娱乐场网站 21

对于表级别的锁兼容性如下:

当一个表格持有S表锁时,不需要其他事务对该表格申请X锁跟IX锁,但是允许申请S跟IS锁。比如,事务A对表格tba全表读,加了S表锁,期间支持其他事务对tba全表读(申请S表锁成功)、支持其他事务对tba行数据查询(申请IS表锁成功),但是不支持对表格全表的修改操作(申请X表锁等待)跟不支持对表格行数据修改操作(申请IX表锁等待);

当一个表格持有X表锁时,持有锁期间,不支持其他所有锁的申请;

当一个表格持有IS表锁时,允许申请 S表锁、IS表锁、IX表锁,但是不支持X表锁申请。

  • 比如,事务A对表格tba 查询了 id = 10(id为主键)这一行数据,这个时候,表格tba持有IS表锁,id = 10 这一行持有 S 行锁,期间,支持其他事务对 tba 全表查询(申请表锁S成功)或者 基于索引查找(申请表锁IS成功)
  • 如果需要对行 id = 20 进行数据修改,则会先申请 tba 的表锁 IX(申请成功),然后再申请id=20行锁X (申请成功);如果需要对 id = 10 这一行数据进行修改,则会申请 tba的表锁 IX(表锁申请成功),然后申请 id = 10 的行锁X(申请堵塞,因为 id = 10 正持有S锁);
  • 如果需要对表格进行全表修改,需要申请表锁(X锁),这个时候,IS锁的优势来了,当查看表格是否有其他事务在访问操作时,一看表锁IS就知道有其他事务对表格内部某些数据持有S锁,并且还没有释放,那么这个时候,申请X锁就会处于等待状态,而不需要一行一行去查询每一行数据有没有被其他事务持有锁,可以大规模的减少查询 锁申请情况;

当一个表格持有IX表锁时,支持申请IS、IX表锁,但是不兼容S、X表锁。

  • 比如,事务A对表格 tba 中 id=10 (id为主键)进行进行 数据修改,这个时候,会对表格 tba 先申请一个 IX 表锁(申请成功),然后申请 id =10 的 X 行锁,申请成功,则 事务A 持有 IX 表锁、id=10的X 行锁,此时事务B 查询 id=20的行,申请表锁 IS 成功,申请 id=20的 S 行锁成功;事务C 修改 id=30的行数据,申请表锁 IX 成功,申请 id=30的行锁 X成功;但是,事务D中,对整个表格发起update或者全表SELECT操作,需要申请 X表锁或者S表锁,正常情况下,应该要对表格的每一行数据进行查看,确保每一行数据的行锁情况,但是因为有了意向锁,事务D一看到 tba 持有 了IX锁,则明白,tba 中某些行持有X锁,则会不兼容其他事务对tba 表锁S ,表锁X的申请。

为什么要引入意向表锁?

  • 在没有意向锁的时候,如果事务T 需要给表格 A 添加 一个S 表锁,那么就意味这这个表格内部的每一行数据,都不能有X锁,才能够申请 S 表锁成功,如果表格数据很多,一行行查找非常浪费加锁时间,这个时候,就出现了表格意向锁,当表格内部某些行发生 UPATE DELETE INSERT操作,则会对表格 加上 一个意向 IX 表锁,这样 事务T在申请 表格A的 S 表锁时,只需要检查 表格 A 是否有 IX表锁,如果有,则意味内部有 部分行数据持有X锁,则直接进入等待情况,如果表格没有 IX表锁,则直接申请S表锁成功,这是一个多么节约加锁时间的操作!

1.1 锁定兼容情况

  四个锁之间的兼容性,需要分成两种情况来讨论,锁粒度小于表级别的锁的兼容情况,表级的锁兼容情况。

  1. 锁粒度小于表级别的锁的兼容情况

澳门新浦京娱乐场网站 22

对于这两行锁的兼容说明如下:

  • 假设有一行数据,添加了行锁S锁,那么这个行数据,可以提供给其他事务进行S锁的申请和添加,但是不支持其他事务对这一行进行X锁的申请和添加。比如,事务A,对 pk100 这一行进行了 查询操作并添加了S锁,那么其他事务仍然可以对这一行数据进行查询,但是不能对这行数据进行 UPDATE 跟 DELETE 操作,会处于锁等待情况,直到该事务A结束并释放S锁;
  • 假设有一行数据,添加了X锁,那么这个行数据,不允许其他事务对这一行数据进行加锁。比如,事务A,对pk100这一行进行了UPDATE操作,那么其他事务在事务A没有结束之前,都无法对这一行数据申请 S锁。

表级的锁兼容情况

澳门新浦京娱乐场网站 23

对于表级别的锁兼容性如下:

当一个表格持有S表锁时,不需要其他事务对该表格申请X锁跟IX锁,但是允许申请S跟IS锁。比如,事务A对表格tba全表读,加了S表锁,期间支持其他事务对tba全表读(申请S表锁成功)、支持其他事务对tba行数据查询(申请IS表锁成功),但是不支持对表格全表的修改操作(申请X表锁等待)跟不支持对表格行数据修改操作(申请IX表锁等待);

当一个表格持有X表锁时,持有锁期间,不支持其他所有锁的申请;

当一个表格持有IS表锁时,允许申请 S表锁、IS表锁、IX表锁,但是不支持X表锁申请。

  • 比如,事务A对表格tba 查询了 id = 10(id为主键)这一行数据,这个时候,表格tba持有IS表锁,id = 10 这一行持有 S 行锁,期间,支持其他事务对 tba 全表查询(申请表锁S成功)或者 基于索引查找(申请表锁IS成功)
  • 如果需要对行 id = 20 进行数据修改,则会先申请 tba 的表锁 IX(申请成功),然后再申请id=20行锁X (申请成功);如果需要对 id = 10 这一行数据进行修改,则会申请 tba的表锁 IX(表锁申请成功),然后申请 id = 10 的行锁X(申请堵塞,因为 id = 10 正持有S锁);
  • 如果需要对表格进行全表修改,需要申请表锁(X锁),这个时候,IS锁的优势来了,当查看表格是否有其他事务在访问操作时,一看表锁IS就知道有其他事务对表格内部某些数据持有S锁,并且还没有释放,那么这个时候,申请X锁就会处于等待状态,而不需要一行一行去查询每一行数据有没有被其他事务持有锁,可以大规模的减少查询 锁申请情况;

当一个表格持有IX表锁时,支持申请IS、IX表锁,但是不兼容S、X表锁。

  • 比如,事务A对表格 tba 中 id=10 (id为主键)进行进行 数据修改,这个时候,会对表格 tba 先申请一个 IX 表锁(申请成功),然后申请 id =10 的 X 行锁,申请成功,则 事务A 持有 IX 表锁、id=10的X 行锁,此时事务B 查询 id=20的行,申请表锁 IS 成功,申请 id=20的 S 行锁成功;事务C 修改 id=30的行数据,申请表锁 IX 成功,申请 id=30的行锁 X成功;但是,事务D中,对整个表格发起update或者全表SELECT操作,需要申请 X表锁或者S表锁,正常情况下,应该要对表格的每一行数据进行查看,确保每一行数据的行锁情况,但是因为有了意向锁,事务D一看到 tba 持有 了IX锁,则明白,tba 中某些行持有X锁,则会不兼容其他事务对tba 表锁S ,表锁X的申请。

为什么要引入意向表锁?

  • 在没有意向锁的时候,如果事务T 需要给表格 A 添加 一个S 表锁,那么就意味这这个表格内部的每一行数据,都不能有X锁,才能够申请 S 表锁成功,如果表格数据很多,一行行查找非常浪费加锁时间,这个时候,就出现了表格意向锁,当表格内部某些行发生 UPATE DELETE INSERT操作,则会对表格 加上 一个意向 IX 表锁,这样 事务T在申请 表格A的 S 表锁时,只需要检查 表格 A 是否有 IX表锁,如果有,则意味内部有 部分行数据持有X锁,则直接进入等待情况,如果表格没有 IX表锁,则直接申请S表锁成功,这是一个多么节约加锁时间的操作!

为此,SQL Server在隔离和并发之间选择了Read Commited作为数据库的默认隔离级别。

1.2 锁的级别

Table Lock

  • 表锁,如果没有where条件、无可用索引或者获取的行记录过多,则会使用 table full scan,添加表锁

Record Lock

  • 记录锁,如果执行计划使用了索引,则会根据索引的查找情况添加行锁

Gap Lock

  • 在RR、RS隔离级别,发生在索引值之间,在连续的两个索引值之间添加锁,加锁后,这两个索引值之间,无法插入新的索引值,不包含行记录

Next-Key Lock

  • Record Lock 跟Gap Lock的组合,合体成为Next-KEY Lock

 

      表锁、行锁都相对好理解,这里尝试简单说明下 GAP LOCK。

      假设当前隔离级别为RR,表格 tbgap( id int auto_increment primary key not null , name varchar(50) , sort int , key ix_sort (sort)) engine=innodb; 

      表格数据如下:

      澳门新浦京娱乐场网站 24

      在索引ix_sort上,一共有7个间隙,分别为(-∞,(1,6)),((1,6),(2,5)),((2,5),(3,2)),((3,2),(5,4)),((5,4),(6,1)),((6,1),(7,3)),((7,3), ∞),而根据实际的隔离级别及锁申请情况,加在这些间隙上的锁,则成为 GAP LOCK 。   

1.2 锁的级别

Table Lock

  • 表锁,如果没有where条件、无可用索引或者获取的行记录过多,则会使用 table full scan,添加表锁

Record Lock

  • 记录锁,如果执行计划使用了索引,则会根据索引的查找情况添加行锁

Gap Lock

  • 在RR、RS隔离级别,发生在索引值之间,在连续的两个索引值之间添加锁,加锁后,这两个索引值之间,无法插入新的索引值,不包含行记录

Next-Key Lock

  • Record Lock 跟Gap Lock的组合,合体成为Next-KEY Lock

 

      表锁、行锁都相对好理解,这里尝试简单说明下 GAP LOCK。

      假设当前隔离级别为RR,表格 tbgap( id int auto_increment primary key not null , name varchar(50) , sort int , key ix_sort (sort)) engine=innodb; 

      表格数据如下:

      澳门新浦京娱乐场网站 25

      在索引ix_sort上,一共有7个间隙,分别为(-∞,(1,6)),((1,6),(2,5)),((2,5),(3,2)),((3,2),(5,4)),((5,4),(6,1)),((6,1),(7,3)),((7,3), ∞),而根据实际的隔离级别及锁申请情况,加在这些间隙上的锁,则成为 GAP LOCK 。   

多个用户同时对数据库的并发操作时会带来以下数据不一致的问题: 

1.3 锁与隔离级别(不考虑 lock in shar mode跟for update )

  • RU,读未提交记录,不加锁读,正常写锁;
  • RC,快照读,无锁;当前读,加 Record Lock
  • RR,快照读,无锁;当前读,对读取到的记录加 Record Lock,同时为了确保where条件范围内的数据无变化,会增加Next key lock
  • RS,读写均为当前读,不支持快照读。包括select 在内,对读取到的记录加 Record Lock,同时为了确保where条件范围内的数据无变化,会增加Next key lock。

1.3 锁与隔离级别(不考虑 lock in shar mode跟for update )

  • RU,读未提交记录,不加锁读,正常写锁;
  • RC,快照读,无锁;当前读,加 Record Lock
  • RR,快照读,无锁;当前读,对读取到的记录加 Record Lock,同时为了确保where条件范围内的数据无变化,会增加Next key lock
  • RS,读写均为当前读,不支持快照读。包括select 在内,对读取到的记录加 Record Lock,同时为了确保where条件范围内的数据无变化,会增加Next key lock。

脏读:一个事务读取到了另外一个事务没有提交的数据。

2 锁的申请与释放过程

      看SQL语句的锁情况,需要结合隔离级别、执行计划、表结构等,同一个SQL,不同的隔离级别、表结构、执行计划,其锁情况不一定是一样的!

      本次模拟这3个表格,age列分别:无索引、有一般索引、有唯一索引。表结构结束及数据如下:

 

CREATE TABLE tb_no_index ( id int primary key not null auto_increment, age int not null, name varchar(100) );

CREATE TABLE tb_index ( id int primary key not null auto_increment, age int not null, name varchar(100) KEY ix_age(age) );

CREATE TABLE tb_unique_index ( id int primary key not null auto_increment, age int not null,name varchar(100) UNIQUE KEY ix_age(age) );

 

INSERT INTO tb_no_index(age) values(2),(9),(21),(4),(7),(25);

INSERT INTO tb_index(age) values(2),(9),(21),(4),(7),(25);

INSERT INTO tb_unique_index(age) values(2),(9),(21),(4),(7),(25);

  

每个表格IX_age的索引行数就据如下图展示:

age
2
4
7
9
21
25
id
1
4
5
2
3
6

 

每个表格主键上面的行数就据如下图展示:

id
1
2
3
4
5
6
age
2
9
21
4
7
25
name
null
null
null
null
null
null

 

2 锁的申请与释放过程

      看SQL语句的锁情况,需要结合隔离级别、执行计划、表结构等,同一个SQL,不同的隔离级别、表结构、执行计划,其锁情况不一定是一样的!

      本次模拟这3个表格,age列分别:无索引、有一般索引、有唯一索引。表结构结束及数据如下:

 

CREATE TABLE tb_no_index ( id int primary key not null auto_increment, age int not null, name varchar(100) );

CREATE TABLE tb_index ( id int primary key not null auto_increment, age int not null, name varchar(100) KEY ix_age(age) );

CREATE TABLE tb_unique_index ( id int primary key not null auto_increment, age int not null,name varchar(100) UNIQUE KEY ix_age(age) );

 

INSERT INTO tb_no_index(age) values(2),(9),(21),(4),(7),(25);

INSERT INTO tb_index(age) values(2),(9),(21),(4),(7),(25);

INSERT INTO tb_unique_index(age) values(2),(9),(21),(4),(7),(25);

  

每个表格IX_age的索引行数就据如下图展示:

age
2
4
7
9
21
25
id
1
4
5
2
3
6

 

每个表格主键上面的行数就据如下图展示:

id
1
2
3
4
5
6
age
2
9
21
4
7
25
name
null
null
null
null
null
null

 

A修改了数据,随后B又读出该数据,但A因为某些原因取消了对数据的修改,数据恢复原值,此时B得到的数据就与数据库内的数据产生了不一致 

2.1 Read Uncommitted

     所有事务隔离级别设置: set session transaction isolation level read Uncommitted ;

     RU是读未提交,不添加 LOCK IN SHARE MODE 跟 FOR UPDATE 的 SELECT 语句,均为读未提交,不加锁,存在脏读、不可重复读及幻读。

     所有UPDATE、DELETE、INSERT获取当前读记录,加锁。

 表格     SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

读不加锁,读未提交数据

可能有脏读、不可重复读及幻读

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

澳门新浦京娱乐场网站 26

 

 

 

表格的age列无索引,所以update过程中,全表加X锁

支持semi-constent-read,如果有其他update语句修改其他行不堵塞,但是不支持 select ... for update

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

 

澳门新浦京娱乐场网站 27

 

 

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

 

澳门新浦京娱乐场网站 28

 

tb_unique_index

同上

同上

      

2.1 Read Uncommitted

     所有事务隔离级别设置: set session transaction isolation level read Uncommitted ;

     RU是读未提交,不添加 LOCK IN SHARE MODE 跟 FOR UPDATE 的 SELECT 语句,均为读未提交,不加锁,存在脏读、不可重复读及幻读。

     所有UPDATE、DELETE、INSERT获取当前读记录,加锁。

 表格     SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

读不加锁,读未提交数据

可能有脏读、不可重复读及幻读

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

澳门新浦京娱乐场网站 29

 

 

 

表格的age列无索引,所以update过程中,全表加X锁

支持semi-constent-read,如果有其他update语句修改其他行不堵塞,但是不支持 select ... for update

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

 

澳门新浦京娱乐场网站 30

 

 

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

 

澳门新浦京娱乐场网站 31

 

tb_unique_index

同上

同上

      

幻读:同一事务中,用同样的操作读取两次,得到的记录数不相同。

2.2 Read Committed

所有事务隔离级别设置: set session transaction isolation level read committed ;

     

     RC是读已提交,不添加 LOCK IN SHARE MODE 跟 FOR UPDATE 的 SELECT 语句,均为 快照读,不加锁,同个事务内读取同一个版本的数据,可能非最新数据,但是不存在脏读、不可重复读及幻读情况。

     所有UPDATE、DELETE、INSERT获取当前读记录,加锁。

 

     下表中,黄绿色 字体 是RC与RU隔离级别不同的地方,仔细阅读分析结果可以知道,在 RU 跟 RC 间,最大的区别在于 SELECT 的查询模式,RU 为 读未提交,而 RC 为快照读。UPATE/DELETE/INSERT的加锁模式类同。

  表格

             SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

快照读,不加锁

读取的数据不一定是最新版本,但是事务内的所有查询读取数据都是同一版本的行数据,不存在脏读、不可重复读及幻读的情况

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

澳门新浦京娱乐场网站 32

 

 

表格的age列无索引,所以update过程中,全表加X锁

支持semi-constent-read,如果有其他update语句修改其他行不堵塞,但是不支持 select ... for update

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

 澳门新浦京娱乐场网站 33

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

 澳门新浦京娱乐场网站 34

 

tb_unique_index

同上

同上

 

 

2.2 Read Committed

所有事务隔离级别设置: set session transaction isolation level read committed ;

     

     RC是读已提交,不添加 LOCK IN SHARE MODE 跟 FOR UPDATE 的 SELECT 语句,均为 快照读,不加锁,同个事务内读取同一个版本的数据,可能非最新数据,但是不存在脏读、不可重复读及幻读情况。

     所有UPDATE、DELETE、INSERT获取当前读记录,加锁。

 

     下表中,黄绿色 字体 是RC与RU隔离级别不同的地方,仔细阅读分析结果可以知道,在 RU 跟 RC 间,最大的区别在于 SELECT 的查询模式,RU 为 读未提交,而 RC 为快照读。UPATE/DELETE/INSERT的加锁模式类同。

  表格

             SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

快照读,不加锁

读取的数据不一定是最新版本,但是事务内的所有查询读取数据都是同一版本的行数据,不存在脏读、不可重复读及幻读的情况

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

澳门新浦京娱乐场网站 35

 

 

表格的age列无索引,所以update过程中,全表加X锁

支持semi-constent-read,如果有其他update语句修改其他行不堵塞,但是不支持 select ... for update

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

 澳门新浦京娱乐场网站 36

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

 澳门新浦京娱乐场网站 37

 

tb_unique_index

同上

同上

 

 

A读取数据,随后B又插入了数据,此时A再读数据是发现前后两次获取的数据行集不一致 

2.3 Read Repeatable

所有事务隔离级别设置: set session transaction isolation level repeatable read ;

 

      RR隔离级别中,SELECT操作支持快照读,所有的UPDATE/DELETE/INSERT加锁,锁类型会新增一个GAP LOCK。

   表格     SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

快照读,不加锁

读取的数据不一定是最新版本,但是事务内的所有查询读取数据都是同一版本的行数据,不存在脏读、不可重复读及幻读的情况

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

澳门新浦京娱乐场网站 38

 

 

表格的age列无索引,所以update过程中

全表加X锁,期间全表堵塞UPDATEDELETEINSERT

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

 

你以为结束了!并没有,这里有趣了!

还会添加两个gap lock ((9,2) ,(21,3)),((21,3), (21,25))

 

这里我们单独拎出小表格来分析。

澳门新浦京娱乐场网站 39

 

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为 

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

 

澳门新浦京娱乐场网站 40

 

tb_unique_index

以为跟上面的加锁范围一样,no no no

唯一索引列上 每一个age都是唯一的,也就是age=21只有一个,不会再INSERT一个新的 age =21进来,故在这里不需要加gap lock,加锁情况如下:

tb_index 加 表格意向锁 IX

age=21 行添加行锁 X 

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为 

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

但是,范围查询添加到gap lock在其他情况下跟非唯一索引会有一些差别,可以看下表的例子。

 

这里做亮点补充说明:

 

2.3 Read Repeatable

所有事务隔离级别设置: set session transaction isolation level repeatable read ;

 

      RR隔离级别中,SELECT操作支持快照读,所有的UPDATE/DELETE/INSERT加锁,锁类型会新增一个GAP LOCK。

   表格     SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

快照读,不加锁

读取的数据不一定是最新版本,但是事务内的所有查询读取数据都是同一版本的行数据,不存在脏读、不可重复读及幻读的情况

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

澳门新浦京娱乐场网站 41

 

 

表格的age列无索引,所以update过程中

全表加X锁,期间全表堵塞UPDATEDELETEINSERT

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

 

你以为结束了!并没有,这里有趣了!

还会添加两个gap lock ((9,2) ,(21,3)),((21,3), (21,25))

 

这里我们单独拎出小表格来分析。

澳门新浦京娱乐场网站 42

 

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为 

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

 

澳门新浦京娱乐场网站 43

 

tb_unique_index

以为跟上面的加锁范围一样,no no no

唯一索引列上 每一个age都是唯一的,也就是age=21只有一个,不会再INSERT一个新的 age =21进来,故在这里不需要加gap lock,加锁情况如下:

tb_index 加 表格意向锁 IX

age=21 行添加行锁 X 

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为 

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

但是,范围查询添加到gap lock在其他情况下跟非唯一索引会有一些差别,可以看下表的例子。

 

这里做亮点补充说明:

 

不可重复读:在同一事务中,两次读取同一数据,得到内容不同。

2.3.1 RR下的非唯一索引加锁情况

      update tbname set name=...  where age = 21

      澳门新浦京娱乐场网站 44 

      还记得上篇文章说过 RR隔离级别可以防止 幻读吗?因为在RR隔离级别中,加多了next-key = record lock gap lock,gap lock是加在索引值之间的锁。也就是 当修改 age=21 的行数据时,除了 在 age=21 这一行添加 X record lock , 还在 ((9,2) ,(21,3)),((21,3), (21,25))这两个age值得范围内添加 gap lock。加锁的情况是:tb_index添加 IX意向锁,age索引上添加age=21的 x record lock,再在主键上的行记录 id=5 添加 X record lock,同时在 age 值上添加两个 gap lock,分别为((9,2) ,(21,3)),((21,3), (21,25))。

     注意这里有个误区,很多小伙伴会认为,那么这么加gap锁,则意味着,当update age=21这一列时, 9<age<25 ,这个范围内,是不允许进行 UPATE/DELETE/INSERT的。这种推测实际上是不完整的,因为它没考虑到跟主键!!!

 

     注意,每次写gap lock的时候,都是有加上主键值的。比如这里,当更新 age=21这列时,加了 ((9,2) ,(21,3)),((21,3), (21,25)) 这两个范围的 GAP LOCK,那么在当前update age=21的事务还没有结束的情况下,假设有两条修改SQL的语句:

update tbname set age=9 where id = 1;

update tbname set age=9 where age = 4;

 

    这两条SQL,是能够正常执行,还是堵塞呢?

    innodb中,索引按照二叉树排列顺序,而这两条SQL修改后在IX_AGE上的索引值分别为:(9,1)、(9,4),可以发现(9,1)在键值(9,2)的左边,不在GAP LOCK的范围内,所以,可以正常执行;而(9,4)在键值的右边,刚好在GAP LOCK的范围内,会被堵塞!总结:第一条UPDATE SQL,正常秩序;第二条UPDATE SQL会被堵塞。

 

    所以,考虑GAP LOCK的时候,一定要注意结合整个索引键值来分析,而索引键值=索引值 主键。

2.3.1 RR下的非唯一索引加锁情况

      update tbname set name=...  where age = 21

      澳门新浦京娱乐场网站 45 

      还记得上篇文章说过 RR隔离级别可以防止 幻读吗?因为在RR隔离级别中,加多了next-key = record lock gap lock,gap lock是加在索引值之间的锁。也就是 当修改 age=21 的行数据时,除了 在 age=21 这一行添加 X record lock , 还在 ((9,2) ,(21,3)),((21,3), (21,25))这两个age值得范围内添加 gap lock。加锁的情况是:tb_index添加 IX意向锁,age索引上添加age=21的 x record lock,再在主键上的行记录 id=5 添加 X record lock,同时在 age 值上添加两个 gap lock,分别为((9,2) ,(21,3)),((21,3), (21,25))。

     注意这里有个误区,很多小伙伴会认为,那么这么加gap锁,则意味着,当update age=21这一列时, 9<age<25 ,这个范围内,是不允许进行 UPATE/DELETE/INSERT的。这种推测实际上是不完整的,因为它没考虑到跟主键!!!

 

     注意,每次写gap lock的时候,都是有加上主键值的。比如这里,当更新 age=21这列时,加了 ((9,2) ,(21,3)),((21,3), (21,25)) 这两个范围的 GAP LOCK,那么在当前update age=21的事务还没有结束的情况下,假设有两条修改SQL的语句:

update tbname set age=9 where id = 1;

update tbname set age=9 where age = 4;

 

    这两条SQL,是能够正常执行,还是堵塞呢?

    innodb中,索引按照二叉树排列顺序,而这两条SQL修改后在IX_AGE上的索引值分别为:(9,1)、(9,4),可以发现(9,1)在键值(9,2)的左边,不在GAP LOCK的范围内,所以,可以正常执行;而(9,4)在键值的右边,刚好在GAP LOCK的范围内,会被堵塞!总结:第一条UPDATE SQL,正常秩序;第二条UPDATE SQL会被堵塞。

 

    所以,考虑GAP LOCK的时候,一定要注意结合整个索引键值来分析,而索引键值=索引值 主键。

A用户读取数据,随后B用户读出该数据并修改,此时A用户再读取数据时发现前后两次的值不一致

2.3.2 RR下的唯一索引加锁情况

      update tbname set name=...  where age between .. and ... 

 

      因为唯一索引上面的索引键值都是唯一的,故不会出现重复值的插入的情况,下表罗列了同样的 范围查询修改语句,在唯一索引及非唯一索引上加 GAP_LOCK的情况。

      表格数据如下:

 

      澳门新浦京娱乐场网站 46

 

      加GAP_LOCK的情况如下(注意注意,方便查看,省略了主键值,实际上是需要添加上主键键值的):      

 
update tbname set name=...
where age between 1 and 7
update tbname set name=...
where age between 2 and 7
update tbname set name=...
where age between 5 and 10
update tbname set name=...
where age between 15 and 50
tb_index
(-∞,2),(2,4),(4,7),(7,9)
(-∞,2),(2,4),(4,7),(7,9)
(4,7),(7,9),(9,21)
(9,21),(21,25),(25, ∞)
tb_unique_index
(-∞,2),(2,4),(4,7)
(2,4),(4,7)
(4,7),(7,9),(9,21)
(9,21),(21,25),(25, ∞)

 

2.3.2 RR下的唯一索引加锁情况

      update tbname set name=...  where age between .. and ... 

 

      因为唯一索引上面的索引键值都是唯一的,故不会出现重复值的插入的情况,下表罗列了同样的 范围查询修改语句,在唯一索引及非唯一索引上加 GAP_LOCK的情况。

      表格数据如下:

 

      澳门新浦京娱乐场网站 47

 

      加GAP_LOCK的情况如下(注意注意,方便查看,省略了主键值,实际上是需要添加上主键键值的):      

 
update tbname set name=...
where age between 1 and 7
update tbname set name=...
where age between 2 and 7
update tbname set name=...
where age between 5 and 10
update tbname set name=...
where age between 15 and 50
tb_index
(-∞,2),(2,4),(4,7),(7,9)
(-∞,2),(2,4),(4,7),(7,9)
(4,7),(7,9),(9,21)
(9,21),(21,25),(25, ∞)
tb_unique_index
(-∞,2),(2,4),(4,7)
(2,4),(4,7)
(4,7),(7,9),(9,21)
(9,21),(21,25),(25, ∞)

 

     丢失更新:事务T1读取了数据,并执行了一些操作,然后更新数据。事务T2也做相同的事,则T1和T2更新数据时可能会覆盖对方的更新,从而引起错误。

2.4 Read Serializable

所有事务隔离级别设置: set session transaction isolation level Serializable   ;

   表格     SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

不支持快照读,所有SELECT都是当前读,所有SELECT操作都需要加S锁,除主键定值查找唯一索引定值查找外,其他基于索引或者主键的范围查找都会添加 S GAP LOCK。并发度是四个隔离级别中性能最差的。

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

表格的age列无索引,所以update过程中

全表加X锁,期间全表堵塞UPDATEDELETEINSERT

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

在age索引上 添加两个gap lock ((9,2) ,(21,3)),((21,3), (21,25))

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

tb_unique_index

唯一索引列上 每一个age都是唯一的,也就是age=21只有一个,不会再INSERT一个新的 age =21进来,故在这里不需要加gap lock,加锁情况如下:

tb_index 加 表格意向锁 IX

age=21 行添加行锁 X 

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为 

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

但是,范围查询添加到gap lock在其他情况下跟非唯一索引会有一些差别,可以看下表的例子。

 

 

    这里详细的来分析下 RS 隔离级别下的SELECT操作加的锁:

      澳门新浦京娱乐场网站 48

 

表格     SQL

select * from tbname

where id=5

select * from tbname

where id betwee 5 and 15

select * from tbname

where age=21

select * from tbname

where age betwee 5 and 9

tb_no_index

主键定值查找

表格tbname 添加 IS 意向锁

id=5 添加 S锁

主键范围查找

表格tbname 添加 IS 意向锁

id=5,id=6 两行数据 添加 S锁

同时添加2个 S GAP LOCK ,分别为 ((5,7),(6,25))跟((6,25), ∞)

全表查找

表格 tbname 添加 IS 意向锁

由于全表查找,整个表格 再次添加 S 表锁

全表查找

表格 tbname 添加 IS 意向锁

由于全表查找,整个表格 再次添加S 表锁

tb_index

ix_age索引查找

表格tbname 添加 IS 意向锁

索引上 age = 21 添加 S 行锁

主键上 id=3 添加 S 行锁

同时添加 2个 S GAP LOCK ,分别为 ((9,2) ,(21,3)),((21,3), (21,25))

ix_age索引查找

表格tbname 添加 IS 意向锁

age索引上面,age=7,age=9 行添加行锁 S

再在主键上,给id=2,id=5 这一行数据,添加行数 S

同时会在索引 age的值上添加 3个 S gap lock,分别为

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

tb_unique_index

ix_age索引查找

表格tbname 添加 IS 意向锁

索引上 age = 21 添加 S 行锁

主键上 id=3 添加 S 行锁

由于age列唯一,故不需要添加GAP LOCK

ix_age索引查找

表格tbname 添加 IS 意向锁

age索引上面,age=7,age=9 行添加行锁 S

再在主键上,给id=2,id=5 这一行数据,添加行数 S

同时会在索引 age的值上添加 2 个 S gap lock,分别为

((4,4),(7,5))、((7,5),(9,2))

 

   至此,已说明了四个隔离级别是如何加锁,那么,释放锁呢?

 

   在MySQL INNODB中,遵循的是 strong strict 2-PL,也就是所有的write lock 跟read lock 都是在 事务 commit后才释放。

2.4 Read Serializable

所有事务隔离级别设置: set session transaction isolation level Serializable   ;

   表格     SQL

select * from tbname

where age/id ...

update tbname set name=...

where id = 4

update tbname set name=...

where age = 21

update tbname set name=...

where age between 5 and 15

tb_no_index

不支持快照读,所有SELECT都是当前读,所有SELECT操作都需要加S锁,除主键定值查找唯一索引定值查找外,其他基于索引或者主键的范围查找都会添加 S GAP LOCK。并发度是四个隔离级别中性能最差的。

当前读,根据主键修改数据

tbname 加意向表锁 IX

id=4 加 行锁 X

表格的age列无索引,所以update过程中

全表加X锁,期间全表堵塞UPDATEDELETEINSERT

同左

tb_index

表格的age列有索引,update过程中

tb_index 加 表格意向锁 IX

age索引上面,age=21 行添加行锁 X

再在主键上,给id=3 这一行数据,添加行数 X

在age索引上 添加两个gap lock ((9,2) ,(21,3)),((21,3), (21,25))

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

澳门新浦京娱乐场网站,同时会在索引 age的值上添加 3个 gap lock,分别为

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

tb_unique_index

唯一索引列上 每一个age都是唯一的,也就是age=21只有一个,不会再INSERT一个新的 age =21进来,故在这里不需要加gap lock,加锁情况如下:

tb_index 加 表格意向锁 IX

age=21 行添加行锁 X 

表格的age列有索引,update过程涉及age=7,9 两行数据

tb_index 加表格意向锁 IX

age索引上面,age=7,age=9 行添加行锁 X

再在主键上,给id=2,id=5 这一行数据,添加行数 X

同时会在索引 age的值上添加 3个 gap lock,分别为 

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

但是,范围查询添加到gap lock在其他情况下跟非唯一索引会有一些差别,可以看下表的例子。

 

 

    这里详细的来分析下 RS 隔离级别下的SELECT操作加的锁:

      澳门新浦京娱乐场网站 49

 

表格     SQL

select * from tbname

where id=5

select * from tbname

where id betwee 5 and 15

select * from tbname

where age=21

select * from tbname

where age betwee 5 and 9

tb_no_index

主键定值查找

表格tbname 添加 IS 意向锁

id=5 添加 S锁

主键范围查找

表格tbname 添加 IS 意向锁

id=5,id=6 两行数据 添加 S锁

同时添加2个 S GAP LOCK ,分别为 ((5,7),(6,25))跟((6,25), ∞)

全表查找

表格 tbname 添加 IS 意向锁

由于全表查找,整个表格 再次添加 S 表锁

全表查找

表格 tbname 添加 IS 意向锁

由于全表查找,整个表格 再次添加S 表锁

tb_index

ix_age索引查找

表格tbname 添加 IS 意向锁

索引上 age = 21 添加 S 行锁

主键上 id=3 添加 S 行锁

同时添加 2个 S GAP LOCK ,分别为 ((9,2) ,(21,3)),((21,3), (21,25))

ix_age索引查找

表格tbname 添加 IS 意向锁

age索引上面,age=7,age=9 行添加行锁 S

再在主键上,给id=2,id=5 这一行数据,添加行数 S

同时会在索引 age的值上添加 3个 S gap lock,分别为

((4,4),(7,5))、((7,5),(9,2))、((9,2),(21,3))

tb_unique_index

ix_age索引查找

表格tbname 添加 IS 意向锁

索引上 age = 21 添加 S 行锁

主键上 id=3 添加 S 行锁

由于age列唯一,故不需要添加GAP LOCK

ix_age索引查找

表格tbname 添加 IS 意向锁

age索引上面,age=7,age=9 行添加行锁 S

再在主键上,给id=2,id=5 这一行数据,添加行数 S

同时会在索引 age的值上添加 2 个 S gap lock,分别为

((4,4),(7,5))、((7,5),(9,2))

 

   至此,已说明了四个隔离级别是如何加锁,那么,释放锁呢?

 

   在MySQL INNODB中,遵循的是 strong strict 2-PL,也就是所有的write lock 跟read lock 都是在 事务 commit后才释放。

A,B两个用户读同一数据并进行修改,其中一个用户的修改结果破坏了另一个修改的结果,比如订票系统 

3 SQL分析

     考虑到下文的例子,这里补充两个概念。

     ICP:

     MRR:

      

     假设表格 tb_lock ( id int primary key not null, age int,score int,name varchar(10), key ix_age_score ( age, score ) ) 数据修改如下 :

      澳门新浦京娱乐场网站 50

 

      假设MySQL当前的隔离级别为 RR,执行 UPDATE tb_index WHERE age between 5 and 22 and score between 1 and 10 and name is not null,其执行计划如下:

      澳门新浦京娱乐场网站 51

      那么,是如何加锁的呢?

      首先,可以看到是根据索引 ix_age_score 查找,那么分为两种情况来分析,第一种,数据库支持ICP;第二种,数据不支持ICP。

3 SQL分析

     考虑到下文的例子,这里补充两个概念。

     ICP:

     MRR:

      

     假设表格 tb_lock ( id int primary key not null, age int,score int,name varchar(10), key ix_age_score ( age, score ) ) 数据修改如下 :

      澳门新浦京娱乐场网站 52

 

      假设MySQL当前的隔离级别为 RR,执行 UPDATE tb_index WHERE age between 5 and 22 and score between 1 and 10 and name is not null,其执行计划如下:

      澳门新浦京娱乐场网站 53

      那么,是如何加锁的呢?

      首先,可以看到是根据索引 ix_age_score 查找,那么分为两种情况来分析,第一种,数据库支持ICP;第二种,数据不支持ICP。

并发控制的主要方法是通过锁,在一段时间内禁止用户做某些操作以避免产生数据不一致

3.1 支持ICP情况

      当数据库支持ICP的时候,根据复合索引第一列 age 查找 age between 5 and 22,然后在索引内部过滤 score between 1 and 10后,取得索引值后,如果数据库支持 MRR,则会把取得的索引值放到buffer中,对主键进行排序,然后可以根据顺序的主键值去 主键中查找行数据,如果不支持,则跳过这一步排序步骤,直接根据索引值内部的主键值,查找主键行数,最后过滤 name is not null 。

    澳门新浦京娱乐场网站 54

      加锁过程如下,tb_lock添加 IX 意向锁,在索引  ix_age_score 给索引值(7,10,5),(21,4,3)添加上 X record lock,并添加4个 X GAP LOCK,如图片红色素箭头展示,分别为((4,7,4), (7,10,5)),((7,10,5), (9,15,2)),((9,15,2),(21,4,3)),((21,4,3),(25,1,6)),最后在主键上给id=3及id=5 两行数就添加X record lock。

3.1 支持ICP情况

      当数据库支持ICP的时候,根据复合索引第一列 age 查找 age between 5 and 22,然后在索引内部过滤 score between 1 and 10后,取得索引值后,如果数据库支持 MRR,则会把取得的索引值放到buffer中,对主键进行排序,然后可以根据顺序的主键值去 主键中查找行数据,如果不支持,则跳过这一步排序步骤,直接根据索引值内部的主键值,查找主键行数,最后过滤 name is not null 。

    澳门新浦京娱乐场网站 55

      加锁过程如下,tb_lock添加 IX 意向锁,在索引  ix_age_score 给索引值(7,10,5),(21,4,3)添加上 X record lock,并添加4个 X GAP LOCK,如图片红色素箭头展示,分别为((4,7,4), (7,10,5)),((7,10,5), (9,15,2)),((9,15,2),(21,4,3)),((21,4,3),(25,1,6)),最后在主键上给id=3及id=5 两行数就添加X record lock。

 

3.2 不支持ICP情况

      当数据库不支持ICP的时候,根据复合索引第一列 age 选择 age between 5 and 22,然后根据筛选的索引值 (7,10,5),(9,15,2),(21,4,3)中的主键 5、2、3,找到对应的行数据,再在行数据中 过滤 score between 1 and 10 and name is not null。

     澳门新浦京娱乐场网站 56

      加锁过程如下,tb_lock添加 IX 意向锁,在索引  ix_age_score 给索引值(7,10,5),(9,15,2),(21,4,3)添加上 X record lock,并添加4个 X GAP LOCK,如图片红色素箭头展示,分别为((4,7,4), (7,10,5)),((7,10,5), (9,15,2)),((9,15,2),(21,4,3)),((21,4,3),(25,1,6)),最后在主键上给id=2、id=3、id=5 两行数就添加X record lock。

 

参考文档:

 

 

3.2 不支持ICP情况

      当数据库不支持ICP的时候,根据复合索引第一列 age 选择 age between 5 and 22,然后根据筛选的索引值 (7,10,5),(9,15,2),(21,4,3)中的主键 5、2、3,找到对应的行数据,再在行数据中 过滤 score between 1 and 10 and name is not null。

     澳门新浦京娱乐场网站 57

      加锁过程如下,tb_lock添加 IX 意向锁,在索引  ix_age_score 给索引值(7,10,5),(9,15,2),(21,4,3)添加上 X record lock,并添加4个 X GAP LOCK,如图片红色素箭头展示,分别为((4,7,4), (7,10,5)),((7,10,5), (9,15,2)),((9,15,2),(21,4,3)),((21,4,3),(25,1,6)),最后在主键上给id=2、id=3、id=5 两行数就添加X record lock。

 

参考文档:

 

 

理解SQL SERVER中的隔离级别

 

为了避免上述几种事务之间的影响,SQL Server通过设置不同的隔离等级来进行不同程度的避免。 SQL Server提供了5种选项来避免不同级别的事务之间的影响。隔离等级由低到高分别为:

  • 未提交读(Read Uncommited):最高的性能,但可能出现脏读,不可重复读,幻读

  • 已提交读(Read commited):可能出现不可重复读,幻读

  • 可重复读(Repeatable Read):可能出现幻读
  • 序列化(Serializable):最低的性能,Range锁会导致并发下降

  • 快照(SNOPSHOT):这个是通过在tempDB中创建一个额外的副本来避免脏读,不可重复读,会给tempDB造成额外负担

 

锁的模式

  • 共享锁(S锁):用于读取资源所加的锁。拥有共享锁的资源不能被修改。共享锁默认情况下是读取了资源马上被释放。

  • 排他锁(X锁): 和其它任何锁都不兼容,包括其它排他锁。排它锁用于数据修改,当资源上加了排他锁时,其他请求读取或修改这个资源的事务都会被阻塞,知道排他锁被释放为止。

  • 更新锁(U锁): U锁可以看作是S锁和X锁的结合,用于更新数据,更新数据时首先需要找到被更新的数据,此时可以理解为被查找的数据上了S锁。当找到需要修改的数据时,需要对被修改的资源上X锁。SQL Server通过U锁来避免死锁问题。因为S锁和S锁是兼容的,通过U锁和S锁兼容,来使得更新查找时并不影响数据查找,而U锁和U锁之间并不兼容,从而减少了死锁可能性。

  • 意向锁(IS IX IU):意向锁与其说是锁,倒不如说更像一个指示器。在SQL Server中,资源是有层次的,一个表中可以包含N个页,而一个页中可以包含N个行。当我们在某一个行中加了锁时。可以理解成包含这个行的页,和表的一部分已经被锁定。当另一个查询需要锁定页或是表时,再一行行去看这个页和表中所包含的数据是否被锁定就有点太痛苦了。因此SQL Server锁定一个粒度比较低的资源时,会在其父资源上加上意向锁,告诉其他查询这个资源的某一部分已经上锁。比如,当我们更新一个表中的某一行时,其所在的页和表都会获得意向排他锁

  • 快照(SNOPSHOT):这个是通过在tempDB中创建一个额外的副本来避免脏读,不可重复读,会给tempDB造成额外负担

  • 键范围锁(KEY-RANGE):在使用可序列化事务隔离级别时,对于 Transact-SQL 语句读取的记录集,键范围锁可以隐式保护该记录集中包含的行范围。可序列化隔离级别要求每当在事务期间执行任一查询时,该查询都必须获取相同的行集。键范围锁可防止其他事务插入其键值位于可序列化事务读取的键值范围内的新行,从而确保满足此要求。

键范围锁可防止幻读。通过保护行之间的键范围,它还可以防止对事务访问的记录集进行幻插入。

键范围锁放置在索引上,指定开始键值和结束键值。此锁将阻止任何要插入、更新或删除任何带有该范围内的键值的行的尝试,因为这些操作会首 先获取索引上的锁。例如,可序列化事务可能发出了一个 SELECT 语句,以读取其键值介于 'AAA' 与 'CZZ' 之间的所有行。从 'AAA' 到 'CZZ' 范围内的键值上的键范围锁可阻止其他事务插入带有该范围内的键值(例如 'ADG'、'BBD' 或 'CAL')的行。

  • 架构锁: SQL Server 使用架构锁来保持表结构的完整性。不像其他提供数据隔离的锁类型,架构锁提供事务中对数据库对象如表、视图、索引的schema隔离。

  • 大容量更新锁:在向表进行大容量数据复制且指定了 TABLOCK 提示时使用

 

锁兼容性

澳门新浦京娱乐场网站 58

 

锁的粒度

所谓所粒度,从本质上说就是,为了给事务提供完全的隔离和序列化,作为查询或更新的一部分被锁定的数据的总量(的大小)。Lock Manager需要在资源的并发访问与维护大量低级别锁的管理开销之间取得平衡。比如,锁的粒度越小,能够同时访问同一张表的并发用户的数量就越大,不过维护这些锁的管理开销也越大。锁的粒度越大,管理锁需要的开销就越少,而并发性也降低了。下图说明了锁的大小与并发性之间的权衡取舍。

 

澳门新浦京娱乐场网站 59

SQL Server支持的锁粒度可以分为为行、页、键、键范围、索引、表或数据库获取锁

 

锁升级

锁升级是将许多较细粒度的锁转换成数量更少的较粗粒度的锁的过程,这样可以减少系统开销,但却增加了并发争用的可能性。

当 SQL Server 数据库引擎获取低级别的锁时,它还将在包含更低级别对象的对象上放置意向锁:

  1. 当锁定行或索引键范围时,数据库引擎将在包含这些行或键的页上放置意向锁。

  2. 当锁定页时,数据库引擎将在包含这些页的更高级别的对象上放置意向锁。

除了对象上的意向锁以外,以下对象上还需要意向页锁:非聚集索引的叶级页、聚集索引的数据页、堆数据页。

 

锁升级的阈值:

  • 单个 Transact-SQL 语句在单个无分区表或索引上获得至少 5,000 个锁。
  • 单个 Transact-SQL 语句在已分区表的单个分区上获得至少 5,000 个锁,并且 ALTER TABLE SET LOCK_ESCALATION 选项设为 AUTO。
  • 数据库引擎实例中的锁的数量超出了内存或配置阈值

TIPS:数据库引擎不会将行锁或键范围锁升级到页锁,而是将它们直接升级到表锁。同样,页锁始终升级到表锁。

 

如何查看锁

1、使用sys.dm_tran_locks这个DMV

澳门新浦京娱乐场网站 60

2、使用Profiler来捕捉锁信息

澳门新浦京娱乐场网站 61

 

二、死锁成因分析

什么是死锁

死锁的本质是一种僵持状态,是多个主体对于资源的争用而导致的。在两个或多个任务中,如果每个任务锁定了其他任务试图锁定的资源,此时会造成这些任务永久阻塞,从而出现死锁。理解死锁首先需要对死锁所涉及的相关观念有一个理解。

澳门新浦京娱乐场网站 62

在上图的例子中,每队汽车都占有一条道路,但都需要另外一队汽车所占有的另一条道路,因此互相阻塞,谁都无法前行,因此造成了死锁。

 

死锁产生的原因及四个必要条件

产生死锁的原因主要是:

(1) 因为系统资源不足。

(2) 进程运行推进的顺序不合适。

(3) 资源分配不当等。

如果系统资源充足,进程的资源请求都能够得到满足,死锁出现的可能性就很低,否则就会因争夺有限的资源而陷入死锁。其次,进程运行推进顺序与速度不同,也可能产生死锁。

产生死锁的四个必要条件:

(1) 互斥条件:一个资源每次只能被一个进程使用。

(2) 请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放。

(3) 不剥夺条件:进程已获得的资源,在末使用完之前,不能强行剥夺。

(4) 循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。

这四个条件是死锁的必要条件,只要系统发生死锁,这些条件必然成立,而只要上述条件之一不满足,就不会发生死锁。 

 

死锁的两种类型

1、循环死锁:两个进程请求不同资源上的锁,每一个进程都需要对方持有的该资源上的锁,这时将发生循环死锁。如下图

澳门新浦京娱乐场网站 63

2、转换死锁:两个或多个进程都在事务中持有同一资源上的共享锁,并且都想把它升级为独占锁,但是,谁也没法升级直到其他的进程释放共享锁,如下图

澳门新浦京娱乐场网站 64

 

SQL Server中产生死锁的一些情况

1、由书签查找产生的死锁:这类死锁产生的原因是书签查找和更新数据产生的僵持状态。简单来说,就是由于Update语句对基本表产生X锁,然后需要对表上的索引也进行更新,而表上的索引正好被另一个连接进行查找,加了S锁,此时又产生书签查找去基本表加了X锁的数据进行书签查找,此时形成死锁 

澳门新浦京娱乐场网站 65

书签查找:当查询优化器使用非聚集索引进行查找时,如果所选择的列或查询条件中的列只部分包含在使用的非聚集索引和聚集索引中时,就需要一个查找(lookup)来检索其他字段来满足请求。对一个有聚簇索引的表来说是一个键查找(key lookup),对一个堆表来说是一个RID查找(RID lookup),这种查找即是——书签查找(bookmark lookup)。简单的说就是当你使用的sql查询条件和select返回的列没有完全包含在索引列中时就会发生书签查找。

解决方案:这种死锁可以通过Include列来减少书签查找,从而减少这种类型死锁发生的概率。

 

2、由外键产生的死锁: 这类死锁产生的原因来自外键约束。当主表(也就是主键是从表外键的那个表)更新数据时,需要查看从表,以确定从表的外键列满足外键约束。此时会在主表上加X锁,但这并不能阻止同一时间,另一个SPID向从表添加被修改的主表主键,为了解决这个问题,SQL Server在进行这类更新时,使用Range锁,这种锁是当隔离等级为序列化时才有的,因此在这时虽然隔离等级可能是默认的已提交读,但是行为却是序列化。这很可能就会导致死锁。

解决方案:向外键列添加索引,使得Range锁加在索引上,而不是表本身。从而降低了死锁发生的概率。

 

3、由于推进顺序不当产生的死锁:在多个事务对资源的使用顺序不当,形成死锁环路而引发的。

澳门新浦京娱乐场网站 66

解决方案:尽量使资源的使用顺序一致。这也是死锁问题出现最多的一种情况。

如何查看死锁

澳门新浦京娱乐场网站 67

澳门新浦京娱乐场网站 68

 

三、死锁的预防与优化

预防死锁

预防死锁就是破坏四个必要条件中的某一个和几个,使其不能形成死锁。有如下几种办法:

1)破坏互斥条件

破坏互斥条件有比较严格的限制,在SQL Server中,如果业务逻辑上允许脏读,则可以通过将隔离等级改为未提交读或使用索引提示。这样使得读取不用加S锁,从而避免了和其它查询所加的与S锁不兼容的锁互斥,进而减少了死锁出现的概率。

澳门新浦京娱乐场网站:谈死锁的督察剖判解决思路,mysql的产出处理体制_下篇。2)破坏请求和等待条件

这点由于事务存在原子性,是不可破坏的,因为解决办法是尽量的减少事务的长度,事务内执行的越快越好。这也可以减少死锁出现的概率。

3)破坏不剥夺条件

由于事务的原子性和一致性,不剥夺条件同样不可破坏。但我们可以通过增加资源和减少资源占用两个角度来考虑。

增加资源:比如说通过建立非聚集索引,使得有了额外的资源,查询很多时候就不再索要锁基本表,转而锁非聚集索引,如果索引能够"覆盖(Cover)"查询,那更好不过。因此索引Include列不仅仅减少书签查找来提高性能,还能减少死锁。

减少资源占用:比如说查询时,能用select col1,col2这种方式,就不要用select * .这有可能带来不必要的书签查找

 

最大限度减少死锁的方法

  1. 按同一顺序访问对象: 按同一顺序访问对象也就是:第一个事务提交或回滚后,第二个事务继续进行,这样不会发生死锁。

  2. 避免事务中的用户交互: 避免编写包含用户交互的事务,因为运行没有用户交互的批处理的速度要远远快于用户手动响应查询的速度,例如答复应用程序请求参数的提示。例如,如果事务正在等待用户输入,而用户去吃午餐了或者甚至回家过周末了,则用户将此事务挂起使之不能完成。这样将降低系统的吞吐量,因为事务持有的任何锁只有在事务提交或回滚时才会释放。即使不出现死锁的情况,访问同一资源的其它事务也会被阻塞,等待该事务完成。

  3. 保持事务简短并在一个批处理中: 在同一数据库中并发执行多个需要长时间运行的事务时通常发生死锁。事务运行时间越长,其持有排它锁或更新锁的时间也就越长,从而堵塞了其它活动并可能导致死锁。 保持事务在一个批处理中,可以最小化事务的网络通信往返量,减少完成事务可能的延迟并释放锁。

  4. 使用低隔离级别: 确定事务是否能在更低的隔离级别上运行,执行提交读取允许事务读取另一个事务已读取(未修改)的数据,而不必等待第一个事务完成。使用较低的隔离级别(例如提交读取)而不使用较高的隔离级别(例如可串行读)可以缩短持有共享锁的时间,从而降低了锁定争夺。

  5. 使用绑定连接: 使用绑定连接使同一应用程序所打开的两个或多个连接可以相互合作。次级连接所获得的任何锁可以象由主连接获得的锁那样持有,反之亦然,因此不会相互阻塞。

优化死锁的一些建议

(1)对于查询频繁的表尽量使用聚集索引;

(2)设法避免一次性影响大量记录的SQL语句,特别是INSERT和UPDATE语句;

(3)设法让UPDATE和DELETE语句使用合适的索引;

(4)使用嵌套事务时,避免提交和回退冲突;

(5)对数据一致性要求不高的查询使用 WITH(NOLOCK)

(6)减小事务的体积,事务应最晚开启,最早关闭,所有不是必须使用事务的操作必须放在事务外。

(7)查询只返回你需要的列,不建议使用 SELECT * FROM 这种写法。

本文由澳门新浦京娱乐场网站发布于数据库,转载请注明出处:澳门新浦京娱乐场网站:谈死锁的督察剖判解决