`
xgbjmxn
  • 浏览: 270439 次
  • 性别: Icon_minigender_1
  • 来自: 新乡
社区版块
存档分类
最新评论

mysql查询更新时的锁表机制分析

    博客分类:
  • db
阅读更多
mysql查询更新时的锁表机制分析
为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。

一、概述

MySQL有三种锁的级别:页级、表级、行级。
MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。

MySQL这3种锁的特性可大致归纳如下:

表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

二、MyISAM表锁

MyISAM存储引擎只支持表锁,是现在用得最多的存储引擎。

1、查询表级锁争用情况

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

mysql> show status like ‘table%’;
+-----------------------+----------+
| Variable_name | Value |
+-----------------------+----------+
| Table_locks_immediate | 76939364 |
| Table_locks_waited | 305089 |
+-----------------------+----------+
2 rows in set (0.00 sec)
Table_locks_waited的值比较高,说明存在着较严重的表级锁争用情况。

2、MySQL表级锁的锁模式

MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁。

所以对MyISAM表进行操作,会有以下情况:
a、对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
b、对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。

下面通过例子来进行验证以上观点。数据表gz_phone里有二百多万数据,字段id,phone,ua,day。现在同时用多个客户端同时对该表进行操作分析。
a、当我用客户端1进行一个比较长时间的读操作时,分别用客户端2进行读和写操作:
client1:

mysql>select count(*) from gz_phone group by ua;
75508 rows in set (3 min 15.87 sec)
client2:

select id,phone from gz_phone limit 1000,10;
+------+-------+
| id | phone |
+------+-------+
| 1001 | 2222 |
| 1002 | 2222 |
| 1003 | 2222 |
| 1004 | 2222 |
| 1005 | 2222 |
| 1006 | 2222 |
| 1007 | 2222 |
| 1008 | 2222 |
| 1009 | 2222 |
| 1010 | 2222 |
+------+-------+
10 rows in set (0.01 sec)

mysql> update gz_phone set phone=’11111111111′ where id=1001;
Query OK, 0 rows affected (2 min 57.88 sec)
Rows matched: 1 Changed: 0 Warnings: 0
说明当数据表有一个读锁时,其它进程的查询操作可以马上执行,但更新操作需等待读锁释放后才会执行。

b、当用客户端1进行一个较长时间的更新操作时,用客户端2,3分别进行读写操作:
client1:

mysql> update gz_phone set phone=’11111111111′;
Query OK, 1671823 rows affected (3 min 4.03 sec)
Rows matched: 2212070 Changed: 1671823 Warnings: 0
client2:

mysql> select id,phone,ua,day from gz_phone limit 10;
+----+-------+-------------------+------------+
| id | phone | ua | day |
+----+-------+-------------------+------------+
| 1 | 2222 | SonyEricssonK310c | 2007-12-19 |
| 2 | 2222 | SonyEricssonK750c | 2007-12-19 |
| 3 | 2222 | MAUI WAP Browser | 2007-12-19 |
| 4 | 2222 | Nokia3108 | 2007-12-19 |
| 5 | 2222 | LENOVO-I750 | 2007-12-19 |
| 6 | 2222 | BIRD_D636 | 2007-12-19 |
| 7 | 2222 | SonyEricssonS500c | 2007-12-19 |
| 8 | 2222 | SAMSUNG-SGH-E258 | 2007-12-19 |
| 9 | 2222 | NokiaN73-1 | 2007-12-19 |
| 10 | 2222 | Nokia2610 | 2007-12-19 |
+----+-------+-------------------+------------+
10 rows in set (2 min 58.56 sec)
client3:

mysql> update gz_phone set phone=’55555′ where id=1;
Query OK, 1 row affected (3 min 50.16 sec)
Rows matched: 1 Changed: 1 Warnings: 0
说明当数据表有一个写锁时,其它进程的读写操作都需等待读锁释放后才会执行。

3、并发插入

原则上数据表有一个读锁时,其它进程无法对此表进行更新操作,但在一定条件下,MyISAM表也支持查询和插入操作的并发进行。

MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。
a、当concurrent_insert设置为0时,不允许并发插入。
b、当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
c、当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。

4、MyISAM的锁调度

由于MySQL认为写请求一般比读请求要重要,所以如果有读写请求同时进行的话,MYSQL将会优先执行写操作。这样MyISAM表在进行大量的更新操作时(特别是更新的字段中存在索引的情况下),会造成查询操作很难获得读锁,从而导致查询阻塞。

我们可以通过一些设置来调节MyISAM的调度行为:

a、通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
b、通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
c、通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。

上面3种方法都是要么更新优先,要么查询优先的方法。这里要说明的就是,不要盲目的给mysql设置为读优先,因为一些需要长时间运行的查询操作,也会使写进程“饿死”。只有根据你的实际情况,来决定设置哪种操作优先。这些方法还是没有从根本上同时解决查询和更新的问题。

在一个有大数据量高并发表的mysql里,我们还可采用另一种策略来进行优化,那就是通过mysql主从(读写)分离来实现负载均衡,这样可避免优先哪一种操作从而可能导致另一种操作的堵塞。下面将用一个篇幅来说明mysql的读写分离技术。

分享到:
评论

相关推荐

    MySQL高级 锁机制

    MySQL的锁机制是数据库管理系统中用于控制并发操作的重要机制,对于多用户环境下的数据一致性、事务隔离性和系统性能有着至关重要的影响。本文将深入探讨MySQL的高级锁机制,包括表锁和行锁,并提供一些优化建议。 ...

    MySQL:锁机制.pdf

    本知识点详细解读了MySQL锁机制的定义、分类、特点以及具体的应用案例。 1. 锁的定义和分类 在数据库系统中,锁是用来控制多个用户或进程对同一数据资源进行访问的同步机制。它解决的是多个操作同时对同一资源进行...

    MySQL中的行级锁,表级锁,页级锁1

    MySQL作为一种广泛应用的关系型数据库管理系统,提供了多种锁机制以应对不同的并发控制需求。其中,行级锁、表级锁和页级锁是MySQL中最为常见的锁类型。本文将详细解析这三种锁机制的特点、应用场景以及优化策略,...

    mysql锁表1

    总结来说,理解并有效地管理MySQL中的锁机制是数据库管理员和开发人员的重要技能。通过监控和调整锁相关的状态变量,以及明智地使用`LOCK TABLES`和`UNLOCK TABLES`,我们可以降低并发问题,提高系统性能,并确保...

    MySQL锁详解

    在锁等待的情况下,可以通过查询表级锁争用情况的相关状态变量来分析。例如,使用show status like "table%"命令可以查看Table_locks_immediate和Table_locks_waited的值,这两个变量分别表示立即获取锁的次数和等待...

    mysql高级优化查询

    通过上述对 MySQL 高级优化查询的深入解析,我们不仅了解了查询缓存的原理及其工作流程,还探讨了 MySQL 的内部机制,包括存储引擎、锁机制、索引管理和查询优化器等关键组件。此外,还介绍了一些额外的优化策略,如...

    ElasticSearch基于Mysql热更新IK词典项目

    综上所述,这个项目旨在解决Elasticsearch在处理中文时的动态词典更新问题,结合MySQL数据库的高效管理和更新能力,实现了词典的实时热更新,从而提高了系统的灵活性和适应性。在实施过程中,我们需要充分理解...

    mysql锁分析(csdn)————程序.pdf

    MySQL 锁机制是 MySQL 数据库中用于管理并发访问的机制, MySQL 锁机制主要有三种类型:表锁、行锁、页锁。每种锁机制都有其特点和应用场景。 表锁 表锁是 MySQL 中最基本的锁机制,表锁分为共享锁和排他锁。共享...

    MySQL悲观锁总结和实践

    悲观锁的主要实现方式依赖于数据库提供的锁机制,特别是事务隔离级别中的行级锁或表级锁。只有数据库层面提供的锁机制才能确保数据访问的排他性,避免多线程或多事务环境下的并发冲突。若仅在应用层面上实现加锁机制...

    MySQL锁机制与用法分析

    本文实例讲述了MySQL锁机制与用法。分享给大家供大家参考,具体如下: MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。比如,MyISAM和MEMORY存储引擎采用的是表级锁;BDB存储引擎采用的是...

    MySQL_源码分析

    3. **并发控制与锁机制**:在多用户环境中,并发控制是确保数据一致性的关键。源码分析可以揭示MySQL是如何实现行级锁、表级锁、死锁检测等机制的,这对于避免并发问题、提高系统吞吐量非常重要。 4. **日志与恢复...

    阿里大牛何sir 深入MySQL加锁处理分析

    在深入分析MySQL加锁处理之前,首先需要了解MySQL数据库的基本架构以及锁机制的相关概念。MySQL是一个支持插件式存储引擎的数据库系统,其中InnoDB存储引擎因其支持事务处理和外键约束而被广泛使用。本文将重点讨论...

    MySQL加锁处理分析@何登成1

    本文将从 MVCC 机制出发,讨论 Snapshot Read 和 Current Read 两种读取方式的区别,然后深入分析 MySQL 加锁机制的实现细节,包括 Cluster Index、2PL、Isolation Level 等方面。最后,本文还将对死锁原理和分析...

    浅析MYSQL中的并发操作与锁定

    MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁。因此,对MyISAM表进行操作,会有以下情况: * 对MyISAM表的读操作(加读...

    五款常用mysql slow log分析工具

    选择MySQL慢查询日志分析工具时,应根据具体需求考虑: - **数据深度**:若需深入分析慢查询原因,推荐mysqlsla,其丰富的报表和定制选项能满足高级需求。 - **使用便捷性**:mysqldumpslow作为官方工具,无需额外...

    MYSQL数据库优化与表设计

    比如,在00:00:00时进行数据插入或更新操作,应设计合理的锁机制来防止数据重复插入导致的错误。 2. **数据流向分析**:绘制系统内的数据流向图,可以帮助开发者识别潜在的性能瓶颈点。通过对数据流进行细致分析,...

    mysql 悲观锁与乐观锁的理解及应用分析

    不要把他们与mysql中提供的锁机制(表锁,行锁,排他锁,共享锁)混为一谈。 一、悲观锁 顾名思义,就是对于数据的处理持悲观态度,总认为会发生并发冲突,获取和修改数据时,别人会修改数据。所以在整个数据处理过程...

Global site tag (gtag.js) - Google Analytics