mysql 开发进阶篇系列 13 锁问题(关于表锁,死锁示

2019-09-27 17:44 来源:未知

二. 关于死锁

  在myisam中是使用的表锁,在获得所需的全部锁时, 要么全部满足,要么等待,因此不会出现死锁。下面在innodb中演示一个死锁例子:

会话1

会话2

SET autocommit =0

SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

SET autocommit =0

SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

-- 因为会话2 已获得排他锁, 该语句等待

 SELECT * FROM cityNew  WHERE city_id=103 FOR UPDATE;

 

 

-- 死锁

 SELECT * FROM city  WHERE city_id=103 FOR UPDATE;

错误代码: 1213

Deadlock found when trying to get lock; try restarting transaction

  上面案例中, 两个事务都需要获得对方持有的排他锁才能继续完成事务,这种循环锁等待就是典型的死锁。 发生死锁后,innodb会自动检测到,并使一个事务释放锁并回退(回滚),另一个事务得锁完成事务。

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

三. 锁等待查看    

  涉及外部锁或表锁,innodb并不能完全自动检测到死锁,这需要设置锁等待超时参数innodb_lock_wait_timeout来解决(设置需慎重),这个参数并不是只用来解决死锁问题,在并发下,大量事务无法立即获得所需锁而挂起,将占用大量资源,甚至拖跨数据库 (在sql server中默认是-1 总是等待)。

--  下面是5秒  获取不到锁就超时
SHOW GLOBAL VARIABLES LIKE 'innodb_lock_wait_timeout';

图片 2

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

一. 什么时候使用表锁

  对于INNODB表,在绝大部分情况下都应该使用行锁。在个别特殊事务中,可以考虑使用表锁(建议)。
  1. 事务需要更新大部份或全部数据,表又比较大,默认的行锁不仅使这个事务执行效率低,可能造成其他事务长时间锁等待和锁冲突,这种情况考虑使用表锁来提高事务的执行速度(具我在sql server中的经历,该大表有上100w,删除40w,表锁有时会造成长时间未执行完成. 还是使用分批来执行好)。
  2. 事务涉及多个表,比较复杂,很可能引起死锁,造成大量事务回滚。这种情况可以考虑一次性锁定事务涉及的表,避免死锁,减少数据库因事务回滚带来的开销。
  使用表锁注意两点
    (1) lock tables虽然可以给innodb加表锁,但表锁不是由innodb存储引擎层管理,则是由上层mysql server负责。仅当autocommit=0, innodb_table_locks=1(默认设置)时,innodb层才知道mysql加的表锁,mysql server也才能感知innodb加的行锁。
    (2) 用lock tables对innodb表加锁时要注意, 要将autocommit 设置为0,否则mysql 不会给表加锁; 事务结束前,不要用unlock tables释放表锁,因为它会隐式的提交事务。 commit 或rollback 并不能释放用lock tables 加的表锁。必须用unlock tables释放表锁。

    下面在5.7版本数据库中,会话2也会阻塞,按上面说法是不会阻塞的,因为会话1没有设置SET autocommit =0(以后在论证)

-- 会话1 给city加表锁读,  不设置  SET autocommit =0
  LOCK TABLES city READ

  --  会话2 会阻塞
 UPDATE city SET CityCode='005' WHERE city_id=103  

  -- 会话1提交
 COMMIT;
 -- 会话1 释放表锁
 UNLOCK TABLES;

图片 3

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

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

图片 4

图片 5

案例描述 在定时脚本运行过程中,发现当备份表格的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表的使用情况如下:

图片 6
根据死锁记录的结果,可以看出确实是这两个语句发生了死锁,且锁冲突发生在主键索引上。那么,为什么两个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结果演示如下:

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

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

复制代码 代码如下:

TAG标签:
版权声明:本文由澳门新浦金网站发布于数据库,转载请注明出处:mysql 开发进阶篇系列 13 锁问题(关于表锁,死锁示