Mysql基础知识,锁

1. 锁的基本感念

在mysql中的锁看起来是很复杂的，而且在实际的工作中(写的项目基本没有并发)很少用到,且没有做过分布式和负载均衡,所以基本就对Mysql的锁很陌生,但是为了以后的学习,现在必须明白锁的作用和用法.

1.1 为什么使用锁

以一个商城的秒杀活动举例,假如秒杀商品只有一件,当活动开始的时候涌入大量的用户,我们在不做队列和缓存的情况下一般的判断步骤是:

• 先判断Mysql中秒杀的商品是否存在
• 存在过后直接生成订单,秒杀商品的库存-1
• 当秒杀商品的库存为0时,结束活动

但是在并发很高的情况下,假如用户A已经生成订单,正准备将秒杀商品库存减去一的时候,用户B此时也进入了活动,并通过了秒杀商品不为0的验证,最终导致了商品的超卖.
所以在此系统中,我们必须要为Mysql进行加锁操作

1.2 Mysql锁的分类

2. 锁的分类

首先，从锁的粒度，我们可以分成三大类：

• 表锁
  开销小，开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
• 行锁
  开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。
• 页面锁
  开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

2.1 MyISAM表锁

首先需要注意一点的是,不同的存储引擎支持的锁粒度是不一样的：

• MyISAM只支持表锁！
• InnoDB行锁和表锁都支持,但行锁是在有索引的情况下,没有索引的表是锁定全表的！

同时表锁下又分为两种模式：

1. 表读锁（Table Read Lock）
2. 表写锁（Table Write Lock）

他们的关系是:在表读锁和表写锁的环境下：读读不阻塞，读写阻塞，写写阻塞！

• 读读不阻塞：当前用户在读数据，其他的用户也在读数据，不会加锁
• 读写阻塞：当前用户在读数据，其他的用户不能修改当前用户读的数据，会加锁！
• 写写阻塞：当前用户在修改数据，其他的用户不能修改当前用户正在修改的数据，会加锁！

最终我们可以理解为:当一个线程获得对一个表的写锁后，只有持有锁线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
我们可以通过以下命令对表锁进行编辑:

lock table member2 read,member write; 		#对member添加写锁 member2添加读锁
SELECT * FROM `member2`						#运行业务SQL
unlock tables;								#解除所有的表锁

对Mysql能否查看数据表是否被加锁呢?? 当然,我们可以使用:

show open tables;

来查看,结果如下:
当In_use为1时,表示该表已经被锁定了;

2.2 InnoDB行锁和事务

InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。
行级锁和表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。

2.2.1 行级锁

行锁由字面意思理解，就是给某一行加上锁，也就是一条记录加上锁。
InnoDB实现了以下两种类型的行锁。

• 共享锁（S锁）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  也叫做读锁：读锁是共享的，多个客户可以同时读取同一个资源，但不允许其他客户修改。

  SELECT * from TABLE where id = "1"  lock in share mode;  结果集的数据都会加共享锁
  

• 排他锁（X锁)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
  也叫做写锁：写锁是排他的，写锁会阻塞其他的写锁和读锁。

  select status from TABLE where id=1 for update;
  

另外，为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

• 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。

• 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

  意向锁也是数据库隐式帮我们做了，不需要程序员操心！

2.2.2 事务（Transaction）及其ACID属性

事务是由一组SQL语句组成的逻辑处理单元，事务具有4属性，通常称为事务的ACID属性。

• 原性性（Actomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以操持完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。
• 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

1. 并发事务带来的问题

并发事务处理也会带来一些问题，主要包括以下几种情况。

• 脏读（Dirty Reads）：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
• 不可重复读（Non-Repeatable Reads）：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
• 幻读（Phantom Reads）：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

2. 事务隔离机制

在并发事务处理带来的问题中，“更新丢失”通常应该是完全避免的。但防止更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对要更新的数据加必要的锁来解决，因此，防止更新丢失应该是应用的责任。
“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。数据库实现事务隔离的方式，基本可以分为以下两种。

1. 是在读取数据前，对其加锁，阻止其他事务对数据进行修改,我们称之为事务的隔离级别。
2. 是不用加任何锁，通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot），并用这个快照来提供一定级别（语句级或事务级）的一致性读取。从用户的角度，好像是数据库可以提供同一数据的多个版本，因此，这种技术叫做数据多版本并发控制（ＭultiVersion Concurrency Control，简称MVCC或MCC），也经常称为多版本数据库。

3. MVCC和事务的隔离级别

我们在初学的时候已经知道，事务的隔离级别有4种：

隔离级别	解释	可能会出现的问题
Read uncommitted	读未提交，就是一个事务可以读取另一个未提交事务的数据。	会出现脏读，不可重复读，幻读
Read committed	读提交，就是一个事务要等另一个事务提交后才能读取数据。	会出现不可重复读，幻读
Repeatable read	重复读，就是在开始读取数据（事务开启）时，不再允许修改操作(此级别为Mysql默认级别)	会出现幻读(但在Mysql实现的Repeatable read配合gap锁不会出现幻读！)
Serializable	最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读,但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。	串行，避免以上的情况！

2.3 悲观锁

悲观锁就是在操作数据时，认为此操作会出现数据冲突，所以在进行每次操作时都要通过获取锁才能进行对相同数据的操作，这点跟java中的synchronized很相似，所以悲观锁需要耗费较多的时间。另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。
需要注意的是共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。
我们可以使用命令设置MySQL为非autocommit模式：

set autocommit=0;

# 设置完autocommit后(autocommit为0时是未自动提交事务模式)，我们就可以执行我们的正常业务了。具体如下：

# 1. 开始事务

begin;/begin work;/start transaction; (三者选一就可以)

# 2. 查询表信息(必须命中索引才能进行行锁)

select status from TABLE where id=1 for update;

# 3. 插入一条数据

insert into TABLE (id,value) values (2,2);

# 4. 修改数据为

update TABLE set value=2 where id=1;

# 5. 提交事务

commit;/commit work;

2.4 乐观锁

用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据。

举例
1、数据库表设计

三个字段，分别是id,value、version

select id,value,version from TABLE where id=#{id}

2、每次更新表中的value字段时，为了防止发生冲突，需要这样操作

update TABLE
set value=2,version=version+1
where id=#{id} and version=#{version};

2.5 死锁

死锁（Deadlock）
所谓死锁：是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象，若无外力作用，它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。由于资源占用是互斥的，当某个进程提出申请资源后，使得有关进程在无外力协助下，永远分配不到必需的资源而无法继续运行，这就产生了一种特殊现象死锁。
解除正在死锁的状态有两种方法：

第一种：

1. 查询是否锁表

   show OPEN TABLES where In_use > 0;
   

2. 查询进程（如果您有SUPER权限，您可以看到所有线程。否则，您只能看到您自己的线程）

   show processlist
   

3. 杀死进程id（就是上面命令的id列）

   kill id
   

第二种：

1. 查看当前的事务

   SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
   

2. 查看当前锁定的事务

   SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
   

3. 查看当前等锁的事务

   SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 
   

4. 杀死进程

   kill 进程ID