Day23-mysql的锁详解-同乐学堂

一、前言

经发现，造成业务异常的，冲突的，最大祸首是我们不能很好的利用MySQL的锁。就拿前几天的事情吧，微信付款，扫码成功，并输入支付密码。

可能因为延迟和不为人知的异常，导致微信支付界面提示失败，可是同时我的银行短信消费通知已经过来了，查了下银行卡余额明确是扣我款了，但是腾讯却告诉我支付失败。

你们说他们事务做的好么？锁利用好了么？

3天之后，腾讯的异常处理机制才发起银行退款功能，第四天钱才回到我的银行卡里。

这肯定要改善的，可能腾讯支付接口和银行接口处理的不是很好，也对！本身也没有起步多少年，出现点Bug也是很正常！

其实小编也学过开发，也知道用代码控制事务的隔离级别等，也知道共享锁,排它锁,乐观，悲观，setlockXXX,等等，但是前端代码层怎么驱动数据库底层的锁进行控制，就mengmeng的了~，因为小编前端代码学的一直很渣，后期一定会深深的研究一下Mybaits 和Hibernate，但是重心会放在Mybaits上，因为发现Hibernate 因为全自动化的缘故，太发福了，太笨重了，用好它真心不容易。

不造Mybaits 和Hibernate的同学可以自行百度一下，他俩就是前端代码专门跟数据库打交道的一段java代码而已，也可以称之为工具，框架。

可是数据库底层的锁是怎么实现的，怎么才能把锁用好，使用省心的、又放心的数据，才是重中之重！所以咱们就一起深入了解MySQL数据库的锁吧。

下面是个人经验吐槽：

其实小编心中有个理想，人人都可以系程序猿~，其实代码都是死的，数据结构，算法，也就那几种，理解以至于会用完全人人都可以做的到，

在中国不行的原因，小编总结了几点：

1 、代码是英文的，中国大部分英语水平极低，导致了大部分人对代码产生了恐惧感，我连英文都学不好，怎么能学好代码，但是那些英语极好的，也不是很懂，原因在于，他是懒得弄懂而已。编程就跟写英文小说一样，只是按照编程语言设计者的规范来写一篇长篇的英文小说！


2、计算机在中国起步较晚，我在大学的时候，才拥有了自己的笔记本，可以尽情的去研究电脑，操作系统。跟外国小孩，没法比，比人晚了60年，但是也别灰心，计算机是死的。编程语言也是人脑袋想出来的。
   只是时间问题，他们终究会败在我们中国的高仿业，因为高仿，因为搬砖，导致我们的IT薪资也是及其低下，这也是没有办法的，希望后期可以转变！


3、中国的开源力量太小，商业化严重，缺少美国加州大学辣种顶尖的实验室，听说咱们也要搞实验室了，马云也要参与了，不以挣钱为目的只为科研这种，其实是很好的开端，不造背后是什么样辣~ 哈哈

4、中国女性的逻辑思维照比中国男性的强悍多了，对工科不感兴趣，以及受某些影响吧，大部分女生不喜欢玩计算机、不报计算机专业的原因我还没有统计出来，其实女性就拿织毛衣，做针织，做家务，生孩子
   都难不倒女人。如果女人投入到编程事业，秒杀男性，有木有！

5、还有巴拉巴拉好多好多，就不细说了·~

二、MySQL的锁是干嘛的！

就是为了保证数据的一致性，而使各种共享资源在被并发访问变得有序所设计的一种规则（它就是干这种事情的~。）

（帮助理解：如果觉的太抽象，你就把它想象成真实世界的锁即可，比如共享单车需要锁吧，让共享单车使用的井然有序，如果它没有锁会变成什么样？就跟数据库的锁坏了，使用不当了是一个道理~）。

锁定机制的优劣直接应想到一个数据库系统的并发处理能力和性能，所以锁定机制的实现也就成为了各种数据库的核心技术之一。

MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁。

页级:引擎 BDB。

表级:引擎 MyISAM ，理解为锁住整个表，可以同时读，写不行

行级:引擎 INNODB ，单独的一行记录加锁

	行锁	表锁	页锁
MyISAM		√
BDB		√	√
InnoDB	√	√

行锁：开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

表锁：开销小，加锁快；不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

页锁：开销和加锁速度介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

由于BDB已经被InnoDB所取代，我们只讨论MyISAM表锁和InnoDB行锁的问题！

三、锁的类型

从对数据操作的类型（读\写）

读锁（共享锁）：针对同一块数据，多个读操作可以同时进行而不会互相影响。

写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

大多数时候，MySQL锁的内部管理都是透明的。

四、锁的粒度和死锁

4.1锁粒度（Lock granularity）

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。

一种提高共享资源并发发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

但是，加锁也需要消耗资源。锁的各种操作，包括获得锁、检查锁和是否已经解除、释放锁等，都会增加系统的开销。所谓锁策略，就是在锁的开销和数据的安全性之间寻求平衡。

表锁：管理锁的开销最小，同时允许的并发量也最小的锁机制。MyIsam存储引擎使用的锁机制。当要写入数据时，把整个表都锁上，此时其他读、写动作一律等待。在MySql中，除了MyIsam存储引擎使用这种锁策略外，MySql本身也使用表锁来执行某些特定动作，比如alter table.

另外，写锁比读锁有更高的优先级，因此一个写锁可能会被插入到读锁队列的前面。

行锁：可以支持最大并发的锁策略（同时也带来了最大的锁开销）。InnoDB和Falcon两张存储引擎都采用这种策略。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。服务器层完全不了解存储引擎中的锁实现。

MySql是一种开放的架构，你可以实现自己的存储引擎，并实现自己的锁粒度策略，不像Oracle，你没有机会改变锁策略，Oracle采用的是行锁。

4.2死锁（Dead Lock）

死锁是指两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的假象。多个事务同时锁定同一个资源时，也会产生死锁。

数据库系统实现了各种死锁检测和死锁超时的机制，InnoDB目前处理死锁的方法是，将持有最少行级排他锁的事务进行回滚。

例如：两个事务同时处理StockPrice表：

事务1

START TRANACTION;

UPDATE StockPrice SET close=45.50 WHERE stock_id=4 and date='2002-05-01';

UPDATE StockPrice SET close=19.80 WHERE stock_id=3 and date='2002-05-02';

commit

事务2

START TRANACTION;

UPDATE StockPrice SET close=20.12 WHERE stock_id=3 and date='2002-05-02';

UPDATE StockPrice SET close=47.20 WHERE stock_id=4 and date='2002-05-01';

commit ;

死锁的关键在于：两个(或以上)的Session加锁的顺序不一致。

那么对应的解决死锁问题的关键就是：让不同的session加锁有次序。

注意事项：锁的行为和顺序和存储引擎相关的，以同样的顺序执行语句，有些存储引擎会产生死锁，有些则不会。
死锁产生有双重原因：有些是因为真正的数据冲突，这种情况很难避免，但有些完全是由于存储引擎的实现方式导致的。
死锁发生以后，只有部分或者完全回滚其中一个事务，才能打破死锁。
对于事务型的系统，这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

日常解决死锁的操作：

解除正在死锁的状态有两种方法：

第一种：

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.杀死进程id（就是上面命令的trx_mysql_thread_id列）

kill 线程ID

实际死锁案例：

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
*************************** 1. row ***************************
                    trx_id: 20866
                 trx_state: LOCK WAIT
               trx_started: 2014-07-31 10:42:35
     trx_requested_lock_id: 20866:617:3:3
          trx_wait_started: 2014-07-30 10:42:35
                trx_weight: 2
       trx_mysql_thread_id: 9930577
                 trx_query: delete from dltask where id=1
       trx_operation_state: starting index read
         trx_tables_in_use: 1
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 376
           trx_rows_locked: 1
         trx_rows_modified: 0
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 10000
          trx_is_read_only: 0
trx_autocommit_non_locking: 0

3，看到有这条9930577的sql，kill掉。
mysql> kill 9930577;
Query OK, 0 rows affected (0.00 sec)

然后再去查询INNODB_TRX表，就没有阻塞的事务sleep线程存在了：
mysql> SELECT * FROM INNODB_TRX\G;
Empty set (0.00 sec)

如果有复杂的死锁，请进行日志分析具体参考此博客，已经分析解析的比较易懂：

http://blog.csdn.net/hw_libo/article/details/38789115

五、锁的使用场景

表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用

行级锁则更适合于有大量按索引条件并发更新数据,同时又有并发查询的应用,如一些在线事务处理系统。

六、详解表级锁，行级锁

MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。顾名思义，当一个表被加了读锁，其他进程仍可以对其加读锁，可是不能加写锁，当一个表被加了写锁，其他进程不能对其加读写锁。

InnoDB的行锁模式及加锁方法

InnoDB实现了以下两种类型的行锁。

l 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。

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

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

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

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

MYSQL的行级锁有四种：共享锁（S），排他锁（X），意向共享锁（IS）和意向排他锁（IX），他们之间的关系如下表所示：

	共享锁（S）	排他锁（X）	意向共享锁（IS）	意向排他锁（IX）
共享锁（S）	兼容	冲突	兼容	冲突
排他锁（X）	冲突	冲突	冲突	冲突
意向共享锁（IS）	兼容	冲突	兼容	兼容
意向排他锁（IX）	冲突	冲突	兼容	兼容

如果一个事务请求的锁模式与当前的锁兼容，InnoDB就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。

¡ 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。

¡ 排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。

用SELECT ... IN SHARE MODE获得共享锁，主要用在需要数据依存关系时来确认某行记录是否存在，并确保没有人对这个记录进行UPDATE或者DELETE操作。但是如果当前事务也需要对该记录进行更新操作，则很有可能造成死锁，对于锁定行记录后需要进行更新操作的应用，应该使用SELECT... FOR UPDATE方式获得排他锁。

详情请参考：http://blog.csdn.net/xifeijian/article/details/20313977

七、Mysql加锁过程详解

此篇文章分析的，比较全面，强烈推荐：

http://www.cnblogs.com/metoy/p/5545580.html

八、隐式和显示锁定机制

InnoDB采用的是两阶段锁定协议。在事务执行过程中，随时都可以执行锁定，锁只有在执行COMMIT或者ROLLBACK的时候才会释放，并且所有的锁是在同一时刻被释放,InnoDB会根据隔离级别在需要的时候自动加锁

另外InnoDB也支持通过特定的语句进行显示锁定，这些语句不属于SQL规范：

SELECT ......LOCK IN SHARE MODE

SELECT ......For UPDATE

MySQL 也支持LOCK TABLES 和 UNLOCK TABLES语句，这是在服务器层实现的，和存储引擎无关。它们有自己的用途，但并不能代替事务处理。如果应用需要用到事务，还是应该选择事务型的存储引擎。

建议：除了在事务中禁用AUTOCOMMIT可以使用 LOCK TABLES之外，其他任何时候都不要显示的执行LOCK TABLES,不管使用的是什么存储引擎。

比较厉害的M有SQL小伙伴的隐式优化策略：https://yq.aliyun.com/articles/41123 （大幅度提高性能，有木有！）