数据库开发 - 事务 死锁分析与解决
数据库开发 - 事务 死锁分析与解决
抢小孩糖吃 发表于1年前
数据库开发 - 事务 死锁分析与解决
  • 发表于 1年前
  • 阅读 69
  • 收藏 0
  • 点赞 0
  • 评论 0

腾讯云 新注册用户 域名抢购1元起>>>   

#死锁分析与解决 ##事务并发执行

输入图片说明

##事务持锁 MySQL数据库是以行加锁的方式,避免不同事务,对同一行数据库进行同时修改的。首先来看事务一,对张三这条记录的Account字段进行修改,需要持有张三这条数据库的行锁。然后事务二也同时并发执行,事务二首先修改李四这条数据库记录的Corp字段,持有李四这条数据库的行锁。
此时两个事务各持有一个行锁,但是接下来事务一要持有事务二的行锁,事务二要持有事务一的行锁。这样就形成了事务一与事务二相互等待,导致两个事务都无法继续执行下去。

输入图片说明

##死锁 死锁:指两个或者两个以上的事务,在执行过程中,因争夺锁资源而造成的一种互相等待的现象。

死锁必须是两个或者两个以上的事务,单个事务不可能发生死锁。死锁是因为争夺锁资源,导致相互持有锁资源,导致相互等待。需要外部干涉的现象。

##死锁产生的必要条件

  • 互斥
    • 并发执行的事务为了进行必要的隔离保证执行正确,在事务结束前,需要对修改的数据库记录持锁,保证多个事务对相同数据库记录串行修改
    • 对于大型并发系统无法避免
  • 请求与保持
    • 一个事务申请多个资源,已经持有一个资源锁,等待另外一个资源锁
    • 死锁仅发生在请求两个或者两个以上的锁对象
    • 由于应用实际需要,难以消除
  • 不剥夺
    • 已经获得锁资源的事务,在未执行前,不能被强制剥夺,只能使用完时,由事务自己释放。
    • 一般用于已经出现死锁时,通过破坏该条件达到解除死锁的目的。
      我们只要强制回滚某个持有锁的事务,让该事务执行完毕。这样就解除了死锁。
    • 数据库系统通常通过一定的死锁检测机制发现死锁,强制回滚代价相对较小的事务,达到解除死锁的目的。
  • 环路等待
    • 发生死锁时,必然存在一个事务--所的环形链。
    • 按照同一顺序获取锁,可以破坏该条件。
    • 通过分析死锁事务之间的锁竞争关系,调整SQL的顺序,达到消除死锁的目的。

##MySQL中的锁 排它锁我们用X来表示,共享锁我们用S来表示。所谓排它锁,就是和任何的锁相互冲突,如果一个事务添加了排它锁,不管其他事务添加任何的锁,都必须要等待这个事务。所谓共享锁是指,多个事务可以共享一把锁,多个锁可以兼容,也就是说,事务一获取共享锁的情况下,事务二还是要加共享锁,则可以直接获取无需等待。如果事务二需要添加一个排它锁,则必须要等待。 输入图片说明

##加锁方式

  • 外部加锁:
    • 由应用程序添加,锁依赖关系较容易分析
      这种依赖关系可以通过分析SQL语句得到,一旦出现死锁时,相对来说比较容易分析
    • 共享锁(S) : SELECT * FROM table LOCK IN SHARE MODE
    • 排它锁(X): SELECT * FROM table FOR UPDATE
  • 内部加锁:
    • 为了实现ACID特性,由数据库系统内部自动添加
    • 加锁规则繁琐,与SQL执行计划、事务隔离级别、表索引结构有关
    • 共享锁(S)和排它锁(X) 要求:对外部加锁能够掌握,对内部加锁的一些基本的场景有了解。对于复杂场景的内部加锁可以查阅专业的MySQL内核的数据进行参考。

##哪些SQL需要持有锁 在MySQL数据库中,所有的SELECT读

  • 快照读:
    • Innodb实现了多版本控制(MVCC),支持不加锁快照读。
    • 所有SELECT操作是不加锁的,SELECT * FROM table WHERE...
    • 这样可以保证SELECT性能,能够保证同一个SELECT结果集是一致的
    • 不能保证同一个事务内部,SELECT语句和其他语句的数据一致性,如果业务需要,需通过外部显示加锁。

需要持锁的SQL语句

  • 当前读:
    • SELECT * FROM table LOCK IN SHARE MODE
    • SELECT * FROM table FOR UPDATE
    • UPDATE FROM table SET ...
    • INSERT INTO...
    • DELETE FROM table... 增删改的加锁与内部的隔离级别息息相关,我们是不允许出现幻读的现象,有可能会加一些间隙锁,避免出现幻读的现象。

##SQL加锁分析 UPDATE user SET account=0 WHERE id = 1语句,直接在行记录进行加排他锁,由于快照读的原因此时SELECT操作是不会被阻塞的,如果是SELECT FOR UPDATE或者是SELECT IN SHARE MODE,都是会被阻塞的。

输入图片说明

SELECT userName FROM user WHERE id=1 IN SHARE MODE对行记录添加的是共享锁,此时,如果其他事务要执行SELECT IN SHARE MODE的话,对同一行记录还是可以进行执行的。如果外部其他事务要执行SELECT FOR UPDATE的话,则一定会被阻塞。

输入图片说明

##分析死锁的常用办法 MySQL数据库会自动分析死锁,并强制回滚代价最小的事务,不需要开发人员去关心。死锁的解除MySQL会自动帮助我们去做。但是我们需要在死锁解除以后,分析死锁产生的SQL语句,避免死锁再次产生。如下方法捕获死锁的SQL语句。

MySQL命令:

show engine innodb status

执行完毕以后,会有一大段内容出现其中,标红部分是关于死锁的,列出了发生死锁时两个等待的SQL语句,然后列出了系统强制回滚的是哪个事务,知道了这些SQL语句,可以分析SQL语句的加锁方式,来调整SQL语句的顺序,改变SQL语句保证按顺序获取锁资源,这样就可以有效的避免死锁的产生。

输入图片说明 #附件 ##自己在MySQL数据库当中,执行

show engine innodb status

得到的结果

| InnoDB |      |
=====================================
2016-10-08 22:04:17 0x7f61915e0700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 45 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 41 srv_active, 0 srv_shutdown, 12620 srv_idle
srv_master_thread log flush and writes: 12661
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 73
OS WAIT ARRAY INFO: signal count 57
RW-shared spins 0, rounds 93, OS waits 45
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 93.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 12583
Purge done for trx's n:o < 12583 undo n:o < 0 state: running but idle
History list length 174
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421532535461264, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421532535460352, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421532535459440, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421532535458528, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421532535457616, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
373 OS file reads, 290 OS file writes, 181 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
Hash table size 34673, node heap has 0 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 3162168
Log flushed up to   3162168
Pages flushed up to 3162168
Last checkpoint at  3162159
0 pending log flushes, 0 pending chkp writes
118 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 137428992
Dictionary memory allocated 317905
Buffer pool size   8191
Free buffers       7813
Database pages     378
Old database pages 0
Modified db pages  0
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 343, created 35, written 143
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 378, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=2247, Main thread ID=140057180620544, state: sleeping
Number of rows inserted 175, updated 14, deleted 3, read 356
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
共有 人打赏支持
抢小孩糖吃
粉丝 61
博文 182
码字总数 202790
×
抢小孩糖吃
如果觉得我的文章对您有用,请随意打赏。您的支持将鼓励我继续创作!
* 金额(元)
¥1 ¥5 ¥10 ¥20 其他金额
打赏人
留言
* 支付类型
微信扫码支付
打赏金额:
已支付成功
打赏金额: