你的位置:首页 > 数据库

[数据库]innodb事务锁


计算机程序锁

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

 



lock  主要是事务,数据库逻辑内容,事务过程
latch/mutex 内存底层锁;
 
更新丢失

原因:
B的更改还没有提交时,A已经再次修改了数据。
此时A使用原来的元数据作为基础更新后,B的更新便会丢失;


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

 

 


 
事务锁粒度

 
行锁: innodb ,oracle
页锁:sql server
表锁:Myisam ,memory
 
获取innodb行锁争用情况
 
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)


如果发现锁争用比较严重,如innodb_row_lock_waits 和 innodb_row_lock_time_avg的值比较高,
还可以通过设置innodb monitor 来进一步观察发生锁冲突的表,数据行等,并分析锁争用的原因:
 
 
innodb锁模式与粒度

 
四种基本锁模式
  • 共享锁(S)-读锁-行锁
  • 排他锁(X)-写锁-行锁
  • 意向共享锁(IS)-表级 :事务想要获得一张表中某几行的共享锁
  • 意向排他锁(IX)-表级:事务想要获得一张表中某几行的排他锁
 
意向锁,简单来说就是:
如需要对页上的记录R进行X锁,那么分别需要对该记录所在的数据库,表,页,上意向锁IX,最后对记录R上X锁。
若其中任何一个部分导致等待,那么该操作需要等待粗粒度锁的完成。
 
innodb支持意向锁设计比较简练,其意向锁即为表级别的锁。设计目的主要是为了在一个事务中揭示下一行将被请求的锁类型。
 
意向锁:
  • 意向锁总是自动先加,并且意向锁自动加自动释放
  • 意向锁提示数据库这个session将要在接下来将要施加何种锁
  • 意向锁和X/S 锁级别不同,除了阻塞全表级别的X/S锁外其他任何锁 
自动施加,自动释放,
 
 
innodb锁模式互斥

 


数据库加锁操作
 
一般的select语句不加任何锁,也不会被任何事物锁阻塞
读的隔离性由MVCC确保
 
undo log 用来帮助事务回滚及MVCC(多版本并发控制 ,即select时可以使用行数据的快照,而不用等待锁资源)
 
S锁
  手动:select * from tb_test lock in share mode;
  自动:insert前
 
X锁
   手动:
select * from tb_test  for update;

   自动:update,delete 前


 
线上环境中:


 
锁等待时间:innodb_lock_wait_timeout
 
mysql>show global variables like "%wait%"


 
innodb 行锁

 
通过索引项加锁实现
  • 只有条件走索引才能实现行级锁                    a)
  • 索引上有重复值,可能锁住多个记录              b)
  • 查询有多个索引可以走,可以对不同索引加锁   c)
  • 是否对索引加锁实际上取决于Mysql执行计划
 
自增主键做条件更新,性能做好;
 
通过索引项加锁实现的例子:
a) 只有,有条件走索引才能实现行级锁
 
mysql> show create table t2\G;*************************** 1. row ***************************    Table: t2Create 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


说明,表中没有索引时,innodb将对整个表加锁,而不能体现行锁的特性;
 
 
 b)  索引上有重复值,可能锁住多个记录 
 
mysql> show create table t2\G;*************************** 1. row ***************************    Table: t2Create Table: CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, KEY `a` (`a`)) ENGINE=InnoDB DEFAULT CHARSET=latin11 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 |+------+------+


注意
行锁升级成表锁:
mysql> select * from t2 where b =9 for update ;


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

 
c)  查询有多个索引可以走,可以对不同索引加锁
 
mysql> show create table t2\G;*************************** 1. row ***************************    Table: t2Create Table: CREATE TABLE `t2` ( `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, KEY `a` (`a`), KEY `b` (`b`)) ENGINE=InnoDB DEFAULT CHARSET=latin1mysql> 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 |+------+------+

 


innodb的gap lock 间隙锁
 
gap lock消灭幻读
     innodb消灭幻读仅仅为了确保 statement模式replicate的主从一致性
 
小心gap lock
 
自增主键做条件更新,性能最好;
 
gap lock 间隙锁 解释:
 
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)


 
此时隔离等级是Repeatable  Read,标准的是可以出现幻读现象的,
即在B连接中 insert into t2 values(27,3),是可以插入成功的,而且B连接提交后,A连接是可以查看到增加的,27,3这一行的。
 
而innodb 通过间隙锁是的B连接中  insert into t2 values(27,3) 插入失败,来消灭幻读的出现。
但是这种方法是有局限的,它会将a=24--29(30-1)中间的任何数都锁住,所以才叫间隙锁;
 
B连接中则只能插入不在这个区间的数据;
 
锁升级

 
  • 由一句单独的sql语句在一个对象上持有的锁的数量超过了阈值,默认这个阈值为5000.值得注意的是,如果是不同对象,则不会发生锁升级。
  • 锁资源占用的内存超过了激活内存的40%时就会发生锁升级
 
innodb不存在锁升级的问题。因为其不是根据每个记录来产生行锁的,相反,其根据每个事务访问的每个页对锁进行管理的,采用的是位图的方式。因此不管一个事务锁住页中一个记录还是多个记录,其开销通常都是一致的。
 
简单说innodb根据页进行加锁,并采用位图方式,定位到行的,所需资源较小。

例子:
 

 



死锁

 

 



 
死锁数据库自动解决
     数据库挑选冲突事务中回滚代价较小的事务回滚
 
死锁预防
     单表死锁可以根据批量更新表的更新条件排序
     可能冲突的跨表事务尽量避免并发
     尽量缩短事务长度
 
排查死锁:
  • 了解触发死锁的sql所在事务的上下文
  • 根据上下文语句加锁的范围来分析存在争用的记录
  • 通常改善死锁的主要方法:
        --对同一表的操作根据加锁条件进行排序

        --拆分长事务
 
业务逻辑加锁

 
     业务流程中的悲观锁(开始的时候,在所有记录加锁,直到最后释放;而乐观锁开始不加锁,只是在最后提交中看提交有没有成功,没成功返回给应用程序)
 
     悲观锁开始就给所有记录加锁,一般等所有业务流程完成,才释放锁;因此会对并发性能有一定的影响;
 
如何缩短锁的时间?
1)开始的时候读取要修改的数据,amount(金额)
2)做业务流程
3)在update时,加锁且判断,现在的amount和开始的amount是否为一个值,如果是,说明这期间amount为改变,则更新;如果amount值改了,则不更新,交给业务来判断该怎么做。
 
这样仅是在update这个语句加锁,大大的缩短的锁的时间提高了并发性;
 
但是如果业务十分的繁忙,amount的值在不断改变,此时这个update 就不断的失败,整个事务就不断的失败,反而影响了 性能。那么该如何做呢?
 
在开始的时候不读取数据,等到要提交的时候读取并加锁提交;
 
 总结


 
  •  更新丢失
  •  innodb意向锁:
    • 表锁
    • 自动施加、自动释放
    • 为了揭示事务下一行将被请求的锁类型
  •  S锁:in share mode
  •  X锁:for update
  •  innodb行锁特点:
    • 只有条件走索引才能实现行锁
    • 索引上有重复值可能锁住多个记录
    • 查询有多个索引可以走,可以对不同索引加锁
  •  gap lock:间隙锁,消灭幻读
  •  死锁解决:数据库挑回滚代价较小的事务回滚;
  •  死锁预防:
    • 单表,更新条件排序
    • 避免跨表事务,缩短事务长度
  •  锁升级:
    • 单独sql语句在单个对象的锁数量超过阙值
    • 锁资源占用的内存超过了激活内存的40%;
  •  innodb根据页进行加锁,并采用位图方式,定位到行的,所需资源较小