MySQL死锁问题实例分析及解决方法

MySQL死锁问题的相关知识是本文我们主要要介绍的内容,接下来我们就来一一介绍这部分内容,希望能够对您有所帮助。


  1、MySQL常用存储引擎的锁机制


  MyISAM和MEMORY采用表级锁(table-level locking)


  BDB采用页面锁(page-level locking)或表级锁,默认为页面锁


  InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁


  2、各种锁特点


  表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低


  行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高


  页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般


  3、各种锁的适用场景


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


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


  4、死锁


  是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。


  表级锁不会产生死锁.所以解决死锁主要还是针对于最常用的InnoDB.


  5、死锁举例分析


  在MySQL中,行级锁并不是直接锁记录,而是锁索引。索引分为主键索引和非主键索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了非主键索引,MySQL会先锁定该非主键索引,再锁定相关的主键索引。


  在UPDATE、DELETE操作时,MySQL不仅锁定WHERE条件扫描过的所有索引记录(mysql中where后面条件筛选顺序为从左到右与oracle相反,如果where语句的第一个条件就是非主键索引怎会锁住改条件筛选的所有记录,所以第一个条件应该尽量是筛选掉最多可能重复记录的条件,或者前两个条件为联合索引能唯一确定记录,这样就能最大限度的避免死锁),而且会锁定相邻的键值,即所谓的next-key locking。


  例如,一个表db.tab_test,结构如下:


  id:主键;


  state:状态;


  time:时间;


  索引:idx_1 (state, time)


  出现死锁日志如下:


  ***(1) TRANSACTION: TRANSACTION 0 677833455, ACTIVE 0 sec, process no 11393, OS thread id 278546 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id 83, query id 162348740 dcnet03 dcnet Searching rows for update update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute) (任务1的sql语句) ***(1) WAITING FOR THIS LOCK TO BE GRANTED: (任务1等待的索引记录) RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833455 _mode X locks rec but not gap waiting Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc xxx.com/;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;; *** (2) TRANSACTION: TRANSACTION 0 677833454, ACTIVE 0 sec, process no 11397, OS thread id 344086 updating or deleting, thread declared inside InnoDB 499 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320, undo log entries 1 MySQL thread id 84, query id 162348739 dcnet03 dcnet Updating update tab_test set state=1067,time=now () where id in (9921180) (任务2的sql语句) *** (2) HOLDS THE LOCK(S): (任务2已获得的锁) RECORD LOCKS space id 0 page no 849384 n bits 208 index `PRIMARY` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap Record lock, heap no 92 PHYSICAL RECORD: n_fields 11; compact format; info bits 0 0: len 8; hex 800000000097629c; asc b ;; 1: len 6; hex 00002866eaee; asc (f ;; 2: len 7; hex 00000d40040110; asc @ ;; 3: len 8; hex 80000000000050b2; asc P ;; 4: len 8; hex 800000000000502a; asc P*;; 5: len 8; hex 8000000000005426; asc T&;; 6: len 8; hex 800012412c66d29c; asc A,f ;; 7: len 23; hex 75706c6f6164666972652e636f6d2f6 8616e642e706870; asc uploadfire.com/hand.php;; 8: len 8; hex 800000000000042b; asc +;; 9: len 4; hex 474bfa2b; asc GK +;; 10: len 8; hex 8000000000004e24; asc N$;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: (任务2等待的锁) RECORD LOCKS space id 0 page no 843102 n bits 600 index `idx_1` of table `db/tab_test` trx id 0 677833454 lock_mode X locks rec but not gap waiting Record lock, heap no 395 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 8; hex 8000000000000425; asc %;; 1: len 8; hex 800012412c66d29c; asc A,f ;; 2: len 8; hex 800000000097629c; asc b ;; *** WE ROLL BACK TRANSACTION (1) (回滚了任务1,以解除死锁)


  原因分析:


  当“update tab_test set state=1064,time=now() where state=1061 and time < date_sub(now(), INTERVAL 30 minute)”执行时,MySQL会使用idx_1索引,因此首先锁定相关的索引记录,因为idx_1是非主键索引,为执行该语句,MySQL还会锁定主键索引。


  假设“update tab_test set state=1067,time=now () where id in (9921180)”几乎同时执行时,本语句首先锁定主键索引,由于需要更新state的值,所以还需要锁定idx_1的某些索引记录。


  这样第一条语句锁定了idx_1的记录,等待主键索引,而第二条语句则锁定了主键索引记录,而等待idx_1的记录,这样死锁就产生了。


  6、解决办法


  拆分第一条sql,先查出符合条件的主键值,再按照主键更新记录:


  select id from tab_test where state=1061 and time < date_sub(now(), INTERVAL 30 minute); update tab_test state=1064,time=now() where id in(......);


  关于MySQL死锁问题的实例分析及解决方法就介绍到这里了,希望本次的介绍能够对您有所收获!

版权声明:本文为博主原创文章,未经博主允许不得转载。

本页内容版权归属为原作者,如有侵犯您的权益,请通知我们删除。

oracle em 修改端口 - 2015-11-10 14:11:59

重建em后,oem的端口由原来的1158变为了5500. 通过下面的命令可以重新修改oem的端口: emca -reconfig ports -DBCONTROL_HTTP_PORT 1158  修改前先停止oem的运行。
查看MySQL数据库表 进入MySQL Command line client下 查看当前使用的数据库: mysqlselect database();mysqlstatus;mysqlshow tables;mysqlshow databases;//可以查看有哪些数据库,返回数据库名(databaseName)mysqluse databaseName; //更换当前使用的数据库mysqlshow tables; //返回当前数据库下的所有表的名称 或者也可以直接用以下命令 mysqlshow tab
背景    基于上文:使用oradebug dump hanganalyze分析oracle hang系列一,http://blog.itpub.net/9240380/viewspace-1823479/,继续熟悉TRACE FILE内容。 结论 1,DUMP FILE第三部分的构成 State of ALL nodes ([nodenum]/cnode/sid/sess_srno/session/ospid/state/[adjlist]): [9]/1/10/1/0xdcba9740/10574/N
前言: 在很多系统中,比如本人目前管理的数据库,索引经常被滥用,甚至使用DTA(数据库引擎优化顾问)来成批创建索引( DTA目前个人认为它的真正用处应该是在发现缺失的统计信息,在以前的项目中,用过一次DTA,里面提示了很多列缺少统计信息,后来在不改动其他操作的前提下,把这些统计信息手动建上去,性能提升非常明显。关于统计信息将另开文章介绍 )。一个表甚至有20多个索引(索引的数量并没有标准,但是要尽量合理,每个索引都应该能支撑大量查询或者增删改中的查询功能才有存在价值)。索引过多带来了服务器的沉重压力,有这
一、MySQL的相关概念介绍 MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成, 如图所示的一个表格: 表头(header): 每一列的名称; 列(row): 具有相同数据类型的数据的集合; 行(col): 每一行用来描述某个人/物的具体信息; 值(value): 行的具体信息, 每个值必须与该列的数据类型相同; 键(key): 表中用来识别某个特定的人\物的方法
首先对于MySQL的DISTINCT的关键字的一些用法: 1.在count 不重复的记录的时候能用到,比如 SELECT COUNT( DISTINCT id ) FROM tablename; 就是计算talbebname表中id不同的记录有多少条。 2,在需要返回记录不同的id的具体值的时候可以用,比如 SELECT DISTINCT id FROM tablename; 返回talbebname表中不同的id的具体的值。 3.上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义,比如 SE
背景    db hang时,分析下oradebug dump hanganalyze 产生的trace file第一部分内容与未HANG的区别,进一步理解其内容构成。 结论 1,DB 未HANG时,第一部分显示如下: Chains most likely to have caused the hang:  [a] Chain 1 Signature:      Chain 1 Signature Hash: 0xccebf225  [b] Chain 2 Signature:      Chain 2

无处不在的等待 - 2015-11-10 14:11:44

无处不在的等待 提交 我的评论 加载中 已评论 11月03日,由Oracle 资深DBA彭小波老师在“DBA+东北群”进行了一次关于“无处不在的等待”的线上主题分享。小编特别整理出其中精华内容,供大家学习交流。 嘉宾简介 “DBA+社群”东北联合发起人。从事Oracle 数据库技术十余年,ACOUG核心成员,Oracle用户组年轻专家。现就职于某金融保险公司,曾服务于航天、航空、机车、大型制造行业数据库的维护以及系统开发,擅长Oracle数据库架构规划、SQL优化、OWI方面性能的优化。 内容摘要 ORA

mysql命令行如何操作 - 2015-11-10 14:11:07

现在向大家介绍mysql命令行下,从数据库的建立到表数据的删除全过程,希望对大家有所帮助。 登陆mysql 打cmd命令终端,如果已经添加了mysql的环境变量,可以直接使用命令mysql -uroot直接回车,之后按提示输入密码。 如果未添加mysql的环境变量,可以切换到mysql的安装目录下的bin目录,再使用mysq -uroot 你也可以手动为mysql添加环境变量。 下面先介绍怎么添加环境变量的方法,使得在cmd命令行中运行mysql。 下载最新版mysql软件,将mysql安装到系统目录中,
背景   oracle db有可能因为诸多因系而hang,可以借助某些工具进行诊断分析,本文主要学习如何用不用oradebug dump hanganalyze 3进行分析产生的TRACE FILE.  以及所包含的内容结构。  结论 1,oradebug hanganalyze 3产生的TRACE FILE包括3部分内容 2,各部分内容如下: 第一部分 Chains most likely to have caused the hang:  [a] Chain 1 Signature:      Cha