mysql锁

1.锁概述

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、EAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更复杂。

2.锁分类

对数据操作的粒度分：
- 表锁：操作时，会锁整个表
- 行锁：操作时，会锁定当前操作行
从对数据操作的类型分：
- 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
- 写锁（排它锁）：当前操作没有完成之前，它会阻断其他写锁和读锁。

3.myisam表锁

myisam存储引擎只支持表锁，这也是mysql开始几个版本唯一支持的锁类型

如何加表锁
- myisam在执行select前，会自动给涉及到的表加读锁，执行update、delete、insert前，会自动加写锁，这个过程不需要用户干预，因此，用户一般不需要用loca table命令给myisam表显式加锁。
```
加读锁： lock table user read;
加写锁：lock table user write;
解锁：unlock tables;
```
读锁会阻塞其他线程的写操作，自己的写操作会报错
写锁会阻塞其他线程的读写操作
myisam的读写锁调度是写优先，开启写锁后其他线程不能做任何操作，大量的跟新会使查询很难得到锁，从而造成永久阻塞。
查看锁的争用情况
```
show open tables;
```
in_user:表示当前这个表被使用的次数。如果为0，则表是打开的，但是当前没有被使用。

name_locked：表名称是否被锁定。名称锁定用于取消表或对表进行重命名等操作。
```
show status like 'Table_locks%';
```
table_locks_immediate:指的是能够立即获得表级锁的次数，每立即获取锁，值加1.

table_locks_waited:指的是不能立即获取表级锁而需要等待的次数，每等待一次，值加1，此值越高说明存在着较为严重的表级锁争用情况。

4.innodb行锁

行锁特点：偏向innodb存储引擎，开销大，加锁慢；会出现死锁；锁的粒度小，发生锁冲突的概率最低，并发度也最高。innodb与myisam的最大不同有两点：一是支持事务；二是采用了行级锁。

事务及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元。

事务具有以下4个特性，简称为事务ACID属性。

ACID属性	含义
原子性（Atomiity）	事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。
一致性（Consistent）	在事务开始和完成时，数据都必须保持一致状态。
隔离性（Isolation）	数据库系统提供了一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境下运行
持久性（Durable）	事务完成之后，对于数据的修改时永久的。

并发事务处理带来的问题

问题	含义
丢失更新（lost update）	当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖。
脏读（Dirt reads ）	当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读（Non-Repeatable Reads）	一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。
幻读（Phantom Reads）	一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。

数据库隔离级别

隔离级别丢失更新脏读不可重复读幻读

Read uncommitted x √ √ √

Read committed x x √ √

Repeatable read(默认) x x x √

Serializable x x x x

备注：√代表可能出现，x代表不会出现

Mysql默认的数据库隔离级别为Repeatable read
```
show variables like 'tx_isolation'
```
innodb的行锁模式（where条件用了非索引字段锁为表锁）
- 共享锁（S）：又称为读锁，简称S锁，共享锁就是多个事务对于同一个数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
- 排他锁（X）：又称为写锁，简称X锁，排他锁就是不能与其他锁共存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据进行读取和修改。
- 对于update、delete、insert语句，innodb会自动给设计数据集加排他锁（X）；
- 对于普通select语句，innodb不会加任何锁；
```
共享锁（S）：select * from user where ... lock in share mode
排他锁（x）：select * from user where ... for update
```
间隙锁

当我们用范围条件，而不是使用相等条件检索数据，并请求共享或排他锁时，innodb会给符合条件的已有数据进行加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（CAP）”，innoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-key锁）。
```
查询到数据id为 1 2 5 7 8符合条件
本质会对 1 2 3 4 5 6 7 8 的数据进行加锁，实际上并没有3 4 6 的数据，插入3 4 6 会被锁住。
```
间隙锁的出现是为了在innodb的可重复读隔离级别下，解决幻读问题产生的。
优化建议：
- 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
- 合理设计索引，尽量缩小锁的范围
- 尽可能减少索引条件，及索引范围，避免间隙锁
- 尽量控制事务大小，减少锁定资源量和时间长度
- 尽可能使用低级别事务隔离级别（但是需要业务层面满足需求）