`
liss
  • 浏览: 844203 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

轻松掌握MySQL数据库锁机制的相关原理

阅读更多

MySQL 中对于使用表级锁定的存储引擎,表锁定时不会死锁的。这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。

在一个update和insert操作频繁的表中,少量数据测试的时候运行良好,在实际运营中,因数据量比较大(21万条记录),会出现死锁现象,用show processlist查看,可以看到一个update语句状态是Locked,一个delete语句状态是Sending data。查看了一下参考手册,把锁定相关的资料整理下来,以便自己记录和追踪该问题的解决情况:

MySQL 5.1支持对MyISAM和MEMORY表进行表级锁定,对BDB表进行页级锁定,对InnoDB 表进行行级锁定。在许多情况下,可以根据培训猜测应用程序使用哪类锁定类型最好,但一般很难说出某个给出的锁类型就比另一个好。一切取决于应用程序,应用程序的不同部分可能需要不同的锁类型。为了确定是否想要使用行级锁定的存储引擎,应看看应用程序做什么并且混合使用什么样的选择和更新语句。例如,大多数 Web应用程序执行许多选择,而很少进行删除,只对关键字的值进行更新,并且只插入少量具体的表。基本MySQL MyISAM设置已经调节得很好。

在MySQL中对于使用表级锁定的存储引擎,表锁定时不会死锁的。这通过总是在一个查询开始时立即请求所有必要的锁定并且总是以同样的顺序锁定表来管理。

对WRITE,MySQL使用的表锁定方法 原理 如下:

◆ 如果在表上没有锁,在它上面放一个写锁。

◆否则,把锁定请求放在写锁定队列中。

对READ,MySQL使用的锁定方法原理如下:

◆如果在表上没有写锁定,把一个读锁定放在它上面。

◆否则,把锁请求放在读锁定队列中。

当一个锁定被释放时,锁定可被写锁定队列中的线程得到,然后是读锁定队列中的线程。

这意味着,如果你在一个表上有许多更新,SELECT语句将等待直到没有更多的更新。

如果INSERT 语句不冲突,可以自由为MyISAM 表混合并行的INSERT 和SELECT 语句而不需要锁定。

InnoDB 使用行锁定,BDB 使用页锁定。对于这两种存储引擎,都可能存在死锁。这是因为,在SQL语句处理期间,InnoDB 自动获得行锁定,BDB 获得页锁定,而不是在事务启动时获得。

行级锁定的优点:

· 当在许多线程中访问不同的行时只存在少量锁定冲突。

· 回滚时只有少量的更改。

· 可以长时间锁定单一的行。

行级锁定的缺点:

· 比页级或表级锁定占用更多的内存。

· 当在表的大部分中使用时,比页级或表级锁定速度慢,因为你必须获取更多的锁。

· 如果你在大部分数据上经常进行 GROUP BY 操作或者必须经常扫描整个表,比其它锁定明显慢很多。

· 用高级别锁定,通过支持不同的类型锁定,你也可以很容易地调节应用程序,因为其锁成本小于行级锁定。

在以下情况下,表锁定优先于页级或行级锁定:

· 表的大部分语句用于读取。

· 对严格的关键字进行读取和更新,你可以更新或删除可以用单一的读取的关键字来提取的一行:

• UPDATE tbl_name SET column = value WHERE unique_key_col = key_value ;

• DELETE FROM tbl_name WHERE unique_key_col = key_value ;

· SELECT 结合并行的INSERT 语句,并且只有很少的UPDATE或 DELETE 语句。

· 在整个表上有许多扫描或 GROUP BY 操作,没有任何写操作。

不同于行级或页级锁定的选项:

· 版本(例如,为并行的插入在MySQL中使用的技术 ),其中可以一个写操作,同时有许多读取操作。这明数据库 或表支持数据依赖的不同视图,取决于访问何时开始。其它共同的术语是“时间跟踪”、“写复制”或者“按需复制”。

· 按需复制在许多情况下优先于页级或行级锁定。然而,在最坏的情况下,它可能比使用常规锁定使用多的内存。

· 除了行级锁定外,你可以使用应用程序级锁定,例如在MySQL中使用GET_LOCK()和RELEASE_LOCK()。这些是建议性锁定,它们只能在运行良好的应用程序中工作

为达到最高锁定速度,除InnoDB 和BDB 之外,对所有存储引擎,MySQL使用表锁定(而不是页、行或者列锁定)。对于InnoDB 和BDB 表,如果你用LOCK TABLES显式锁定表,MySQL只使用表锁定;如果你不使用LOCK TABLES,因为 InnoDB 使用自动行级锁定而BDB 使用页级锁定来保证事务隔离。

但是对于大表,对于大多数应用程序,表锁定比行锁定更好,但存在部分缺陷。表锁定使许多线程同时从一个表中进行读取操作,但如果一个线程想要对表进行写操作,它必须首先获得独占访问。更新期间,所有其它想要访问该表的线程必须等待直到更新完成。

表更新通常情况认为比表检索更重要,因此给予它们更高的优先级。这应确保更新一个表的活动 不能“饿死”,即使该表上有很繁重的SELECT 活动。

表锁定在这种情况下会造成问题,例如当线程正等待,因为硬盘已满并且在线程可以处理之前必须有空闲空间。在这种情况下,所有想要访问出现问题的表的线程也被设置成等待状态,直到有更多的硬盘空间可用。

表锁定在下面的情况下也存在问题:

· 一个客户发出长时间运行的查询。

· 然后,另一个客户对同一个表进行更新。该客户必须等待直到SELECT完成。

· 另一个客户对同一个表上发出了另一个 SELECT 语句。因为UPDATE比 SELECT 优先级高,该SELECT 语句等待UPDATE完成,并且等待第1个 SELECT 完成。

下面描述了一些方法来避免或减少表锁定造成的竞争:

· 试图使 SELECT 语句运行得更快。可能必须创建一些摘要(summary)表做到这点。

· 用--low-priority-updates启动mysqld。这将给所有更新(修改)一个表的语句以比SELECT语句低的优先级。在这种情况下,在先前情形的第2个SELECT语句将在UPDATE语句前执行,而不需要等候第1个 SELECT 完成。

· 可以使用SET LOW_PRIORITY_UPDATES=1语句指定具体连接中的所有更新应使用低优先级。

· 可以用LOW_PRIORITY属性给与一个特定的INSERT、UPDATE或DELETE语句较低优先级。

· 可以用HIGH_PRIORITY属性给与一个特定的SELECT语句较高优先级。

· 为max_write_lock_count系统变量指定一个低值来启动mysqld来强制MySQL在具体数量的插入完成后临时提高所有等待一个表的SELECT 语句的优先级。这样允许在一定数量的WRITE锁定后给出READ锁定。

· 如果你有关于INSERT结合SELECT的问题,切换到使用新的MyISAM表,因为它们支持并发的SELECT和INSERT。

· 如果你对同一个表混合插入和删除,INSERT DELAYED将会有很大的帮助。

· 如果你对同一个表混合使用 SELECT 和DELETE 语句出现问题,DELETE 的LIMIT 选项可以有所帮助。

· 对 SELECT 语句使用SQL_BUFFER_RESULT可以帮助使表锁定时间变短。

· 可以更改mysys/thr_lock.c中的锁代码 以使用单一的队列。在这种情况下,写锁定和读锁定将具有相同的优先级,对一些应用程序会有帮助。

这里是一些MySQL中表锁定相关的技巧:

· 如果不混合更新与需要在同一个表中检查许多行的选择,可以进行并行操作。

· 可以使用 LOCK TABLES 来提高速度,因为在一个锁定中进行许多更新比没有锁定的更新要快得多。将表中的内容切分为几个表也可以有所帮助。

· 如果在MySQL中表锁定时遇到速度问题,可以将表转换为 InnoDB 或BDB 表来提高性能。

分享到:
评论

相关推荐

    如何导入超大MySQL数据库备份文件

    ### 如何导入超大MySQL数据库备份文件 #### BigDump工具简介及应用场景 在日常网站管理和维护过程中,数据库的备份和恢复是一项...通过本文介绍的方法,用户可以轻松掌握BigDump的使用技巧,高效完成数据库恢复任务。

    ssh框架和开发的时MySQL数据库工具

    开发者可以利用Hibernate的ORM功能,轻松地操作MySQL数据库,实现数据的增删改查。 在实际开发中,SSH框架的集成需要配置相关的XML文件,例如Struts2的struts.xml、Spring的applicationContext.xml和Hibernate的...

    学校数据库ppt数据库原理,浅显易懂

    这份“学校数据库ppt”可能涵盖以上部分或全部知识点,通过图文并茂的方式,使初学者能够轻松掌握数据库的基本概念和操作。在实际学习过程中,配合案例分析和实践操作,将能更深入理解和运用这些知识。

    我们都是小青蛙MYSQL系列

    【MySQL是怎样运行的:从根儿上理解MySQL】系列文章由作者小孩子撰写,旨在深入解析MySQL的使用和原理。文章适合已有一定基础的读者,尤其是Java架构师和MySQL爱好者。内容涵盖MySQL的启动、字符集、记录存储、索引...

    数据库系统原理的课件

    这个“数据库系统原理的课件”是针对初学者设计的,旨在提供一个轻松上手的学习路径,便于实践操作。让我们深入探讨一下这个课件中可能涉及的重要知识点。 1. **数据库基础概念**:首先,你会了解到数据库是什么,...

    Golang 针对 MySQL 数据库表结构的差异 SQL 工具。.zip

    针对MySQL数据库,Golang提供了丰富的库和工具,使得开发者能够方便地与数据库进行交互,包括管理表结构的差异和SQL操作。 本项目"针对MySQL数据库表结构的差异SQL工具"可能是一个Golang编写的程序,旨在帮助开发者...

    用Qt通过ODBC连接MySQL数据库实现学生管理系统.zip

    【标题】: 使用Qt通过ODBC连接MySQL数据库实现学生管理系统 在C++编程环境中,Qt是一个功能强大的跨平台开发框架,常用于创建图形用户界面(GUI)应用。本项目是利用Qt来构建一个学生管理系统,它通过ODBC(Open ...

    数据库原理 - 比较基础,容易上手

    常见的数据库类型有关系型数据库(如MySQL、Oracle、SQL Server)、非关系型数据库(如MongoDB、Cassandra)以及图形数据库(如Neo4j)。关系型数据库基于关系理论,采用表格形式存储数据,而非关系型数据库则更灵活...

    ssh+mysql 完整项目

    4. **MySQL数据库**:MySQL提供了高效的数据存储和检索能力,适用于小型到大型的应用。在本项目中,它将存储代理商的详细信息,交易记录等所有相关数据,并通过Hibernate进行交互。 5. **JSP与CSS+jQuery**:JSP...

    MySQL游标:数据库操作的精准定位器

    MySQL 是一个广泛应用的开源关系型数据库管理系统(RDBMS),是LAMP技术栈的重要组成部分,该技术栈包括Linux操作系统、Apache Web服务器、MySQL数据库以及PHP/Python/Perl等编程语言。MySQL 的特性包括但不限于: ...

    Mysql5.7入门到精通PDF带目录 高清

    这份“MySQL 5.7入门到精通”的PDF教程是学习和掌握MySQL数据库技术的重要资源,尤其对于初学者来说,它提供了清晰的路径和详尽的指导。 教程的“带目录”特性意味着读者可以轻松地定位到特定的主题或章节,方便...

    redhat 下mysql的安装和操作命令

    掌握这些命令,可以有效地管理MySQL数据库,进行数据的增删改查等操作。 总之,在Red Hat Linux环境下安装和配置MySQL及相关的Perl模块并不复杂,只需按照上述步骤操作即可。然而,深入理解每个步骤背后的原理和...

    pyQt5及mysql制作的一个带UI界面的简易ATM系统,可以与数据库进行交互.zip

    在本资源中,我们利用PyQt5构建了一个模拟ATM系统的用户界面,该界面直观易用,能够与MySQL数据库进行交互,实现了基本的银行账户管理功能。 首先,让我们详细了解PyQt5。PyQt5包括了各种窗口小部件,如按钮、...

    多功能数据库连接工具

    1. **多数据库支持**:该工具能够连接到各种类型的数据库,无论你是需要处理关系型数据库(如MySQL、SQL Server)还是非关系型数据库(如MongoDB、Redis),都能轻松应对。这极大地扩展了用户的操作范围,提高了工作...

    统一数据库管理系统(毕业设计)

    通过这个统一数据库管理系统,学习者不仅可以掌握数据库管理的基本原理,还能了解到如何设计和实现一个跨平台、多数据库兼容的应用程序,这对于提升编程技能和理解软件工程实践具有重要的价值。

    MySQL必知必会 高清带书签

    10. **备份与恢复**:讨论了如何备份MySQL数据库,以及在数据丢失或系统故障时如何恢复,确保数据安全。 11. **权限管理**:介绍MySQL的用户管理和权限控制机制,如何创建用户、设置权限,以及如何进行权限的收回和...

    mysql-5.5.27-winx64.zip

    同时,熟悉MySQL的工作原理,如事务处理、锁定机制、备份与恢复策略,以及性能优化技巧,如查询优化、内存管理、分区表等,将有助于深入理解和运用MySQL。 总的来说,"mysql-5.5.27-winx64.zip"是一个适合初学者和...

    MySQL绿色版

    MySQL绿色版是一个便携式的MySQL数据库管理系统,它无需安装即可使用,特别适合于开发者进行快速测试、调试或者在没有权限安装完整版本的环境下操作数据库。这个绿色版包含了易语言源码,这是一种中国本土的编程语言...

    PHP Apache 和 MySQL 网页开发初步(PDF)

    3. 学习MySQL数据库的基本操作,如创建数据库、表,以及SQL查询语句; 4. 了解如何使用PHP连接MySQL数据库,进行数据的CRUD(创建、读取、更新、删除)操作; 5. 探索面向对象编程在PHP中的应用; 6. 实现用户认证和...

Global site tag (gtag.js) - Google Analytics