mysql数据库锁的产生原因及解决办法

数据库和操作系统一样，是一个多用户使用的共享资源。当多个用户并发地存取数据 时，在数据库中就会产生多个事务同时存取同一数据的情况。若对并发操作不加控制就可能会读取和存储不正确的数据，破坏数据库的一致性。加锁是实现数据库并 发控制的一个非常重要的技术。在实际应用中经常会遇到的与锁相关的异常情况，当两个事务需要一组有冲突的锁，而不能将事务继续下去的话，就会出现死锁，严 重影响应用的正常执行。

在数据库中有两种基本的锁类型：排它锁（Exclusive Locks，即X锁）和共享锁（Share Locks，即S锁）。当数据对象被加上排它锁时，其他的事务不能对它读取和修改。加了共享锁的数据对象可以被其他事务读取，但不能修改。数据库利用这两 种基本的锁类型来对数据库的事务进行并发控制。

死锁的第一种情况

一个用户A 访问表A(锁住了表A),然后又访问表B；另一个用户B 访问表B(锁住了表B)，然后企图访问表A；这时用户A由于用户B已经锁住表B，它必须等待用户B释放表B才能继续，同样用户B要等用户A释放表A才能继续，这就死锁就产生了。

解决方法：

这种死锁比较常见，是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进 行处理，尽量避免同时锁定两个资源，如操作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按照相同的顺序来锁定资源。

死锁的第二种情况

用户A查询一条纪录，然后修改该条纪录；这时用户B修改该条纪录，这时用户A的事务里锁的性质由查询的共享锁企图上升到独占锁，而用户B里的独占锁由于A 有共享锁存在所以必须等A释放掉共享锁，而A由于B的独占锁而无法上升的独占锁也就不可能释放共享锁，于是出现了死锁。这种死锁比较隐蔽，但在稍大点的项 目中经常发生。如在某项目中，页面上的按钮点击后，没有使按钮立刻失效，使得用户会多次快速点击同一按钮，这样同一段代码对数据库同一条记录进行多次操 作，很容易就出现这种死锁的情况。

解决方法：

1、对于按钮等控件，点击后使其立刻失效，不让用户重复点击，避免对同时对同一条记录操作。
2、使用乐观锁进行控制。乐观锁大多是基于数据版本（Version）记录机制实现。即为数据增加一个版本标识，在基于数据库表的版本解决方案中，一般是 通过为数据库表增加一个“version”字段来实现。读取出数据时，将此版本号一同读出，之后更新时，对此版本号加一。此时，将提交数据的版本数据与数 据库表对应记录的当前版本信息进行比对，如果提交的数据版本号大于数据库表当前版本号，则予以更新，否则认为是过期数据。乐观锁机制避免了长事务中的数据 库加锁开销（用户A和用户B操作过程中，都没有对数据库数据加锁），大大提升了大并发量下的系统整体性能表现。Hibernate 在其数据访问引擎中内置了乐观锁实现。需要注意的是，由于乐观锁机制是在我们的系统中实现，来自外部系统的用户更新操作不受我们系统的控制，因此可能会造 成脏数据被更新到数据库中。
3、使用悲观锁进行控制。悲观锁大多数情况下依靠数据库的锁机制实现，如Oracle的Select … for update语句，以保证操作最大程度的独占性。但随之而来的就是数据库性能的大量开销，特别是对长事务而言，这样的开销往往无法承受。如一个金融系统， 当某个操作员读取用户的数据，并在读出的用户数据的基础上进行修改时（如更改用户账户余额），如果采用悲观锁机制，也就意味着整个操作过程中（从操作员读 出数据、开始修改直至提交修改结果的全过程，甚至还包括操作员中途去煮咖啡的时间），数据库记录始终处于加锁状态，可以想见，如果面对成百上千个并发，这 样的情况将导致灾难性的后果。所以，采用悲观锁进行控制时一定要考虑清楚。

死锁的第三种情况

如果在事务中执行了一条不满足条件的update语句，则执行全表扫描，把行级锁上升为表级锁，多个这样的事务执行后，就很容易产生死锁和阻塞。类似的情 况还有当表中的数据量非常庞大而索引建的过少或不合适的时候，使得经常发生全表扫描，最终应用系统会越来越慢，最终发生阻塞或死锁。

解决方法：

SQL语句中不要使用太复杂的关联多表的查询；使用“执行计划”对SQL语句进行分析，对于有全表扫描的SQL语句，建立相应的索引进行优化。
5．小结
总体上来说，产生内存溢出与锁表都是由于代码写的不好造成的，因此提高代码的质量是最根本的解决办法。有的人认为先把功能实现，有BUG时再在测试阶段进 行修正，这种想法是错误的。正如一件产品的质量是在生产制造的过程中决定的，而不是质量检测时决定的，软件的质量在设计与编码阶段就已经决定了，测试只是 对软件质量的一个验证，因为测试不可能找出软件中所有的BUG。

赞 (0)

相关推荐

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

    MySQL死锁问题是很多程序员在项目开发中常遇到的问题,现就MySQL死锁及解决方法详解如下: 1.MySQL常用存储引擎的锁机制 MyISAM和MEMORY采用表级锁(table-level locking) BDB采用页面锁(page-level locking)或表级锁,默认为页面锁 InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁 2.各种锁特点 表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低 行级锁:开销大,加锁慢;

  • MYSQL锁表问题的解决方法

    本文实例讲述了MYSQL锁表问题的解决方法.分享给大家供大家参考,具体如下: 很多时候!一不小心就锁表!这里讲解决锁表终极方法! 案例一 mysql>show processlist; 参看sql语句 一般少的话 mysql>kill thread_id; 就可以解决了 kill掉第一个锁表的进程, 依然没有改善. 既然不改善, 咱们就想办法将所有锁表的进程kill掉吧, 简单的脚本如下. #!/bin/bash mysql - u root - e " show processli

  • mysql 锁表锁行语句分享(MySQL事务处理)

    复制代码 代码如下: mysql_query("set autocommit=0"); $list_one = $db->fetch_first("select * from prizes where id = ".$id." FOR UPDATE"); $db->query("DELETE from prizes WHERE id =".$list_one['id']); mysql_query("co

  • Mysql(MyISAM)的读写互斥锁问题的解决方法

    由于没办法在短期内增加读的服务器,所以采取对Mysql进行了一些配置,以牺牲数据实时性为代价,来换取所有服务器的生命安全.呵呵,具体相关调整以及思路如下: MyISAM在读操作占主导的情况下是很高效的.可一旦出现大量的读写并发,同InnoDB相比,MyISAM的效率就会直线下降,而且,MyISAM和 InnoDB的数据存储方式也有显著不同:通常,在MyISAM里,新数据会被附加到数据文件的结尾,可如果时常做一些UPDATE,DELETE操作之后,数据文件就不再是连续的,形象一点来说,就是数据文件

  • mysql 数据库死锁原因及解决办法

    死锁(Deadlock) 所谓死锁:是指两个或两个以上的进程在执行过程中,因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去.此时称系统处于死锁状态或系统产生了死锁,这些永远在互相等待的进程称为死锁进程.由于资源占用是互斥的,当某个进程提出申请资源后,使得有关进程在无外力协助下,永远分配不到必需的资源而无法继续运行,这就产生了一种特殊现象死锁. 一种情形,此时执行程序中两个或多个线程发生永久堵塞(等待),每个线程都在等待被其他线程占用并堵塞了的资源.例如,如果线程A锁住了记

  • mysql锁表和解锁语句分享

    页级的典型代表引擎为BDB. 表级的典型代表引擎为MyISAM,MEMORY以及很久以前的ISAM. 行级的典型代表引擎为INNODB. -我们实际应用中用的最多的就是行锁. 行级锁的优点如下: 1).当很多连接分别进行不同的查询时减小LOCK状态. 2).如果出现异常,可以减少数据的丢失.因为一次可以只回滚一行或者几行少量的数据. 行级锁的缺点如下: 1).比页级锁和表级锁要占用更多的内存. 2).进行查询时比页级锁和表级锁需要的I/O要多,所以我们经常把行级锁用在写操作而不是读操作. 3).

  • MySQL中由load data语句引起死锁的解决案例

    MySQL中由load data语句引起死锁的解决案例

    一个线上项目报的死锁,简要说明一下产生原因.处理方案和相关的一些点. 1.背景 这是一个类似数据分析的项目,数据完全通过LOAD DATA语句导入一个InnoDB表中.为方便描述,表结构简化为如下: Create table tb(id int primary key auto_increment, c int not null) engine=innodb; 导入数据的语句对应为 Load data infile 'data1.csv' into table tb; Load data inf

  • mysql数据库锁的产生原因及解决办法

    数据库和操作系统一样,是一个多用户使用的共享资源.当多个用户并发地存取数据 时,在数据库中就会产生多个事务同时存取同一数据的情况.若对并发操作不加控制就可能会读取和存储不正确的数据,破坏数据库的一致性.加锁是实现数据库并 发控制的一个非常重要的技术.在实际应用中经常会遇到的与锁相关的异常情况,当两个事务需要一组有冲突的锁,而不能将事务继续下去的话,就会出现死锁,严 重影响应用的正常执行. 在数据库中有两种基本的锁类型:排它锁(Exclusive Locks,即X锁)和共享锁(Share Lock

  • MySQL主从同步延迟的原因及解决办法

    MySQL主从同步延迟的原因及解决办法

    由于历史原因,MySQL复制基于逻辑的二进制日志,而非重做日志.多次被问到何时MySQL能支持基于物理的复制,其实这就看MySQL各位大佬的想法.上次和赖老师脑暴,倏地说道:MySQL会不会来个基于Paxos的redo复制? 物理复制的真正好处不在于正确性,因为基于ROW格式的日志复制也已能完全保证复制的正确性.由于物理日志的写入是在事务执行过程中就不断写入,而二进制日志的写入仅仅在事务提交时.因此物理日志的优势如下所示: 复制架构下,大事务日志提交速度快: 复制架构下,主从数据延迟小: 假设执

  • MYSQL数据库使用UTF-8中文编码乱码的解决办法

    1.用phpmyadmin创建数据库和数据表 创建数据库的时候,请将"整理"设置为:"utf8_general_ci" 或执行语句: 复制代码 代码如下: CREATE DATABASE `dbname` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; 创建数据表的时候:如果是该字段是存放中文的话,则需要将"整理"设置为:"utf8_general_ci", 如果该字段是

  • Mysql/MariaDB启动时处于进度条状态导致启动失败的原因及解决办法

    Mysql/MariaDB启动时处于进度条状态导致启动失败的原因及解决办法

    今天打开网站突然发现网站无法打开,后来通过SSH登陆服务器发现MARIADB数据库没有启动成功,再次启动还是无法成功启动,一直处于启动进度条,进度条结束后提示ERROR.查看日志出现以下错误: InnoDB: Unable to lock ./ibdata1, error: 11 后经调试发现是因为MariaDB数据库所在分区已经满了,造成无法启动. 只有将MariaDB数据库存放数据目录移动到另外一个磁盘份额比较大的分区或者将当前分配删除一些不必要的文件. 移动办法: 1.停掉mysql服务器

  • Oracle用户被锁的原因及解决办法

    在登陆时被告知test用户被锁 1.用dba角色的用户登陆,进行解锁,先设置具体时间格式,以便查看具体时间 SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'; Session altered. 2.查看具体的被锁时间 SQL> select username,lock_date from dba_users where username='TEST'; USERNAME LOCK_DATE TEST 2009-03-1

  • MySQL查询优化之查询慢原因和解决技巧

    在做开发的朋友特别是和mysql有接触的朋友会碰到有时mysql查询很慢,当然我指的是大数据量百万千万级了,不是几十条了, 下面我们来看看解决查询慢的办法 会经常发现开发人员查一下没用索引的语句或者没有limit n的语句,这些没语句会对数据库造成很大的影响,例如一个几千万条记录的大表要全部扫描,或者是不停的做filesort,对数据库和服务器造成io影响等.这是镜像库上面的情况. 而到了线上库,除了出现没有索引的语句,没有用limit的语句,还多了一个情况,mysql连接数过多的问题.说到这里

  • MySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 的原因分解决办法

    MySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 的原因分解决办法

    MySQL ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO) 的解决办法和原因 这两天下载了MySQL5.7.11进行安装,发现到了初次使用输入密码的时候,不管怎样都进不去,即使按照网上说的在mysqld 下面添加skip-grant-tables也是不行,后来研究了两天,终于找出原因和解决办法. 复制代码 代码如下: [mysqlld] skip-grant-tables: 原因

  • MySQL启动失败之MySQL服务无法启动的原因及解决

    目录 MySQL服务无法启动的原因及解决 1.缺乏my.ini文件 2.缺乏data文件 3.端口占用(MySQL的默认端口是3306) 总结 MySQL服务无法启动的原因及解决 此处我的电脑为win10,mysql为8.0版本. 一般情况下,通过压缩的安装包,安装MySQL可能会出现MySQL无法启动的情况,如下图: 综合网上的各种资料,加自己的实际操作,总结为以下三种原因: 1.缺乏my.ini文件 通过记事本的方式创建my.ini文件, 也可以在电脑中搜索其他的my.ini文件,将其复制后

  • 当master down掉后，pt-heartbeat不断重试会导致内存缓慢增长的原因及解决办法

    当master down掉后，pt-heartbeat不断重试会导致内存缓慢增长的原因及解决办法

    最近同事反映,在使用pt-heartbeat监控主从复制延迟的过程中,如果master down掉了,则pt-heartbeat则会连接失败,但会不断重试. 重试本无可厚非,毕竟从使用者的角度来说,希望pt-heartbeat能不断重试,直到重新连接上数据库.但是,他们发现,不断的重试会带来内存的缓慢增长. 重现 环境: pt-heartbeat v2.2.19,MySQL社区版 v5.6.31,Perl v5.10.1,RHEL 6.7,内存500M 为了避免数据库启停对pt-heartbea

  • mysql免安装版1067错误终极解决办法图文教程

    mysql免安装版1067错误终极解决办法图文教程

    [从笔记本到pc] 笔记本一直都使用MySQL免安装版5.6.17,使用如下方法安装: 今天把同一个zip包传到pc上,按上面的方法安装居然报1067,按上面方法多次尝试还是不行. 百度,google查半天还是不行,最终折腾到一个好办法,应该可以一劳永逸的解决这个问题. [windows事件查看] 我的电脑--此电脑--右键管理--计算机管理--系统工具--事件查看器--Windows日志--应用程序--找错误标志,如下图 发现提示3306被占用 [杀掉占用程序] 通过netstat -ano查

随机推荐