Mysql性能优化-4.锁与事务

1.锁的作用

• 避免资源争用的机制功能。
• 资源争用：多个任务同时使用一个资源，对该资源产生争用。
• 数据库中，记录（数据）就是资源，不同的客户端对记录CRUD操作就是任务。
• 思路，就是在某个任务使用资源时，标识出来，其他任务就不能同时操作，等待或者放弃了。

2.流程

先尝试加锁，如果锁定成功，去使用资源。否则等待或者放弃！
加锁 》锁状态 》操作资源 》释放锁

3.锁的类型

• 共享锁，读锁，S-lock。是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。如果事务A对数据A加上了共享锁，其他事务只能对A再加共享锁，不能加排他锁，获得共享锁的事务只能读数据，不能修改数据。
• 在查询语句后面增加LOCK IN SHARE MODE，Mysql会对查询结束中的每行都加共享锁，当没有其它线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。
• 排他锁，独占锁，写锁，X-lock。如果事务A对数据A加上排他锁后，则其他事务不能再对A加任何类型的锁，其他事务也不能对A做update，insert，delete操作，因为在innodb中这些操作默认加了排他锁，可以进行select操作因为查询的时候是不加任何锁的。
• 在查询语句后面增加FOR UPDATE，Mysql会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。
• Mysql 在执行任何的SQL时，都会自动增加锁定。通常不需要认为操作锁。

其他事务操作	共享锁	排他锁
select(默认不加锁)	能	能
update	不能	不能
insert	不能	不能
delete	不能	不能
加共享锁	能	不能
加排他锁	不能	不能

4.锁粒度（范围）

在MySQL中，锁被实现了不同的粒度。
一旦加锁，锁定的记录数量不同。

• 表级锁定，table-level：操作会锁定整张表，无论是共享锁还是独占锁。
• 行级锁定，row-level：操作会锁定该操作的记录。

MySQL的Innodb同时实现了表级和行级

5.MySQL中锁的相关语法

锁

• 表锁，myisam、innodb都是表锁。

• 加锁：lock tables table-name1,table-name2 READ|WRITE;

• 解锁：unlock tables；

• 对于Innodb，支持行锁。
  对查询的记录增加共享锁：select * from table where id < 10 lock in share mode;
  对查询的记录增加排他锁：select * from table where id < 10 for update;

mysql> lock tables innodb1 read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb1;
+----+--------+
| id | title  |
+----+--------+
| 11 | 令狐冲 |
| 16 | 成昆   |
| 23 | 张无忌 |
| 27 | 风清扬 |
| 28 | 风清扬 |
| 29 | 李莫愁 |
| 30 | 张无忌 |
| 31 | 风清扬 |
| 35 | 胡佩   |
| 36 | 胡佩   |
+----+--------+
10 rows in set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> lock tables innodb1 write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb1;
+----+--------+
| id | title  |
+----+--------+
| 11 | 令狐冲 |
| 16 | 周杰伦 |
| 23 | 张无忌 |
| 27 | 风清扬 |
| 28 | 风清扬 |
| 29 | 李莫愁 |
| 30 | 张无忌 |
| 31 | 风清扬 |
| 35 | 胡佩   |
| 36 | 胡佩   |
+----+--------+
10 rows in set (0.00 sec)

mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb1 where id < 25 lock in share mode;
+----+--------+
| id | title  |
+----+--------+
| 11 | 令狐冲 |
| 16 | 周杰伦 |
| 23 | 张无忌 |
+----+--------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb1 where id < 25 for update;
+----+--------+
| id | title  |
+----+--------+
| 11 | 令狐冲 |
| 16 | 成昆   |
| 23 | 张无忌 |
+----+--------+
3 rows in set (0.00 sec)

mysql> update innodb1 set title = '任我行' where id = 16;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

6.事务

• 开启事务 start transaction;或者 begin；
• 回滚事务 rollback；
• 提交事务 commit；

隔离级别	脏读(Dirty Read)	不可重复读(NonRepeatable Read)	幻读(Phantom Read)
未提交读(Read Uncommitted)	可能	可能	可能
已提交读(Read Committed)	不可能	可能	可能
可重复读(Repeatable Read)	不可能	不可能	可能
可串行化(Serializable)	不可能	不可能	不可能

• 未提交读(Read Uncommitted)：允许脏读，也就是可能读取到其他会话中未提交事务修改的数据
• 提交读(Read Committed)：只能读取到已经提交的数据。Oracle等多数数据库默认都是该级别 (不重复读)
• 可重复读(Repeated Read)：可重复读。在同一个事务内的查询都是事务开始时刻一致的，InnoDB默认级别。在SQL标准中，该隔离级别消除了不可重复读，但是还存在幻象读
• 串行读(Serializable)：完全串行化的读，每次读都需要获得表级共享锁，读写相互都会阻塞

InnoDB使用不同的锁策略(Locking Strategy)来实现不同的隔离级别。

一，读未提交(Read Uncommitted)
这种事务隔离级别下，select语句不加锁。
画外音：官方的说法是
SELECT statements are performed in a nonlocking fashion.
此时，可能读取到不一致的数据，即“读脏”。这是并发最高，一致性最差的隔离级别。

二，串行化(Serializable)
这种事务的隔离级别下，所有select语句都会被隐式的转化为select ... in share mode.
这可能导致，如果有未提交的事务正在修改某些行，所有读取这些行的select都会被阻塞住。
画外音：官方的说法是
To force a plain SELECT to block if other transactions have modified the 
selected rows.
这是一致性最好的，但并发性最差的隔离级别。
在互联网大数据量，高并发量的场景下，几乎不会使用上述两种隔离级别。

三，可重复读(Repeated Read, RR)
这是InnoDB默认的隔离级别，在RR下：
(1)普通的select使用快照读(snapshot read)，这是一种不加锁的一致性读(Consistent
 Nonlocking Read)，底层使用MVCC来实现，具体的原理在《InnoDB并发如此高，原因竟然在这？》
 中有详细的描述；
(2)加锁的select(select ... in share mode / select ... for update), update,
delete等语句，它们的锁，依赖于它们是否在唯一索引(unique index)上使用了唯一的查询条件(
unique searchcondition)，或者范围查询条件(range-type search condition)：

在唯一索引上使用唯一的查询条件，会使用记录锁(record lock)
，而不会封锁记录之间的间隔，即不会使用间隙锁(gap lock)与临键锁(next-key lock)


范围查询条件，会使用间隙锁与临键锁，锁住索引记录之间的范围，避免范围间插入记录，以避免产生
幻影行记录，以及避免不可重复的读


画外音：这一段有点绕，多读几遍。
关于记录锁，间隙锁，临键锁的更多说明，详见《InnoDB，select为啥会阻塞insert？》。


四，读提交(Read Committed, RC)
这是互联网最常用的隔离级别，在RC下：
(1)普通读是快照读
(2)加锁的select, update, delete等语句，除了在外键约束检查(foreign-key constraint 
checking)以及重复键检查(duplicate-key checking)时会封锁区间，其他时刻都只使用记录锁；
此时，其他事务的插入依然可以执行，就可能导致，读取到幻影记录。

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set, 1 warning (0.00 sec)

mysql> set tx_isolation='read-uncommitted';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set tx_isolation='read-committed';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)

脏读：事务a未提交，事务b就能读到a更改的数据

A	B
begin	select * from innodb1 where id =16; {id:16,title:任我行}
update innodb1 set title = '独孤求败' where id = 16;	select * from innodb1 where id =16; {id:16,title:独孤求败}
commit;

不可重复读：事务a未提交，事务b读的未更改数据，a提交，事务b读的a的更改数据。

A	B
begin	begin
	select * from innodb1 where id =16; {id:16,title:独孤求败}
update innodb1 set title = '任我行' where id = 16;
commit;
	select * from innodb1 where id =16; {id:16,title:任我行}
	commit；

可重复读：

A	B
begin	begin
	select * from innodb1 where id =16; {id:16,title:独孤求败}
update innodb1 set title = '任我行' where id = 16;
commit;
	select * from innodb1 where id =16; {id:16,title:独孤求败}
	commit；
	select * from innodb1 where id =16; {id:16,title:任我行}

幻读

A	B
begin	begin
select * from innodb1 where id < 30;
	insert into innodb1 values(24,'任盈');
	commit;
update innodb1 set title = "任我行" where id < 30;
select * from innodb1 where id < 30; {id :24, 任我行}
commit；