|成功不是将来才有的,而是从决定去做的那一刻起,持续累积而成。
一、InnoDB锁
InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。行级锁与表级锁本来就有许多不同之处,另外,事务的引入也带来了一些新问题。下面我们先介绍一点背景知识,然后详细讨论InnoDB的锁问题。
1.事务(Transaction)及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。
l 原子性(
Atomicity
):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
l 一致性(
Consistent
):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
l 隔离性(
Isolation
):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
l 持久性(
Durable
):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
相对于串行处理来说,
并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持更多的用户
。但并发事务处理也会带来一些问题,主要包括以下几种情况。
l 更新丢失(Lost Update):
当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题--最后的更新覆盖了由其他事务所做的更新。例如,两个编辑人员制作了同一文档的电子副本。每个编辑人员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改。如果在一个编辑人员完成并提交事务之前,另一个编辑人员不能访问同一文件,则可避免此问题。
l 脏读(Dirty Reads):一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做"脏读"。
l 不可重复读(Non-Repeatable Reads):一个事务在读取某些数据后的某个时间,再次读取以前读过的数据,却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。
l 幻读(Phantom Reads):一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。
四、InnoDB引擎表锁的几种类型
● Record Lock:锁定单条记录
● Gap Lock:锁定一个范围的记录、但不包括记录本身
● Next-Key Lock:锁定一个范围的记录、并且包含记录本身、这是默认的锁类型
● Gap Lock:锁定一个范围的记录、但不包括记录本身
● Next-Key Lock:锁定一个范围的记录、并且包含记录本身、这是默认的锁类型
● select ... lock in share mode:加 S 锁
● select ... for update:加 X 锁
在实际应用当中,最长使用的是行锁,那么什么是行锁呢?行锁的实现原理是什么呢?
MySQL的行锁是针对索引加的锁,不是针对记录加的锁,如果锁定没有索引的记录,是锁定全表(行锁)。做个试验验证一下:
操作
|
session_1
|
session_2
|
结论
|
在一个session设置行锁
|
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1;
+----+----------+
| id | name |
+----+----------+
| 1 | body1 |
| 2 | body2 |
| 3 | body3 |
| 4 | bodytest |
| 5 | body5 |
| 6 | body6 |
| 7 | body7 |
| 8 | body8aa |
| 9 | body9 |
| 10 | body10 |
| 11 | body11 |
| 12 | body12 |
| 13 | body13 |
| 18 | body18 |
| 19 | body19 |
+----+----------+
15 rows in set (0.00 sec)
mysql> select * from test1 where id=2 for update;
+----+-------+
| id | name |
+----+-------+
| 2 | body2 |
+----+-------+
1 row in set (0.00 sec)
|
mysql> update test1 set name = 'body2aaaa' where id=1;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql>
|
session_1中设置行锁,在session_2中去更新数据,是无法更新的,并且报出
Lock wait timeout exceeded错误(此时并没有为该表添加任何索引)
|
commit后 |
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
|
mysql> update test1 set name = 'body2aaaa' where id=2;
Query OK, 1 row affected (7.37 sec)
Rows matched: 1 Changed: 1 Warnings: 0
|
session_1中commit后解除了表锁,session_2中更新数据操作自动运行成功
|
为test1表创建索引
|
mysql>
alter table test1 add index idx_id(id);
Query OK, 0 rows affected (0.26 sec)
Records: 0 Duplicates: 0 Warnings: 0
|
|
|
索引创建成功以后,在加行锁
|
mysql> select * from test1 where id = 2 for update;
+----+-----------+
| id | name |
+----+-----------+
| 2 | body2aaaa |
+----+-----------+
1 row in set (0.00 sec)
|
mysql> update test1 set name = 'body1aaaa' where id=1;
Query OK, 1 row affected (0.05 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> update test1 set name = 'body2bbbb' where id=2;
一直处于等待状态
|
添加索引后,我们去锁定id=2的记录,在另外一个session中去更新id=1的数据是可以成功的说明行锁创建成功。
|
|
|
|
|
结论:1、当我们使用行锁的时候,如果没有用到索引,那么就会造成表锁。
2、若想使用行锁必须用到索引。
3、当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁
五、获取InnoDB行锁争用情况
mysql> alter table test1 engine=innodb;
获取InnoDB行锁争用情况,可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情
况:
Query OK, 15 rows affected (16.94 sec)
Records: 15 Duplicates: 0 Warnings: 0
mysql> show create table test1;
+-------+----------------------------------------------------------------------
| Table | Create Table
+-------+----------------------------------------------------------------------
| test1 | CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(32) NOT NULL DEFAULT '' COMMENT '名称',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=20 DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------
1 row in set (0.00 sec)
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_current_waits
|
当前等待的待锁定的行数
|
Innodb_row_lock_time
|
系统启动到现在、锁定的总时间长度
|
Innodb_row_lock_time_avg
|
每次平均锁定的时间
|
Innodb_row_lock_time_max
|
最长一次锁定时间
|
Innodb_row_lock_waits
|
系统启动到现在、总共锁定次数
|
六、实际问题分析
1、如果发生了表锁,如何解决?
①
show processlist;查看当前是否有表锁,造成表锁的进程id是多少。
②kill id;杀掉造成表锁的进程id
③查找造成表锁进程的程序代码,修改代码
2、怎么查看当前系统是否存在表锁?
mysql> SHOW PROCESSLIST;
+----+------+-----------------+------+---------+------+-------+-----------------
| Id | User | Host | db | Command | Time | State | Info
+----+------+-----------------+------+---------+------+-------+-----------------
| 1 | root | localhost:57346 | test | Query | 0 | NULL | SHOW PROCESSLIST
| 5 | root | localhost:59864 | test | Sleep | 878 | | NULL
+----+------+-----------------+------+---------+------+-------+-----------------
2 rows in set (0.00 sec)
3、用到了索引,那么行锁一定就会成功吗?什么情况下不成功?
答案是否定的。以下例子就是一种用到索引却产生了表锁的示例:
操作
|
session_1
|
session_2
|
结论
|
查询两个表中的数据
|
mysql> select * from test1 order by id asc;
+----+-----------+
| id | name |
+----+-----------+
| 1 | body1aaaa |
| 2 | body2aaaa |
| 3 | body3 |
| 4 | bodytest |
| 5 | body |
| 6 | body |
| 7 | body |
| 8 | body |
| 9 | body9 |
| 10 | body10 |
| 11 | body11 |
| 12 | body12 |
| 13 | body13 |
| 18 | body18 |
| 19 | body19 |
+----+-----------+
15 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
|
mysql> select * from test1 order by id asc;
+----+-----------+
| id | name |
+----+-----------+
| 1 | body1aaaa |
| 2 | body2aaaa |
| 3 | body3 |
| 4 | bodytest |
| 5 | body |
| 6 | body |
| 7 | body |
| 8 | body |
| 9 | body9 |
| 10 | body10 |
| 11 | body11 |
| 12 | body12 |
| 13 | body13 |
| 18 | body18 |
| 19 | body19 |
+----+-----------+
15 rows in set (0.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.04 sec)
|
结果一致
|
给字段name添加索引,以name为条件进行查询
|
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1 where name='body' for update;
+----+------+
| id | name |
+----+------+
| 5 | body |
| 6 | body |
| 7 | body |
| 8 | body |
+----+------+
4 rows in set (0.00 sec)
|
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1 where name='body1' for update;
+----+-------+
| id | name |
+----+-------+
| 10 | body1 |
| 11 | body1 |
| 12 | body1 |
| 13 | body1 |
+----+-------+
4 rows in set (0.00 sec)
|
都可读,
好了,到这里什么问题都没有,是吧,可是接下来问题就来了,大家请看:回到第一个 session ,运行:
|
|
mysql> update test1 set name = 'bodyaaaa' where name='body';
Query OK, 4 rows affected (0.00 sec)
Rows matched: 4 Changed: 4 Warnings: 0
|
|
可以运行,那这样不是应该是正确的吗?不就是应该这样吗?好,接下来我们改变一下数据
|
|
mysql> select * from test1;
+----+-------+
| id | name |
+----+-------+
| 1 | body |
| 2 | body |
| 3 | body |
| 4 | body |
| 5 | body |
| 6 | body |
| 7 | body |
| 8 | body |
| 9 | body1 |
| 10 | body1 |
| 11 | body1 |
| 12 | body1 |
| 13 | body1 |
| 18 | body1 |
| 19 | body1 |
+----+-------+
15 rows in set (0.00 sec)
|
|
更改数据内容如session_1所示
|
同时查询两个session中的数据
|
mysql> begin;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from test1 where name='body' for update;
+----+------+
| id | name |
+----+------+
| 1 | body |
| 2 | body |
| 3 | body |
| 4 | body |
| 5 | body |
| 6 | body |
| 7 | body |
| 8 | body |
+----+------+
8 rows in set (0.00 sec)
|
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from test1 where name = 'body1' for update;
+----+-------+
| id | name |
+----+-------+
| 9 | body1 |
| 10 | body1 |
| 11 | body1 |
| 12 | body1 |
| 13 | body1 |
| 18 | body1 |
| 19 | body1 |
+----+-------+
7 rows in set (0.00 sec)
|
现在看不出来什么问题,感觉是正常的。
|
再去更新数据
|
mysql> select * from test1 where name='body' for update;
+----+------+
| id | name |
+----+------+
| 1 | body |
| 2 | body |
| 3 | body |
| 4 | body |
| 5 | body |
| 6 | body |
| 7 | body |
| 8 | body |
+----+------+
8 rows in set (0.00 sec)
mysql> update test1 set name='bodyaaa' where name='body';
执行等待中。。。。。
|
|
看,问题出现了,这跟上面的操作不应该是一样的吗?为什么这一次不能执行成功了呢?
|
总结:
由于 name字段重复率太高,只有 2 个值,分别是 body 和 body1。而数据量相对于这两个值来说却是比较大的,是 15 条,8 倍的关系。
那么 mysql 在解释 sql 的时候,会忽略索引,因为它的优化器发现:即使使用了索引,还是要做全表扫描,故而放弃了索引,也就没有使用行锁,却使用了表锁。简单的讲,就是 MYSQL 无视了你的索引,它觉得与其行锁,还不如直接表锁,毕竟它觉得表锁所花的代价比行锁来的小。以上问题即便你使用了 force index 强制索引,结果还是一样,永远都是表锁。
所以, mysql 的行锁用起来并不是那么随心所欲的,必须要考虑索引。再看下面的例子:
1、select id from items where id in (select id from items where id <6) for update;
--id字段加了索引
2、
select id from items where id in (1,2,3,4,5) for update;
大部分会认为结果一样没什么区别,其实差别大了,区别就是
第一条 sql 语句会产生表锁
,而
第二个 sql 语句是行锁
,为什么呢?因为第一个 sql 语句
用了子查询外围查询,故而没使用索引
,导致表锁。