云顶娱乐平台 38

云顶娱乐平台:innodb事务锁

四 锁的互斥(兼容性)

  在sql
server里有个表,来维护锁与锁之间的兼容性,这是sqlserver预先定义好的,没有任务参数或配置能够去修改它们。如何提高兼容性呢?那就是在设计数据库结构和处理sql语句时应该考虑,尽量保持锁粒度小,这样产生阻塞的概率就会比较小,如果一个连接经常申请页面级,表级,甚至是数据库级的锁资源,程序产生的阻塞的可能性就越大。假设:事务1要申请锁时,该资源已被事务2锁住,并且事务1要申请的锁与事务2的锁不兼容。事务1申请锁就会出现wait状态,直到事务2的锁释放才能申请到。
可通过sp_lock查看wait等待(也就是常说的阻塞) 

  下面是最常见的锁模式的兼容性云顶娱乐平台 1

c)  查询有多个索引可以走,可以对不同索引加锁

云顶娱乐平台 2
根据死锁记录的结果,可以看出确实是这两个语句发生了死锁,且锁冲突发生在主键索引上。那么,为什么两个sql语句会存在锁冲突呢?冲突为什么会在主键索引上呢?语句(2)得到了主键索引锁,为什么还会再次申请锁呢?
锁冲突分析
2.1 innodb的事务与行锁机制
MySQL的事务支持不是绑定在MySQL服务器本身,而是与存储引擎相关,MyISAM不支持事务、采用的是表级锁,而InnoDB支持ACID事务、
行级锁、并发。MySQL默认的行为是在每条SQL语句执行后执行一个COMMIT语句,从而有效的将每条语句作为一个单独的事务来处理。
2.2 两语句加锁情况 在innodb默认的事务隔离级别下,普通的SELECT是不需要加行锁的,但LOCK IN
SHARE MODE、FOR
UPDATE及高串行化级别中的SELECT都要加锁。有一个例外,此案例中,语句(1)insert
into teamUser_20110121 select * from
teamUser会对表teamUser_20110121(ENGINE=
MyISAM)加表锁,并对teamUser表所有行的主键索引(即聚簇索引)加共享锁。默认对其使用主键索引。
而语句(2)DELETE FROM teamUser WHERE teamId=$teamId AND
titleWeight<32768 AND
joinTime<‘$daysago_1week’为删除操作,会对选中行的主键索引加排他锁。由于此语句还使用了非聚簇索引KEY
`k_teamid_titleWeight_score`
(`teamId`,`titleWeight`,`score`)的前缀索引,于是,还会对相关行的此非聚簇索引加排他锁。
2.3 锁冲突的产生 由于共享锁与排他锁是互斥的,当一方拥有了某行记录的排他锁后,另一方就不能其拥有共享锁,同样,一方拥有了其共享锁后,另一方也无法得到其排他锁。所
以,当语句(1)、(2)同时运行时,相当于两个事务会同时申请某相同记录行的锁资源,于是会产生锁冲突。由于两个事务都会申请主键索引,锁冲突只会发生
在主键索引上。
常常看到一句话:在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的。那就说明,单个SQL组成的事务锁是一次获得的。而此案例中,语句(2)
已经得到了主键索引的排他锁,为什么还会申请主键索引的排他锁呢?同理,语句(1)已经获得了主键索引的共享锁,为什么还会申请主键索引的共享锁呢?
死锁记录中,事务一等待锁的page no与事务二持有锁的page
no相同,均为218436,这又代表什么呢?
我们的猜想是,innodb存储引擎中获得行锁是逐行获得的,并不是一次获得的。下面来证明。
死锁产生过程分析 要想知道innodb加锁的过程,唯一的方式就是运行mysql的debug版本,从gdb的输出中找到结果。根据gdb的结果得到,单个SQL组成的事
务,从宏观上来看,锁是在这个语句上一次获得的,但从底层实现上来看,是逐个记录行查询,得到符合条件的记录即对该行记录的索引加锁。
Gdb结果演示如下:

八. 锁的超时

   在sql server
里锁默认是不会超时的,是无限的等待。多数客户端编程允许用户连接设置一个超时限制,因此在指定时间内没有反馈,客户端就会自动撤销查询,
但数据库里锁是没有释放的。

  可以通 select @@lock_timeout  查看默认值是 ” -1″, 可以修改超时时间 
例如5秒超时 set  lock_timeout  5000;

     下面是查看锁的等待时间,
wait_time是当前会话的等待资源的持续时间(毫秒)

select  session_id, blocking_session_id,command,sql_handle,database_id,wait_type
,wait_time,wait_resource
from sys.dm_exec_requests 
where blocking_session_id>50

 

(gdb) b lock_rec_lock
 Breakpoint 1 at 0×867120: file lock/lock0lock.c, line 2070.
 (gdb) c
 Continuing.
 [Switching to Thread 1168550240 (LWP 5540)]
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01c1
“789200″, index=0x2aada734b8, thr=0x2aada74c18) at
lock/lock0lock.c:2070
 2070 {
 Current language: auto; currently c
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=1029, rec=0x2aedbc80ba
“200″, index=0x2aada730b8, thr=0x2aada74c18) at
lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing.
 Breakpoint 1, lock_rec_lock (impl=0, mode=5, rec=0x2aedbe01cf
“789200″, index=0x2aada734b8, thr=0x2aada74c18) at
lock/lock0lock.c:2070
 2070 {
 (gdb) c
 Continuing.

二.锁的产生背景

  在关系型数据库里锁是无处不再的。当我们在执行增删改查的sql语句时,锁也就产生了。锁对应的就的是事务,不去显示加tran就是常说的隐式事务。当我们写个存储过程希望数据一致性时,
要么同时回滚,要么同时提交,这时我们用begin tran
来做显示事务。锁的范围就是事务。在sql server里事务默认是提交读(Read
Committed) 。
  锁是对目标资源(行、页、区、表..)获取所有权的锁定,是一个逻辑概念,用来保存事务的ACID.
当多用户并发同时操作数据时,为了避免出现不一致的数据,锁定是必须的机制。
但同时如果锁的数量太多,持续时间太长,对系统的并发和性能都没有好处。


云顶娱乐平台 3

七. 锁的升级

  7.1 使用profiler窗口查看实时的锁升级

云顶娱乐平台:innodb事务锁。  以单次批操作受影响的行数超过5000条时(锁数量最大值5000),升级为表锁。在sqlserver里可以选择完全关掉锁升级,虽然可以减少阻塞,但锁内存会增加,降低性能还可能造成更多死锁。

 锁升级缺点:会给其它会话带来阻塞和死锁。锁升级优点:减少锁的内存开销。

  检测方法:在profiler中查看lock:escalation事件类。通过查看Type列,可查看锁升级的范围,升级成表锁(object是表锁)

  如下图:

    云顶娱乐平台 4

云顶娱乐平台 5

  如果减少批操作量,就没有看到升级表锁, 可自行通过
escalation事件查看,下图就是减少了受影响的行数。

    云顶娱乐平台 6

  总结:将批操作量受影响行数减少到5000以下,减少锁的升级后,发生了更频繁的死锁,原因是多个page页的争用。后有人指出你先把并行度降下来(删除500一下的数据可以不使用并行)
在语句中设置maxdop = 1 这样应该不会死锁了。具体原因还需具体分析。

  7.2 使用dmv查看锁升级

sys.dm_db_index_operational_stats返回数据库中的当前较低级别 I/O、
锁定、 闩锁,和将表或索引的每个分区的访问方法活动。

index_lock_promotion_attempt_count:数据库引擎尝试升级锁的累积次数。

index_lock_promotion_count:数据库引擎升级锁的累积次数。

SELECT  OBJECT_NAME(ddios.[object_id], ddios.database_id) AS [object_name] ,
        i.name AS index_name ,
        ddios.index_id ,
        ddios.partition_number ,
        ddios.index_lock_promotion_attempt_count ,
        ddios.index_lock_promotion_count ,
        ( ddios.index_lock_promotion_attempt_count
          / ddios.index_lock_promotion_count ) AS percent_success
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.object_id = i.object_id
                                    AND ddios.index_id = i.index_id
WHERE   ddios.index_lock_promotion_count > 0
ORDER BY index_lock_promotion_count DESC;

  7.3 使用dmv查看页级锁资源争用

  page_lock_wait_count:数据库引擎等待页锁的累积次数。

  page_lock_wait_in_ms:数据库引擎等待页锁的总毫秒数。

  missing_index_identified:缺失索引的表。

SELECT  OBJECT_NAME(ddios.object_id, ddios.database_id) AS object_name ,
        i.name AS index_name ,
        ddios.index_id ,
        ddios.partition_number ,
        ddios.page_lock_wait_count ,
        ddios.page_lock_wait_in_ms ,
        CASE WHEN DDMID.database_id IS NULL THEN 'N'
             ELSE 'Y'
        END AS missing_index_identified
FROM    sys.dm_db_index_operational_stats(DB_ID(), NULL, NULL, NULL) ddios
        INNER JOIN sys.indexes i ON ddios.object_id = i.object_id
                                    AND ddios.index_id = i.index_id
        LEFT OUTER JOIN ( SELECT DISTINCT
                                    database_id ,
                                    object_id
                          FROM      sys.dm_db_missing_index_details
                        ) AS DDMID ON DDMID.database_id = ddios.database_id
                                      AND DDMID.object_id = ddios.object_id
WHERE   ddios.page_lock_wait_in_ms > 0
ORDER BY ddios.page_lock_wait_count DESC;

如何缩短锁的时间?

于是,两个事务分别拥有部分锁并等待被对方持有的锁,出现这种资源循环等待的情况,即死锁。此案例中被检测时候的锁冲突就发现在page
no为218436和218103的锁上。
InnoDB
会自动检测一个事务的死锁并回滚一个或多个事务来防止死锁。Innodb会选择代价比较小的事务回滚,此次事务(1)解锁并回滚,语句(2)继续运行直至事务结束。
innodb死锁形式归纳 死锁产生的四要素:互斥条件:一个资源每次只能被一个进程使用;请求与保持条件:一个进程因请求资源而阻塞时,对已获得的资源保持不放;不剥夺条件:进程
已获得的资源,在末使用完之前,不能强行剥夺;循环等待条件:若干进程之间形成一种头尾相接的循环等待资源关系。
Innodb检测死锁有两种情况,一种是满足循环等待条件,还有另一种策略:锁结构超过mysql配置中设置的最大数量或锁的遍历深度超过设置的最大深度
时,innodb也会判断为死锁(这是提高性能方面的考虑,避免事务一次占用太多的资源)。这里,我们只考虑满足死锁四要素的情况。
死锁的形式是多样的,但分析到innodb加锁情况的最底层,因循环等待条件而产生的死锁只有可能是四种形式:两张表两行记录交叉申请互斥锁、同一张表则存在主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级导致的锁等待队列阻塞。
以下首先介绍innodb聚簇索引与非聚簇索引的数据存储形式,再以事例的方式解释这四种死锁情况。
4.1聚簇索引与非聚簇索引介绍 聚簇索引即主键索引,是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序,聚簇索引的索引页面指针指向数据页面。非聚簇索引(即第二主键索
引)不重新组织表中的数据,索引顺序与数据物理排列顺序无关。索引通常是通过B-Tree数据结构来描述,那么,聚簇索引的叶节点就是数据节点,而非聚簇
索引的叶节点仍然是索引节点,通常是一个指针指向对应的数据块。
而innodb在非聚簇索引叶子节点包含了主键值作为指针。(这样是为了减少在移动行或数据分页时索引的维护工作。)其结构图如下:
云顶娱乐平台 7

三.锁的全面认识

  3.1 锁住的资源

  我们知道sql
server的存储数据单元包括文件组,页,区,行。锁住资源范围从低到高依次对应的是:行(RID/KEY)锁,页(PAGE)锁,
表(OBJECT)锁。可通过sp_lock查看,比如:
当我们操作一条数据时应该是行锁, 大批量操作时是页锁或表锁,
这是大批量操作会使锁的数量越多,锁就会自动升级
将大量行锁合成多个页锁或表锁,来避免资源耗尽。SQL SERVER要锁定资源时,默认是从最底级开始锁起(行)
。锁住的常见资源如下:

名称

资源

说明

数据行 RID 锁住堆中(表没有建聚集索引)的单个行。格式为File:Page:SlotID  如 1:8787:4
索引键 KEY 锁住T-tree(索引)中单个行,是一个哈值值。如:(fb00a499286b)                 
PAGE 锁住数据页(一页8kb,除了页头和页尾,页内容存储数据)可在sys.dm_os_buffer_descriptors找到。格式FileID :Page Number 如1:187541
范围 extent 锁住区(一组连续的8个页 64kb)FileID:N页 。如:1:78427
数据表 object 通常是锁整个表。 如:2858747171
文件 File 一般是数据库文件增加或移除时。如:1
数据库 database 锁住整个数据库,比如设置修改库为只读模式时。 database ID如:7

    下图是通过sp_lock的查看的,显示了锁住的资源类型以及资源

云顶娱乐平台 8

  3.2 锁的类型及锁说明

锁类型 锁说明
共享锁 (S锁) 用于不更改或不更新数据的读取操作,如 SELECT 语句。
更新锁 (U锁) 它是S与X锁的混合,更新实际操作是先查出所需的数据,为了保护这数据不会被其它事务修改,加上U锁,在真正开始更新时,转成X锁。U锁和S锁兼容, 但X锁和U锁不兼容。
独占锁(排它锁)(X锁) 用于数据修改操作,例如 INSERT、UPDATE 或 DELETE。 确保不会同时对同一资源进行多重更新
意向锁(I锁) (I)锁也不是单独的锁模式,用于建立锁的层次结构。 意向锁包含三种类型:意向共享 (IS)、意向排他 (IX) 和意向排他共享 (SIX)。意识锁是用来标识一个资源是否已经被锁定,比如一个事务尝试锁住一个表,首先会检查是否已有锁在该表的行或者页上。
架构锁(Sch-M,Sch-S) 在执行依赖于表架构操作时使用,例如:添加列或删除列 这个时候使用的架构修改锁(Sch-M),用来防止其它用户对这个表格进行操作。别一种是数据库引擎在编译和执行查询时使用架构性  (Sch-S),它不会阻止其它事务访问表格里的数据,但会阻止对表格做修改性的ddl操作和dml操作。
大容量更新 (BU) 是指数据大容量复制到表中时使用BU锁,它允许多个线程将数据并发地大容量加载到同一表,同时防止其它不进行大容量加载数据的进程访问该表。
键范围 当使用可序列化事务隔离级别时(SERIALIZABLE)保护查询读取的行的范围。 确保再次运行查询时其他事务无法插入符合可序列化事务的查询的行。下章介绍的事务时再详细说

 

云顶娱乐平台 9

六. 锁的持续时间

  下面是锁在不同事务隔离级别里,所持续占用的时间:

云顶娱乐平台 10

  6.1  SELECT动作要申请的锁

    我们知道select 会申请到共享锁,下面来演示下共享锁在Repeatable
重复读的级别下,共享锁保留到事件提交时才释放。

    具体是1.事务A设置隔离级别为Repeatable重复读,开启事务运行且不提交事务。

       2.再打开一个会话窗口,使用sys.dm_tran_locks来分析查看事务的持有锁。 

--开启一个事务A, 设置可重复读, 不提交
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ 
BEGIN TRAN 
SELECT  * FROM dbo.Product WHERE SID=204144 

--上面执行完后,打开另一会话查询锁状态
SELECT  k.request_session_id,k.resource_type,k.request_status,k.request_mode,k.resource_description,
 OBJECT_NAME( p.object_id) as objectName,p.index_id FROM SYS.dm_tran_locks k LEFT JOIN SYS.PARTITIONS p
ON k.resource_associated_entity_id=p.hobt_id
ORDER BY request_session_id,resource_type

    先看看查询单条语句的执行计划,再看看锁住的资源

    云顶娱乐平台 11

    云顶娱乐平台 12

   通过DMV查询,我们看到:

    (1)首先是锁住DATABASE资源,是数据库级别的共享锁,以防止别人将数据库删除。

    (2)锁住OBJECT表资源,在Product表上加了意向共享锁IS,以防止别人修改表的定义。

    (3)锁住了二个PAGE页加了意向共享锁IS,通过上面执行计划可以看出来,查询出来的数据是通过索引查询50%,RID堆查询50%。这条数据分布在二个页上,通过where
SID来查找没有完全走索引查找。

    (4)通过第3点可以看出,数据1个页是对应RID行,另一页对应KEY行
二个共享锁,堆位置1:112205:25  ,KEY的哈希值(70009fe3578a) 。

  总结下:通过Repeatable
重复读,直要事务不提交,共享锁一直会存在。针对想减少被别人阻塞或者阻塞别人的概率,能考虑事情有:1.
尽量减少返回的记录,返回的记录越多,需要的锁也就越多,在Repeatable隔离级别及以上,更是容易造成阻塞。2.返回的数据如果是一小部份,尽量使用索引查找,避免全表扫描。3.可以的话,根据业务设计好最合适的几个索引,避免通过多个索引找到结果。 
                                                

  4.2  UPDATE动作要申请的锁

    对于UPDATE需要先查询,再修改。具体是查询加S锁,找到将要修改的记录后先加U锁,真正修改时升级成X锁。还是通过上面的product表来演示具体:选用Repeatable级别,运行一个update语句(先kill
掉之前的会放52) 

--开启一个事务, 设置可重复读, 不提交
BEGIN TRAN 
UPDATE    dbo.Product SET model='test'
 WHERE SID IN(10905,119921,204144)

 
 云顶娱乐平台 13

  通过 dmv查看,吓一跳没想到锁住了这么多资源,纠结
那下面试着来分析下为什么锁住这么多资源:使用sys.indexes查看index_id
的0,2,4各使用了什么索引

  SELECT  * FROM sys.indexes WHERE object_id= OBJECT_id('product')

  云顶娱乐平台 14

  (1)这个product表并没有建聚集索引,是在堆结构上建立的非索聚索引,index_id=0
是堆, index_id=2和4 又是分别二个非索聚索引

  (2)同样在DATABASE和OBJECT资源 上都加了共享锁。

  (3)意向排它锁IX,锁住的Page共9页
说明数据关联了9页,其中堆上3页,ix_1非索聚索引上3页,ixUpByMemberID非索聚索引上3页。 

  (4)
排它锁X锁住RID堆上3行,KEY索引上6行。大家可能会觉得奇怪明明只改三行的model值,为什么会涉及到9行呢?
 我来解释下这个表是建了三个非聚集索引,其中ix_1索引里有包含列model,xUpByMemberID索引里也同样有包含列model,还有model数据是在堆,当堆上数据修改后,model关联的非聚集索引也要重新维护。如下图

   云顶娱乐平台 15云顶娱乐平台 16

  (5) 这里还有架构锁Sch-s ,锁住了元数据。

  总结:1.一定要给表做聚集索引,除了特殊情况使用堆结构。2.要修改的数据列越多,锁的数目就会越多,这里model就涉及到了9行维护。3.
描述的页面越多,意向锁就会越多,对扫描的记录也会加锁,哪怕没有修改。所以想减少阻塞要做到:1).尽量修改少的数据集,修改量越多,需要的锁也就越多。2)
尽量减少无谓的索引,索引的数目越多,需要的锁也可能越多。3.严格避免全局扫描,修改表格记录时,尽量使用索引查询来修改。

  4.3  DELETE动作要申请的锁  

BEGIN TRAN 
DELETE     dbo.Product WHERE SID =10905

  
 云顶娱乐平台 17

   (1) 删除了RID堆的数据,以及关联的非聚集索引三个key的值分别是(2,5,4)

   (2) 在要删除的4个page上加了意向排它锁,同样对应一个RID和三个KEY。

   (3)在OBJECT资源表上加了意向排它锁。

   总结:在DELETE过程中是先找到符合条件的记录,然后再删除,
可以说是先SELECT后DELETE,如果有索引第一步查询申请的锁会比较
少。 对于DELETE不但删除数据本身,还会删除所有相关的索引键,一个表上的索引越多,锁的数目就会越多,也容易阻塞。为了防步阻塞我们不能不建索引,也不能随便就建索引,而是要根据业务建查询绝对有利的索引。

  4.4  INSERT动作要申请的锁 

BEGIN TRAN 
INSERT into    dbo.Product VALUES('modeltest','brandtest',GETDATE(),9708,'test')

   云顶娱乐平台 18

    对于以上三种动作,INSERT相对简单点,只需要对要插入数据本身加上X锁,对应的页加IX锁,同步更新了关联的索引三个key。

    这里新增跟删除最终显示的锁一样,但在锁申请的过程中,新增不需要先查询到数据s锁,升级u锁,再升级成X锁。

云顶娱乐平台 19.png)

当执行update、delete操作时,会修改表中的数据信息。由于innodb存储引擎中索引的数据存储结构,会根据修改语句使用的索引以及修改信息
的不同执行不同的加锁顺序。当使用索引进行查找并修改记录时,会首先加使用的索引锁,然后,如果修改了主键信息,会加主键索引锁和所有非聚簇索引锁,修改
了非聚簇索引列值会加该种非聚簇索引锁。
此案例中,事务一使用非聚簇索引查找并修改主键值,事务二使用主键索引查找并修改主键值,加锁顺序不同,导致同时运行时产生资源循环等待。
锁升级造成锁队列阻塞 案例:同一行记录,事务内进行锁升级,与另一等待锁发送锁队列阻塞,导致死锁

五. 锁与事务关系

  如今系统并发现象,引起的资源急用,出现的阻塞死锁一直是技术人员比较关心的。这就涉及到了事务,
事务分五种隔离级别,每个隔离级别有一个特定的并发模式,不同的隔离级别中,事务里锁的作用域,锁持续的时间都不同,后面再详细介绍事务。这里看下客户端并发下的锁与事务的关系,
可以理解事务是对锁的封装,事务就是在并发与锁之间的中间层。如下图:

  云顶娱乐平台 20

     innodb消灭幻读仅仅为了确保 statement模式replicate的主从一致性

云顶娱乐平台 21

 一.概述

  讲到sql
server锁管理时,感觉它是一个大话题,因为它不但重要而且涉及的知识点很多,重点在于要掌握高并发要先要掌握锁与事务,涉及的知识点多它包括各式各样的锁,锁的组合,锁的排斥,锁延伸出来的事务隔离级别,
锁住资源带来的阻塞,锁之间的争用造成的死锁,索引数据与锁等。这次介绍锁和事务,我想分上中下篇,上篇详细介绍锁,中篇介绍事务,下篇总结,
针对锁与事务我想把我掌握的以及参考多方面资料,整合出来尽量说详细。
最后说下,对于高级开发人员或DBA,锁与事务应该是重点关注的,它就像是数据库里的一个大boss,如完全掌握了它,数据库就会像就像庖丁解牛一样游刃有余 
哈哈 。

云顶娱乐平台 22

主键索引锁与非聚簇索引锁冲突 案例:同一行记录,两事务使用不同的索引进行更新操作

线上环境中:

您可能感兴趣的文章:

  • Mysql数据库锁定机制详细介绍
  • mysql锁表和解锁语句分享
  • MySQL行级锁、表级锁、页级锁详细介绍
  • MYSQL锁表问题的解决方法
  • mysql
    数据库死锁原因及解决办法
  • mysql
    锁表锁行语句分享(MySQL事务处理)
  • 一次Mysql死锁排查过程的全纪录
  • Mysql(MyISAM)的读写互斥锁问题的解决方法
  • mysql锁定单个表的方法
  • 查找MySQL线程中死锁的ID的方法
  • Mysql 数据库死锁过程分析(select for
    update)
  • MySQL锁机制与用法分析

四种基本锁模式

云顶娱乐平台 23


条件:
A、 两事务使用不同索引
B、 申请的锁互斥
C、 操作同一行记录

 

复制代码 代码如下:

行锁: innodb ,oracle

死锁日志打印出的时间点表明,语句(1)运行过程中,当语句(2)开始运行时,发生了死锁。
当mysql检测出死锁时,除了查看mysql的日志,还可以通过show InnoDB STATUS
G语句在mysql客户端中查看最近一次的死锁记录。由于打印出来的语句会很乱,所以,最好先使用pager
less命令,通过文件内容浏览方式查看结果,会更清晰。(以nopager结束)
得到的死锁记录如下:

 

条件:
A、 两事务操作同一行记录
B、 一事务对某一记录先申请共享锁,再升级为排他锁
C、 另一事务在过程中申请这一记录的排他锁

 

避免死锁的方法 InnoDB给MySQL提供了具有提交,回滚和崩溃恢复能力的事务安全(ACID兼容)存储引擎。InnoDB锁定在行级并且也在SELECT语句提供非锁定读。这些特色增加了多用户部署和性能。
但其行锁的机制也带来了产生死锁的风险,这就需要在应用程序设计时避免死锁的发生。以单个SQL语句组成的隐式事务来说,建议的避免死锁的方法如下:
1.如果使用insert…select语句备份表格且数据量较大,在单独的时间点操作,避免与其他sql语句争夺资源,或使用select
into outfile加上load data infile代替
insert…select,这样不仅快,而且不会要求锁定
2.
一个锁定记录集的事务,其操作结果集应尽量简短,以免一次占用太多资源,与其他事务处理的记录冲突。
3.更新或者删除表格数据,sql语句的where条件都是主键或都是索引,避免两种情况交叉,造成死锁。对于where子句较复杂的情况,将其单独通过sql得到后,再在更新语句中使用。
4.
sql语句的嵌套表格不要太多,能拆分就拆分,避免占有资源同时等待资源,导致与其他事务冲突。
5.
对定点运行脚本的情况,避免在同一时间点运行多个对同一表进行读写的脚本,特别注意加锁且操作数据量比较大的语句。
6.应用程序中增加对死锁的判断,如果事务意外结束,重新运行该事务,减少对功能的影响。

 

Gdb结果显示,语句(1)(2)加锁的获取记录为多行,即逐行获得锁,这样就解释了语句(2)获得了主键索引锁还再次申请主键索引锁的情况。
由于语句(1)使用了主键索引,而语句(2)使用了非聚簇索引,两个事务获得记录行的顺序不同,而加锁的过程是边查边加、逐行获得,于是,就会出现如下情况:

这样仅是在update这个语句加锁,大大的缩短的锁的时间提高了并发性;

条件:
A、 两事务分别操作两个表、相同表的同一行记录
B、 申请的锁互斥
C、 申请的顺序不一致

 

云顶娱乐平台 24

  自动:insert前

当使用非聚簇索引时,会根据得到的主键值遍历聚簇索引,得到相应的记录。
4.2四种死锁情况 在InnoDB中,使用行锁机制,于是,锁通常是逐步获得的,这就决定了在InnoDB中发生死锁是可能的。
即将分享的四种死锁的锁冲突分别是:不同表的相同记录行索引锁冲突、主键索引锁冲突、主键索引锁与非聚簇索引锁冲突、锁升级造成锁队列阻塞。
不同表的相同记录行锁冲突 案例:两个表、两行记录,交叉获得和申请互斥锁
云顶娱乐平台 25

   手动:

此案例涉及TSK_TASK表,该表相关字段及索引如下:
ID:主键;
MON_TIME:监测时间;
STATUS_ID:任务状态;
索引:KEY_TSKTASK_MONTIME2 (STATUS_ID, MON_TIME)。

 

主键索引锁冲突 案例:本文案例,产生冲突在主键索引锁上
条件:
A、 两sql语句即两事务操作同一个表、使用不同索引
B、 申请的锁互斥
C、 操作多行记录
D、 查找到记录的顺序不一致

通过索引项加锁实现

(说明:”789200″为非聚簇索引,”200″为主键索引)

 

案例描述 在定时脚本运行过程中,发现当备份表格的sql语句与删除该表部分数据的sql语句同时运行时,mysql会检测出死锁,并打印出日志。
两个sql语句如下: (1)insert into backup_table select * from source_table
(2)DELETE FROM source_table WHERE Id>5 AND titleWeight<32768
AND joinTime<‘$daysago_1week’
teamUser表的表结构如下:
PRIMARY KEY (`uid`,`Id`),
KEY `k_id_titleWeight_score` (`Id`,`titleWeight`,`score`),
ENGINE=InnoDB
两语句对source_table表的使用情况如下:

 

云顶娱乐平台 26

innodb的gap lock 间隙锁

gap lock 间隙锁 解释:

X锁

mysql>show global variables like "%wait%"

2)做业务流程

例子:

     数据库挑选冲突事务中回滚代价较小的事务回滚

   
 悲观锁开始就给所有记录加锁,一般等所有业务流程完成,才释放锁;因此会对并发性能有一定的影响;

 

云顶娱乐平台 27.png)

还可以通过设置innodb monitor
来进一步观察发生锁冲突的表,数据行等,并分析锁争用的原因:

B的更改还没有提交时,A已经再次修改了数据。


 

   自动:update,delete 前

事务锁粒度

小心gap lock

排查死锁:

mysql> select * from t2 where  b =9 for update ;

   
 业务流程中的悲观锁(开始的时候,在所有记录加锁,直到最后释放;而乐观锁开始不加锁,只是在最后提交中看提交有没有成功,没成功返回给应用程序)

这句对本意在b=9这行加索引,b又没有加索引,所以这是对整个表加锁;因为没有指定a
=2,所以mysql找不到a这个索引的;

 

死锁

 

 

innodb
行锁

 

 

数据库加锁操作

意向锁,简单来说就是:

云顶娱乐平台 28.png)

 

innodb锁模式互斥

 

 


此时A使用原来的元数据作为基础更新后,B的更新便会丢失;

 

 

 

a) 只有,有条件走索引才能实现行级锁

        –对同一表的操作根据加锁条件进行排序

3)在update时,加锁且判断,现在的amount和开始的amount是否为一个值,如果是,说明这期间amount为改变,则更新;如果amount值改了,则不更新,交给业务来判断该怎么做。

  手动:select * from tb_test lock in share mode;

 b)  索引上有重复值,可能锁住多个记录 

云顶娱乐平台 29.png)

自增主键做条件更新,性能最好;

云顶娱乐平台 30.png)

读的隔离性由MVCC确保

     尽量缩短事务长度

云顶娱乐平台 31.png)

 

行锁升级成表锁:

 云顶娱乐平台 32

 

  •  更新丢失
  •  innodb意向锁:
    • 表锁
    • 自动施加、自动释放
    • 云顶娱乐平台,为了揭示事务下一行将被请求的锁类型
  •  S锁:in share mode

  •  X锁:for update
  •  innodb行锁特点:
    • 只有条件走索引才能实现行锁
    • 索引上有重复值可能锁住多个记录
    • 查询有多个索引可以走,可以对不同索引加锁
  •  gap lock:间隙锁,消灭幻读

  •  死锁解决:数据库挑回滚代价较小的事务回滚;
  •  死锁预防:
    • 单表,更新条件排序
    • 避免跨表事务,缩短事务长度
  •  锁升级:

    • 单独sql语句在单个对象的锁数量超过阙值
    • 锁资源占用的内存超过了激活内存的40%;
  •  innodb根据页进行加锁,并采用位图方式,定位到行的,所需资源较小

  • 了解触发死锁的sql所在事务的上下文
  • 根据上下文语句加锁的范围来分析存在争用的记录
  • 通常改善死锁的主要方法:

解决办法:

 

 

 总结

 

     单表死锁可以根据批量更新表的更新条件排序

innodb不存在锁升级的问题。因为其不是根据每个记录来产生行锁的,相反,其根据每个事务访问的每个页对锁进行管理的,采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录,其开销通常都是一致的。

云顶娱乐平台 33

mysql> select * from t2;
+------+------+
| a    | b    |
+------+------+
|   20 |    2 |
|   24 |    4 |
|   27 |    5 |
|   27 |    6 |
|   27 |    8 |
|   30 |    6 |
|   31 |    4 |
|   32 |    9 |
+------+------+
8 rows in set (0.00 sec)

在A连接中给a=27 加锁(a 是有索引的)
mysql> select * from t2 where a=27 for update;
+------+------+
| a    | b    |
+------+------+
|   27 |    5 |
|   27 |    6 |
|   27 |    8 |
+------+------+
3 rows in set (0.00 sec)

而innodb 通过间隙锁是的B连接中  insert into t2 values(27,3)
插入失败,来消灭幻读的出现。

gap lock消灭幻读

自增主键做条件更新,性能做好;

mysql> show status like '%innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     |
+-------------------------------+-------+
5 rows in set (0.00 sec)

 

 

 

 

 

latch/mutex 内存底层锁;

更新丢失

 

在开始的时候不读取数据,等到要提交的时候读取并加锁提交;

select *  from tb_test   for update;

锁等待时间:innodb_lock_wait_timeout

死锁预防

表锁:Myisam ,memory

S锁

 

页锁:sql server

 

  • 意向锁总是自动先加,并且意向锁自动加自动释放
  • 意向锁提示数据库这个session将要在接下来将要施加何种锁
  • 意向锁和X/S 锁级别不同,除了阻塞全表级别的X/S锁外其他任何锁 

 

 

 

 云顶娱乐平台 34

 

 

 

 

 

B连接中则只能插入不在这个区间的数据;

 

注意

 

 

 云顶娱乐平台 35

 

若其中任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。

 

 


云顶娱乐平台 36.png)

 

  • 共享锁(S)-读锁-行锁
  • 排他锁(X)-写锁-行锁
  • 意向共享锁(IS)-表级 :事务想要获得一张表中某几行的共享锁
  • 意向排他锁(IX)-表级:事务想要获得一张表中某几行的排他锁


云顶娱乐平台 37

 

 

一般的select语句不加任何锁,也不会被任何事物锁阻塞

innodb支持意向锁设计比较简练,其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。



业务逻辑加锁

 

 

     可能冲突的跨表事务尽量避免并发

但是这种方法是有局限的,它会将a=24–29(30-1)中间的任何数都锁住,所以才叫间隙锁;

但是如果业务十分的繁忙,amount的值在不断改变,此时这个update
就不断的失败,整个事务就不断的失败,反而影响了 性能。那么该如何做呢?

说明,表中没有索引时,innodb将对整个表加锁,而不能体现行锁的特性;

在修改数据上加写锁,当有锁时,A会等B更新提交完,才可以继续在B的基础上继续更新;

 

mysql> show create table t2G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`),
  KEY `b` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> select * from t2;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    2 |    9 |
+------+------+
在A连接中对 a=1 and b=2 加锁;
mysql> select * from t2 where a =1 and b =2  for update;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+

此时B连接中对a =1 and b=3 ,也是可以加锁的;这是因为mysql 可以从a=1这个索引来加锁,也可以对b=3加锁;
所以就与上面b)中只能对a=1索引来加锁 区别开来;

mysql> select * from t2 where a =1 and b =3  for update;
+------+------+
| a    | b    |
+------+------+
|    1 |    3 |
+------+------+

如需要对页上的记录R进行X锁,那么分别需要对该记录所在的数据库,表,页,上意向锁IX,最后对记录R上X锁。

  • 控制对共享资源进行并发访问
  • 保护数据的完整性和一致性

 云顶娱乐平台 38

        –拆分长事务

通过索引项加锁实现的例子:

mysql> show create table t2G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  KEY `a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> select * from t2;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    1 |    3 |
|    2 |    9 |
+------+------+

在A连接中,在a=1,b=2处加一个写锁;实际上 是在a=1这个索引上加的锁
mysql> select * from t2 where a=1 and b=2 for update;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

在B连接中,在a=1 and b=3处加写锁失败,因都是a=1这个索引,而A中已经对a=1这个索引的行加过了锁;
mysql> select * from t2 where a =1 and b=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

此时B连接是可以对 a=2 and b =9 这一行中,在a=2 这个索引上加锁的;
mysql> select * from t2 where a=2 and b =9 for update ;
+------+------+
| a    | b    |
+------+------+
|    2 |    9 |
+------+------+

undo log 用来帮助事务回滚及MVCC(多版本并发控制
,即select时可以使用行数据的快照,而不用等待锁资源)

获取innodb行锁争用情况

计算机程序锁

 

 

意向锁:

 

锁升级

简单说innodb根据页进行加锁,并采用位图方式,定位到行的,所需资源较小。

自动施加,自动释放,

mysql> show create table t2G;
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

mysql> select * from t2;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
|    1 |    3 |
+------+------+

此时A连接 在b =2 时加 写锁;
mysql> select * from t2 where b =2 for update;
+------+------+
| a    | b    |
+------+------+
|    1 |    2 |
+------+------+
而此时再B连接中再对b=3,加写锁时,失败;
mysql> select * from t2 where b=3 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

lock  主要是事务,数据库逻辑内容,事务过程

 

死锁数据库自动解决

innodb锁模式与粒度

 

 

此时隔离等级是Repeatable  Read,标准的是可以出现幻读现象的,

即在B连接中 insert into t2
values(27,3),是可以插入成功的,而且B连接提交后,A连接是可以查看到增加的,27,3这一行的。

 

原因:

  • 只有条件走索引才能实现行级锁                    a)
  • 索引上有重复值,可能锁住多个记录              b)
  • 查询有多个索引可以走,可以对不同索引加锁   c)
  • 是否对索引加锁实际上取决于Mysql执行计划
  • 由一句单独的sql语句在一个对象上持有的锁的数量超过了阈值,默认这个阈值为5000.值得注意的是,如果是不同对象,则不会发生锁升级。
  • 锁资源占用的内存超过了激活内存的40%时就会发生锁升级

如果发现锁争用比较严重,如innodb_row_lock_waits 和
innodb_row_lock_time_avg的值比较高,

1)开始的时候读取要修改的数据,amount(金额)