二. 关于死锁

  在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会自动检测到,并使一个事务释放锁并回退(回滚),另一个事务得锁完成事务。

意向共享锁(IS):

  通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加共享锁,那么此时innodb会先找到这张表,对该表加意向共享锁之后,再对记录A添加共享锁。也就是说一个数据行加共享锁前必须先取得该表的IS锁

LOCK_INDEX:锁的索引

 

一. 什么时候使用表锁

  对于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;

悲观锁与乐观锁的实现方式:

  悲观锁的实现依靠的是数据库提供的锁机制来实现,例如select * from
news where id=12 for
update,而乐观锁依靠的是记录数据版本来实现,即通过在表中添加版本号字段来作为是否可以成功提交的关键因素。

图片 1

一致性非锁定在MVCC读取当前数据库里面的数据在读取的数据正在被修改不会产生锁等待(对当前数据拍照片)读没有加锁
没有加共享锁 没有被阻塞


三. 锁等待查看    

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

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

图片 2

  有多种方法可以避免死锁,这里介绍常见的三种:

   ps:如果出现死锁,可以用SHOW INNODB
STATUS命令来确定最后一个死锁产生的原因和改进措施。

innodb_trx 
innodb_locks innodb_lock_waits

获取innodb行锁争用情况

行锁分为三种情况:

  Record Lock:对索引项加锁,即锁定一条记录。

  Gap Lock:对索引项之间的 ‘间隙’
、对第一条记录前的间隙或最后一条记录后的间隙加锁,即锁定一个范围的记录,不包含记录本身

  Next-key Lock:锁定一个范围的记录并包含记录本身(上面两者的结合)

  注意:InnoDB默认级别是repeatable-read(重复读)级别。ANSI/IOS
SQL标准定义了4种事务隔离级别:未提交读(read uncommitted),提交读(read
committed),重复读(repeatable read),串行读(serializable)

LOCK_SPACE:innodb存储引擎表空间ID号

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

行锁:

  InnoDB实现了两种类型额行级锁,共享锁和排它锁

图片 3

也可以从视图查看锁
事务状态 information_schma 库下面

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

读锁:

  读锁是共享的,或者说是相互不阻塞的。多个用户在同一时刻可以同时读取同一个资源,而互不干扰。

LOCK_REC:被锁住行的数量,若是表锁则该值为NULL

 

排它锁(X):

  排它锁也叫写锁,一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁(排他锁或者共享锁),即一个事务在读取一个数据行的时候,其他事务不能对该数据行进行增删改查

  设置排它锁:SELECT …. FOR UPDATE

  注意点:

  • 对于select
    语句,innodb不会加任何锁,也就是可以多个并发去进行select的操作,不会有任何的锁冲突,因为根本没有锁。
  • 对于insert,update,delete操作,innodb会自动给涉及到的数据加排他锁,只有查询select需要我们手动设置排他锁。

加排它锁:select *
from xx where ….. for update,update
delete 也是加排它锁

 

 锁的实现方式:

  在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。

  InnoDB行锁是通过给索引项加锁实现的,如果没有索引,InnoDB会通过隐藏的聚簇索引来对记录加锁。也就是说:如果不通过索引条件检索数据,那么InnoDB将对表中所有数据加锁,实际效果跟表锁一样

innodb_lock_waits

页锁:sql server

 死锁:

  我们说过MyISAM中是不会产生死锁的,因为MyISAM总是一次性获得所需的全部锁,要么全部满足,要么全部等待。而在InnoDB中,锁是逐步获得的,就造成了死锁的可能。

   
 发生死锁后,InnoDB一般都可以检测到,并使一个事务释放锁回退,另一个获取锁完成事务。但在涉及外部锁,或涉及锁的情况下,InnoDB并不能完全自动检测到死锁,这需要通过设置锁等待超时参数innodb_lock_wait_timeout来解决。需要说明的是,这个参数并不是只用来解决死锁问题,在并发访问比较高的情况下,如果大量事务因无法立即获取所需的锁而挂起,会占用大量计算机资源,造成严重性能问题,甚至拖垮数据库。我们通过设置合适的锁等待超时阈值,可以避免这种情况发生。

共享锁(S-LOCKING)允许一个事务去读一行,阻止其它事务获得相同数据集的排它锁

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

共享锁(S):

  共享锁也叫读锁,一个事务获取了一个数据行的共享锁,其他事务能获得该行对应的共享锁,但不能获得排他锁,即一个事务在读取一个数据行的时候,其他事务也可以读,但不能对该数据行进行增删改

  设置共享锁: SELECT …. LOCK IN SHARE MODE;

LOCK_TYPE:所得类型表锁还是行锁

select *  from tb_test   for update;

悲观锁:

  悲观锁,也叫悲观并发控制,当事务A对某行数据应用了锁,并且当这个事务把锁释放后,其他事务才能够执行与该锁冲突的操作,这里事务A所施加的锁就叫悲观锁。享锁和排他锁(行锁,间隙锁,next-key
lock)都属于悲观锁

LOCK_DATA:被锁住的行的主键值,若是表锁时,则该值为NULL;

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

在InnoDB加锁前,为什么要先start transaction

  innodb下锁的释放在事务提交/回滚之后,事务一旦提交/回滚之后,就会自动释放事务中的锁,innodb默认情况下autocommit=1即开启自动提交

检索条件使用索引和不使用索引的锁区别:

  检索条件有索引的情况下会锁定特定的一些行。

检索条件没有使用使用的情况下会进行全表扫描,从而锁定全部的行(包括不存在的记录)

Requesting_trx_id:申请资源的事务ID

 

写锁:

  写锁是排他的,也就是说一个写锁会阻塞其他的写锁和读锁。另外写锁比读锁有更高的优先级,因此一个写锁请求可能会被插入到读锁
队列的前面,但是读锁则不肯能插入到写锁的前面

Trx_state:当前事务的状态

 

参考文献:

 [1] Baron Schwartz等 著,宁海元等 译 ;《高性能MySQL》(第3版);
电子工业出版社 ,2013

 [2] 简书博客,

 [3]CSDN博客,

 [4]
CSDN博客,

 [5] CSDN博客,

 [6] CSDN博客,

 [7]
CSDN博客,

 [8]
官网文档,

在InnoDB加锁前,为什么要先start
transaction
innodb下锁的释放在事务提交/回滚之后,事务一旦提交/回滚之后,就会自动释放事务…

加共享锁:select *
from xx where ,….. lock in share mode

 

意向排它锁(IX):

  通知数据库接下来需要施加什么锁并对表加锁。如果需要对记录A加排他锁,那么此时innodb会先找到这张表,对该表加意向排他锁之后,再对记录A添加共享锁。也就是说一个数据行加排它锁前必须先取得该表的IX锁

访问不同的记录不会发生等待 由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然时访问不同的行记录。但是如果是使用相同的索引键,会出现锁冲突的,应用设计的时候要注意

 

 总结:

  对于InnoDB表,主要有以下几点  
  (1)InnoDB的行销是基于索引实现的,如果不通过索引访问数据,InnoDB会使用表锁。
    (2)InnoDB间隙锁机制,以及InnoDB使用间隙锁的原因。  
  (3)在不同的隔离级别下,InnoDB的锁机制和一致性读策略不同。  
  (4)MySQL的恢复和复制对InnoDB锁机制和一致性读策略也有较大影响。  
  (5)锁冲突甚至死锁很难完全避免。        
在了解InnoDB的锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁,包括:

  • 尽量使用较低的隔离级别
  • 精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。
  • 选择合理的事务大小,小事务发生锁冲突的几率也更小。
  • 给记录集显示加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。
  • 不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
  • 尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
  • 不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁。
  • 对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能。

2) 
由于MySQL的行锁针对索引加锁,不是针对记录加的锁,所以虽然时访问不同行的记录,但是如果是使用相同的索引键,则会出现锁冲突

 

何时在InnoDB中使用表锁:

  InnoDB在绝大部分情况会使用行级锁,因为事务和行锁往往是我们选择InnoDB的原因,但是有些情况下我们也考虑使用表级锁

  • 当事务需要更新大部分数据时,表又比较大,如果使用默认的行锁,不仅效率低,而且还容易造成其他事务长时间等待和锁冲突。
  • 事务比较复杂,很可能引起死锁导致回滚。

Blocking_trx_id:阻塞锁的ID

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

Gap Lock和Next-key Lock的区别:

  Next-Key
Lock是行锁与间隙锁的组合,这样,当InnoDB扫描索引记录的时候,会首先对选中的索引记录加上行锁(Record
Lock),再对索引记录两边的间隙加上间隙锁(Gap
Lock)。如果一个间隙被事务T1加了锁,其它事务是不能在这个间隙插入记录的。

  行锁防止别的事务修改或删除,Gap锁防止别的事务新增,行锁和GAP锁结合形成的Next-Key锁共同解决了RR界别在写数据时的幻读问题。

LOCK_PAGE:被锁住的页的数量,若是表锁,则该值为null

 

  共享锁和意向共享锁,排他锁与意向排他锁的区别:

  • 共享锁和排他锁,系统在特定的条件下会自动添加共享锁或者排他锁,也可以手动添加共享锁或者排他锁。
  • 意向共享锁和意向排他锁都是系统自动添加和自动释放的,整个过程无需人工干预。
  • 共享锁和排他锁都是锁的行记录,意向共享锁和意向排他锁锁定的是表。

意向共享锁(IS)事务打算给数据行加共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁

innodb锁模式互斥

在InnoDB下 ,使用表锁要注意以下两点。

    (1)使用LOCK
TALBES虽然可以给InnoDB加表级锁,但必须说明的是,表锁不是由InnoDB存储引擎层管理的,而是由其上一层MySQL
Server负责的,仅当autocommit=0、innodb_table_lock=1(默认设置)时,InnoDB层才能知道MySQL加的表锁,MySQL
Server才能感知InnoDB加的行锁,这种情况下,InnoDB才能自动识别涉及表级锁的死锁;否则,InnoDB将无法自动检测并处理这种死锁。
    (2)在用LOCAK
TABLES对InnoDB锁时要注意,要将AUTOCOMMIT设为0,否则MySQL不会给表加锁;事务结束前,不要用UNLOCAK
TABLES释放表锁,因为UNLOCK
TABLES会隐含地提交事务;COMMIT或ROLLBACK不能释放用LOCAK
TABLES加的表级锁,必须用UNLOCK TABLES释放表锁,正确的方式见如下:
  例如:如果需要写表t1并从表t读   

SET AUTOCOMMIT=0;
LOCAK TABLES t1 WRITE, t2 READ, ...;
[do something with tables t1 and here];
COMMIT;
UNLOCK TABLES;

Trx_query:事务运行的SQL语句


表锁:

  InnoDB还有两个表锁:意向共享锁(IS),意向排它锁(IX)

注意:

图片 4.png)

mysql-innoDB-锁,

意向独占锁(IX)事务打算给数据行加排它锁,事务在给一个数据行加排它锁前必须先取得该表的IX锁


乐观锁:

  乐观锁,也叫乐观并发控制,它假设多用户并发的事务在处理时不会彼此互相影响,各事务能够在不产生锁的情况下处理各自影响的那部分数据。在提交数据更新之前,每个事务会先检查在该事务读取数据后,有没有其他事务又修改了该数据。如果其他事务有更新的话,那么当前正在提交的事务会进行回滚。

Innodb
行级别的锁基于索引实现的支持并发和一致性

innodb的gap lock 间隙锁

INNODB的几种锁

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

不同隔离级别,和不同索引类型的加锁处理分析

 

图片 5

结论:

RR  
2.innodb_locks_unsafe_for_binlog=0

1.任何辅助索引上的锁,或者非索引列上的锁,最终都要回溯到主键上,在主键上也要加一把锁

2.任何叶子节点上的S或X锁之前,都会在根节点上加一个IS或IX锁,也就是表级别的IS,IX锁

3.主键索引=record
lock(但外键约束,唯一性约束检测仍然使用 gap lock)

4.唯一辅助索引=record
lock(但外键约束,唯一性约束检测仍然使用 gap lock)

5.非唯一辅助索引=next-key-lock(RC隔离级别=record
lock)

Recordlock:单个记录上的锁,至少锁定一行记录;

Gap
lock(间隙锁)
:在索引记录间隙上的锁,或者是第一条索引记录之前,最后一条索引记录之后上的间隙锁(两条记录中间的缝隙) 锁定两个记录中间的缝隙;

Next-keylock(下一键锁)索引记录锁以及索引记录之间的间隙锁,二者的组合锁;

记录锁至少锁定一条记录(普通,主键,唯一 索引)或是无任何索引innodb会对rowid加锁(左右两边加自身的记录);

设置RC隔离级别或者是启用innodb_lock_unsafe_for_binlog的其他影响

1.在mysql评估完where条件后,会释放找不到相应记录的记录锁

2.在update语句中,innodb使用“半一致性读“,会返回提交后的最新版本号,以便判是否匹配update语句中的where条件

gap lock避免幻读:

假设一个SQL:select * from child where
id>=100 for update;

Id字段当前有2个值:90,102
这时候gap是90—-102之间,如果只有recode lock 就没办法再阻止101这个id
(就会发生幻读再次读取后可以看到101这个id值);

有了next-key
lock后,可以阻止写入101这个id确保两次读取的结果是一样的,不会发生幻读;

有唯一属性索引时,就无需使用gap
lock(扫描包含多个字段的唯一索引中的部分字段除外);

还有一种叫做意向插入(insertionintention)的gap
lock,如果两个事务往同一个gap
lock中写入数据,但写入位置不一样时,是无须等待,可以直接写入因此没有冲突

设定pkid =3

T1:insert into
t(pkid)values(4)

T2:insert into t (pkid)
values(5)

Gap
lock仅用于防止往gap上写入新记录(避免幻读),因此无论是S-GAP
还是X-GAP锁其实作用是一样的。

Innode引擎监控的开启的方法
  

锁监控:
打开innodb的锁监控:
CREATE TABLE innodb_lock_monitor (a
INT) ENGINE=INNODB;    
5.6.16可以使用:
 –两个都需要打开
set GLOBAL
innodb_status_output=ON;
set GLOBAL
innodb_status_output_locks=ON; 
表空间监控:  
打开innodb表空间监控:
CREATE TABLE innodb_tablespace_monitor
(a INT) ENGINE=INNODB;
表监控:
打开innodb表监控:
CREATE TABLE innodb_table_monitor (a
INT) ENGINE=INNODB;
打开监视器以后
innodb_monitor和innodb_lock_monitor会每隔15秒会向错误日志中记录InnoDB监控信息;
innodb_table_monitor和innodb_tablespace_monitor是每隔64秒;
innodb_monitor和innodb_lock_monitor两种监视器的输出结果基本类似,后者会有更多关于锁的信息,而前一个实际上就是show
innodb status;
innodb_table_monitor会将系统中所有innodb的表的一些结构和内部信息输出;
innodb_tablespace_monitor输出的是tablespace的信息,注意该monitor输出的只是共享表空间的信息,如果使用innodb_file_per_table为每个表使用独立的表空间,则这些表空间的信息是不会包含在输出中的。
停止InnoDB监控
drop table innodb_monitor;
drop table
innodb_lock_monitor;
drop table
innodb_table_monitor;
drop table
innodb_tablespace_monitor;
重点的锁类型
如果辅助索引上的搜索及锁定是排它的,则会取回其相应的聚集索引,并且在它上面加锁;
对无索引的字段检索更新时升级成表级别锁(表中全部记录被锁,除非在RC或innodb_locks_unsafe_for_binlog=1
模式下 采用semi-consitent read机制);
insert into T select … from S where
T表上排它record lock
事务隔离级别为RC或者启用innodb_locks_unsafe_for_binlog并且隔离级别不是serializable时,S表上采用无锁一致性读,否则(rr),加排它next-key
lock(RC不加锁。RR加next-key lock);
insert 排它record lock,而非next-key
lock,但在写入新记录之前需要加意向gap lock(insertion intention gap
lock);
insert…on duplicate key update
排它next-key lock(即使被update的记录上)会同时并发执行;
create table…select 和insert…select
一样;
replace 没冲突/重复时 和insert一样
否则(有冲突时先delete后insert)加next-key-lock;
replace into t select … from S where
或者update T … where col IN(SELECT…FROM S..),都会在S表上加next-key
lock;
auto..increment列上写新数据时,索引末尾设置排它锁,请求自增列计数器时,INNODB使用一个AUTO-INC表锁,只对请求的那个SQL有影响,不会影响整个事务,该锁被持有时,其他会话不能往INNODB表中写入新行;
select…from
一致性非锁定读除非是serializable隔离级别,在其影响的索引记录上设置一个共享锁(简单的select…from是不加锁的);
lock in shared mode,使用共享next-key
lock;
for update使用排它next-key
lock锁,会阻止lock in shared mode请求;
update/delete,排它next-key lock.
死锁 
死锁不会卡,有一个会立刻回滚,再次提交即可,show
engine innodb status
只显示最后死锁的信息,设置innodb_print_all_deadlocks=1,在日志中记录全部死锁信息;
自动检测死锁,并优先回滚最小事务(影响较小的事务),加表锁时,不会发生死锁;
事务中如果select调用存储函数/存储过程失败了,对用的SQL会回滚事务,如果再显示执行ROLLBACK,那么整个事务都回滚;
事务回滚时,会释放全部的锁,个别情况下,如果个别SQL因为某些错误回滚事务的话它所持有的行锁可能无法释放,因为INNODB的行锁信息并没有记录时那个SQL持有的,这时候建议执行一次显示的ROLL
BACK。
避免死锁
事务尽快提交,小事务越不容易发生死锁;
加for update lock in shared
mode读锁时最好降低事务隔离级别,例如用RC级别降低死锁发生概率;
事务中涉及多个表,或者涉及多行记录时,每个事务的操作顺序都要保持一致,降低死锁发生概率,最好用存储过程/存储函数固化;
通过索引等方式优化SQL效率,降低死锁发生概率,减少扫描/锁范围,降低概率。

 

 

 

 

 

锁升级

哪个事务被哪个事务阻塞很明显通过该innodb_lock_waits看

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

LOCK_MODE:锁的模式

 

通过select* from
information_schema.INNODB_LOCK可查看

 

Lock_id:锁的ID

死锁数据库自动解决

 

X

IX

S

IS

X

冲突

冲突

冲突

冲突

IX

冲突

兼容

冲突

兼容

S

冲突

冲突

兼容

兼容

IS

冲突

兼容

兼容

兼容

 

可以通过show full
processlist
,show engine innodb status等命令查看锁状态

图片 6.png)

innodb_locks

 总结

Trx_wait_started:事务开始等待时间

 

LOCK_TABLE:要加锁的表

死锁

innodb_trx

 

Trx_id:innodb存储引擎内部唯一事务ID

行锁升级成表锁:

排它锁(X-LOCKING)允许获得排它锁的事务更新数据,阻止其它事务取得相同数据集的共享读锁和排它锁

gap lock消灭幻读

Request_lock_id:申请锁的ID

例子:

Trx_started:事务开始时间

 

INNODB还独有实现了2种锁

 

Trx_mysql_thread_id
MySQL中的线程ID show processlist 显示结果

表锁:Myisam ,memory

1) 
在不通过索引条件查询的时候,innodb使用的是表锁

事务锁粒度

Lock_trx_id:事务ID

 图片 7

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

注意

 

 

Author

发表评论

电子邮件地址不会被公开。 必填项已用*标注