MySQL中的死锁和日志二或三。
案例:部分数据更新失败
有一天,渠道学生反馈一个特定频道数据的报告是0,大部分频道数据是正常的。原则上,它要么是正常的,要么都是失败的。什么导致异常数据异常
首先,我们可以在统计任务日志的基础上思考自然。但我们没有看到SQL更新失败的异常描述。当时数据库发生了什么事在查看MySQL服务器日志之前,习惯性地查看数据库的状态:
碰巧,更新在清晨发生了僵局。
有限的空间,这里的环境我省略了很多,从日志里可以看到,交易1和交易2人分别持有一定数量的行锁,然后等待一个锁,以MySQL死锁最后检测,然后选择回滚的预处理方法是交易1:InnoDB僵局将至少持有行级锁的事务回滚,他。
这里有3个问题。
1, InnoDB row lock is not only one line
因为这张桌子是InnoDB引擎,InnoDB支持行锁和表锁,InnoDB锁是通过佳所指数来实现这一点,MySQL和Oracle,后者是通过在数据块对应的锁行这行锁achieve.innodb实施特点意味着,只有当指数数据检索索引,InnoDB使用行级锁。否则,InnoDB使用表锁锁上所有的扫描线。在实际应用中,应特别注意这一特点,InnoDB行锁,否则,它可能会导致大量的锁冲突,这会影响并发性能。因为MySQL的行锁是锁的指标,而不是一个记录加锁,所以虽然它是一个记录,不访问的同行,但如果你使用相同的索引键值,将有一个锁冲突。当我们使用条件范围内的、不平等的检索数据和请求一个共享或独占锁,Jiasuo InnoDB指数将满足现有数据记录的条件;此外还将锁间隙锁多行,InnoDB除了通过锁定范围使用间隙锁,如果平等要求记录锁的使用存在,InnoDB也将使用间隙锁!
就这样。让我们看一下业务表的索引。
可以看出,该表的索引是非常不合理的:有3个索引,但更新没有完全使用索引,导致更新不精确索引、锁定多行范围数据和触发死锁。
知道原理后,我们可以精心打造四场综合指数让更新的准确指标InnoDB索引。事实上,在更新索引后,死锁问题得到了解决。
注:InnoDB不仅将打印出的交易和交易持有和等待的锁,而且记录本身,不幸的是,它可能会超过对预留长度输出InnoDB(只能打印1M的内容只能把死锁信息上),如果你看不到完整的输出,这样可以创造innodb_monitor或innodb_lock_monitor任何图书馆的形式,那么InnoDB的状态信息将完成每一15s记录在错误日志中。如:创建表innodb_monitor(INT)引擎= InnoDB;删除该表时,你不需要被记录在错误日志。
2、回滚为什么只有更新语句的一部分失败了
为什么只有更新语句的一部分失败了,而整个事务中的所有更新都失败了
这是因为我们的InnoDB是默认的自动提交:
在多个UPDATE或INSERT语句的情况下,每次执行SQL,InnoDB立即改变犯过一段时间,并释放锁,同时,这也是为什么很少的语句失败,在这种情况下,死锁回滚事务后。
需要注意的是,通常会有另一种情况,也能导致语句被回滚部分很重要,需要额外的关注。有一个参数叫innodb_rollback_on_timeout InnoDB
这是在官方手册中描述的。
在MySQL 5.1中,InnoDB只回滚的最后一个语句在默认情况下,事务超时。如果innodb_rollback_on_timeout指定事务超时的原因会中止并回滚整个事务(行为在MySQL这个变量是4.1相同)。在MySQL 5.1.15添加。
说明:如果此参数是关闭的或不存在的,则事务的最后一个查询只有在超时结束时才回滚,事务的打开在事务超时时回滚到整个事务。
三.如何降低InnoDB死锁的可能性
在行锁和事务场景中很难完全消除死锁,但可以通过表设计和SQL调整来减少锁冲突和死锁,包括:
尝试使用较低的隔离级别。例如,如果有一个间隙锁,你可以会话或事务的隔离级别更改为RC(读取)的水平来避免它,但在这个时候你需要设置binlog_format划船或混合格式。
仔细设计索引,尽可能使用索引访问数据,使锁更准确,从而减少锁冲突的机会。
选择合理的事务大小,在小事务中锁定冲突的概率也较小。
当锁显示的记录集,最好是找一个足够的锁一次。例如,如果你要修改的数据,最好是申请互斥锁,而不是申请一个锁,锁和修改以使僵局很容易。
当不同的程序访问一组表时,它们应该尝试以相同的顺序访问每个表。对于一个表,尝试以固定的顺序访问表中的行,这样可以大大减少死锁的机会。
尽量使用相等的条件来访问数据,这样可以避免间隙锁对并发插入的影响。
不要应用超过需要的锁定级别;除非必要,不要显示锁。
对于某些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能性。
案例2:奇怪的锁等待超时
这几天在8在6在早晨和上午有一个java sql数据加载任务失败,本地文件锁等待超时突破尝试,重新启动时,InnoDB异常交易,这是锁与学生沟通的平台,时间太短,自己企业的数据库或锁冲突。但不你认为呢这不总是好的吗它基本上是一个单一的表单任务,并且没有多人冲突。
别担心这个问题。让我们先看看我们自己的数据库。
默认锁超时50秒,这一次真的不算短,估计。不,当下一次测试没有用的时候,确实是死了…
这表明发动机INNODB STATUS G也没有出现任何死锁信息,然后把MySQL服务器日志,希望从日记中哪些数据是之前和之后的时间做的事情。这里是对MySQL的日志文件系统组成简介。
(一)错误日志:记录时出现的问题开始,运行,或停止mysqld默认。
(b)一般日志:一般查询日志,记录所有语句和指令,数据库将有大约5%的性能损失。
(C)binlog日志:一个二进制格式记录所有变化的数据报表,主要从复制和数据恢复。
(d)慢日志:记录long_query_time秒执行时间或没有索引的所有查询,并默认是关闭默认。
(E):InnoDB InnoDB日志重做日志,撤消日志数据和撤销操作恢复。
从上面的描述可以看出,目前的日志问题可能在D和B,见D没有,它只会打开B,但是B数据库的性能有一定的损失,因为整个日志的数量,是非常庞大的,所以要谨慎开放:
我只是每天打开一个完整的日志,在问题出现前后一个半小时,我没有找到任何MySQL客户机请求到我们的业务数据库!日志格式如下,所有连接和命令都记录下来:
基本的问题是确定的,客户要求我们不把例外,方在三和平沟通和确认,最后确认在平台边实施插入在他们需要删除SQL和SQL任务从任务状态更新,在大量插入和更新的并发性整点此表中的结果,导致一些SQL超时等待锁…
MySQL日志分析脚本
由于上午是数据仓库业务的高峰期,此时出现了许多问题。一些奇怪的问题,往往不能在村里发生。我们如何捕捉快速定位的日志,这是重中之重,在这里我写了一个脚本,crontab配置,您可以选择时间范围的开放,每分钟采样的时间记录,这是一般的日志。不要打开,否则数据库性能就会丢失。
以上是本文的全部内容,希望能对您有所帮助,希望大家多多支持。