首页 > > 网络编程 > Mysql >

innodb事务锁

2018-06-17 22:50:06来源：未知阅读 ()

计算机程序锁

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

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: 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

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

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

mysql> show create table t2\G;
*************************** 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 |
+------+------+

注意

行锁升级成表锁：

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: 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 |
+------+------+

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)