快乐学习
前程无忧、中华英才非你莫属!

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 (大幅度提高性能,有木有!)

九、MySQL锁容易出现的问题:争用!

可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺:

如果Table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况。

1. 查询InnoDB行锁争用情况

show status like 'innodb_row_lock%';

如果InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高,说明锁争用比较严重,这时可以通过设

置InnoDB Monitors来进一步观察发生锁冲突的表、数据行等,并分析锁争用的原因。

打开监视器:

CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;

Show innodb status\G;

停止监视器:

DROP TABLE innodb_monitor;

   打开监视器以后,默认情况下每15 秒会向日志中记录监控的内容,如果长时间打开会导致.err 文件变得非常的巨大,

所以用户在确认问题原因之后,要记得删除监控表以关闭监视器,或者通过使用“--console”选项来启动服务器以关闭写

日志文件。

最后小结:中小型企业对是数据库的锁也不要过于害怕,担心,因为中小型公司的并发量级业务以及复杂程度还达不到数据库经常死锁的情况 。了解了解就可以啦~

以下文章强烈推荐阅读:

MySQL详解-锁

http://blog.csdn.net/xifeijian/article/details/20313977

Lock wait timeout exceeded; try restarting transaction

http://blog.csdn.net/mchdba/article/details/38313881

处理mysql锁争用的一个示例

http://blog.itpub.net/29612462/viewspace-1162167

mysql数据库锁定机制

http://www.cnblogs.com/ggjucheng/archive/2012/11/14/2770445.html

MySQL学习之——锁(行锁、表锁、页锁、乐观锁、悲观锁等)

http://blog.csdn.net/mysteryhaohao/article/details/51669741

《高性能MySQL》读书笔记--锁、事务、隔离级别

http://blog.csdn.net/xifeijian/article/details/45229247

打赏
赞(0) 打赏
未经允许不得转载:同乐学堂 » Day23-mysql的锁详解

特别的技术,给特别的你!

联系QQ:1071235258QQ群:710045715

觉得文章有用就打赏一下文章作者

非常感谢你的打赏,我们将继续给力更多优质内容,让我们一起创建更加美好的网络世界!

支付宝扫一扫打赏

微信扫一扫打赏

error: Sorry,暂时内容不可复制!