`
zxl7049
  • 浏览: 30165 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

mysql myisam 锁表问题

阅读更多
锁是计算机协调多个进程或线程并发访问某一资源的机制 。在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。 从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。本章我们着重讨论MySQL锁机制的特点,常见的锁问题,以及解决MySQL锁问题的一些方法或建议。

MySQL锁概述
相对其他数据库而言,MySQL的锁机制比较简单,其最显著的特点是不同的存储引擎支持不同的锁机制。
比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);
BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;
InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁。
MySQL这3种锁的特性可大致归纳如下。
     表级锁:开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突的概率最高,并发度最低。
     行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。
     页面锁:开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。
从上述特点可见,很难笼统地说哪种锁更好,只能就具体应用的特点来说哪种锁更合适!仅从锁的角度来说:表级锁更适合于以查询为主,只有少量按索引条件更新数据的应用,如Web应用;而行级锁则更适合于有大量按索引条件并发更新少量不同数据,同时又有并发查询的应用,如一些在线事务处理(OLTP)系统。这一点在本书的“开发篇”介绍表类型的选择时,也曾提到过。下面几节我们重点介绍MySQL表锁和 InnoDB行锁的问题,由于BDB已经被InnoDB取代,即将成为历史,在此就不做进一步的讨论了。





MyISAM表锁

MyISAM存储引擎只支持表锁,这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高,MySQL才开始开发基于事务的存储引擎,后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎(实际 InnoDB是单独的一个公司,现在已经被Oracle公司收购)。但是MyISAM的表锁依然是使用最为广泛的锁类型。本节将详细介绍MyISAM表锁的使用。
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺:
mysql> show status like 'table%';   
+-----------------------+-------+   
| Variable_name         | Value |   
+-----------------------+-------+   
| Table_locks_immediate | 2979  |   
| Table_locks_waited    | 0     |   
+-----------------------+-------+  

mysql> show status like 'table%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Table_locks_immediate | 2979  |
| Table_locks_waited    | 0     |
+-----------------------+-------+

如果Table_locks_waited的值比较高,则说明存在着较严重的表级锁争用情况。


MySQL表级锁的锁模式
MySQL的表级锁有两种模式:表共享读锁(Table Read Lock)和表独占写锁(Table Write Lock)。锁模式的兼容性如表20-1所示。

可见,对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;对 MyISAM表的写操作,则会阻塞其他用户对同一表的读和写操作;MyISAM表的读操作与写操作之间,以及写操作之间是串行的!根据如表20-2所示的例子可以知道,当一个线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待,直到锁被释放为止。




如何加表锁
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。在本书的示例中,显式加锁基本上都是为了方便而已,并非必须如此。
给MyISAM表显示加锁,一般是为了在一定程度模拟事务操作,实现对某一时间点多个表的一致性读取。例如,有一个订单表orders,其中记录有各订单的总金额total,同时还有一个订单明细表order_detail,其中记录有各订单每一产品的金额小计 subtotal,假设我们需要检查这两个表的金额合计是否相符,可能就需要执行如下两条SQL:
Select sum(total) from orders;   
Select sum(subtotal) from order_detail;  

Select sum(total) from orders;
Select sum(subtotal) from order_detail;
这时,如果不先给两个表加锁,就可能产生错误的结果,因为第一条语句执行过程中,order_detail表可能已经发生了改变。因此,正确的方法应该是: 
Lock tables orders read local, order_detail read local;   
Select sum(total) from orders;   
Select sum(subtotal) from order_detail;   
Unlock tables;  

Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;
要特别说明以下两点内容。
¡  上面的例子在LOCK TABLES时加了“local”选项,其作用就是在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾并发插入记录,有关MyISAM表的并发插入问题,在后面的章节中还会进一步介绍。
¡  在用LOCK TABLES给表显式加表锁时,必须同时取得所有涉及到表的锁,并且MySQL不支持锁升级。也就是说,在执行LOCK TABLES后,只能访问显式加锁的这些表,不能访问未加锁的表;同时,如果加的是读锁,那么只能执行查询操作,而不能执行更新操作。其实,在自动加锁的情况下也基本如此,MyISAM总是一次获得SQL语句所需要的全部锁。这也正是MyISAM表不会出现死锁(Deadlock Free)的原因。
在如表20-3所示的例子中,一个session使用LOCK TABLE命令给表film_text加了读锁,这个session可以查询锁定表中的记录,但更新或访问其他表都会提示错误;同时,另外一个session可以查询表中的记录,但更新就会出现锁等待。




当使用LOCK TABLES时,不仅需要一次锁定用到的所有表,而且,同一个表在SQL语句中出现多少次,就要通过与SQL语句中相同的别名锁定多少次,否则也会出错!举例说明如下。
(1)对actor表获得读锁:
mysql> lock table actor read;   
Query OK, 0 rows affected (0.00 sec)  

mysql> lock table actor read;
Query OK, 0 rows affected (0.00 sec)
(2)但是通过别名访问会提示错误:
 
mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;   
ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES  

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;
ERROR 1100 (HY000): Table 'a' was not locked with LOCK TABLES

(3)需要对别名分别锁定:
 
mysql> lock table actor as a read,actor as b read;   
Query OK, 0 rows affected (0.00 sec)  

mysql> lock table actor as a read,actor as b read;
Query OK, 0 rows affected (0.00 sec) 
(4)按照别名的查询可以正确执行:
 
mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;   
+------------+-----------+------------+-----------+   
| first_name | last_name | first_name | last_name |   
+------------+-----------+------------+-----------+   
| Lisa       | Tom       | LISA       | MONROE    |   
+------------+-----------+------------+-----------+   
1 row in set (0.00 sec)  

mysql> select a.first_name,a.last_name,b.first_name,b.last_name from actor a,actor b where a.first_name = b.first_name and a.first_name = 'Lisa' and a.last_name = 'Tom' and a.last_name <> b.last_name;
+------------+-----------+------------+-----------+
| first_name | last_name | first_name | last_name |
+------------+-----------+------------+-----------+
| Lisa       | Tom       | LISA       | MONROE    |
+------------+-----------+------------+-----------+
1 row in set (0.00 sec) 

并发插入(Concurrent Inserts)
上文提到过MyISAM表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert,专门用以控制其并发插入的行为,其值分别可以为0、1或2。
        当concurrent_insert设置为0时,不允许并发插入。
        当concurrent_insert设置为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM允许在一个进程读表的同时,另一个进程从表尾插入记录。这也是MySQL的默认设置。
       当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾并发插入记录。
在如表20-4所示的例子中,session_1获得了一个表的READ LOCAL锁,该线程可以对表进行查询操作,但不能对表进行更新操作;其他的线程(session_2),虽然不能对表进行删除和更新操作,但却可以对该表进行并发插入操作,这里假设该表中间不存在空洞。



可以利用MyISAM存储引擎的并发插入特性,来解决应用中对同一表查询和插入的锁争用。例如,将concurrent_insert系统变量设为2,总是允许并发插入;同时,通过定期在系统空闲时段执行 OPTIMIZE TABLE语句来整理空间碎片,收回因删除记录而产生的中间空洞。有关OPTIMIZE TABLE语句的详细介绍,可以参见第18章中“两个简单实用的优化方法”一节的内容。
MyISAM的锁调度
前面讲过,MyISAM存储引擎的读锁和写锁是互斥的,读写操作是串行的。那么,一个进程请求某个 MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理呢?答案是写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插到读锁请求之前!这是因为MySQL认为写请求一般比读请求要重要。这也正是MyISAM表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况有时可能会变得非常糟糕!幸好我们可以通过一些设置来调节MyISAM 的调度行为。
  通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
  通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
  通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
虽然上面3种方法都是要么更新优先,要么查询优先的方法,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题。
另外,MySQL也提供了一种折中的办法来调节读写冲突,即给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。上面已经讨论了写优先调度机制带来的问题和解决办法。这里还要强调一点:一些需要长时间运行的查询操作,也会使写进程“饿死”!因此,应用中应尽量避免出现长时间运行的查询操作,不要总想用一条SELECT语句来解决问题,因为这种看似巧妙的SQL语句,往往比较复杂,执行时间较长,在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。
  • 大小: 27.3 KB
分享到:
评论

相关推荐

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

    "Mysql(MyISAM)的读写互斥锁问题的解决方法" 在Mysql(MyISAM)中,读写互斥锁问题是常见的性能瓶颈之一。为了解决这个问题,需要了解MyISAM的读写机制和锁机制。MyISAM在读操作占主导的情况下是很高效的,但是一旦...

    MySQL锁类型以及子查询锁表问题、解锁1

    锁表操作如`LOCK TABLES tbl_name READ/WRITE;`可以在备份或维护时使用,它会阻止其他用户对指定表进行读写操作,直到执行`UNLOCK TABLES;`为止。 总的来说,理解和正确使用MySQL的锁机制对于优化并发性能和避免...

    MYSQL 解锁与锁表介绍

    比如,MyISAM和MEMORY存储引擎采用的是表级锁(table-level locking);BDB存储引擎采用的是页面锁(page-level locking),但也支持表级锁;InnoDB存储引擎既支持行级锁(row-level locking),也支持表级锁,但...

    mysql的myisam解决并发读写解决方法

    ### MySQL的MyISAM表类型解决并发读写问题 在MySQL数据库系统中,MyISAM是一种非常常用的存储引擎。它以其高效性和简单的结构而在许多场景下得到广泛应用,尤其是在那些读取操作远多于写入操作的应用场景中。然而,...

    mysql事务与锁机制(存储引擎和锁、MyISAM锁机制、InnoDB锁机制、Next-Key锁、Dead-Lock).docx

    #### 二、MyISAM锁机制 **1、基础描述** MyISAM引擎采用表级锁,其特点是在读操作时不会阻止其他线程对同一表的读请求,但在写操作时会阻塞其他线程的读写请求。这种锁机制使得读写操作之间以及写写操作之间呈串行...

    MYSQL锁机制全揭秘

    MyISAM存储引擎采用的是表级锁,加锁速度快、开销小,但并发度最低,容易产生锁冲突。它支持读写锁,读操作不阻塞读操作但会阻塞写操作,而写操作则会阻塞所有读写操作,操作之间是串行的。通过SHOW STATUS命令可以...

    MySQL:锁机制.pdf

    MySQL的表锁分为表共享读锁(TableReadLock)和表独占写锁(TableWriteLock)。表锁的特点是实现简单,开销小,加锁速度快,但在高并发环境下容易发生锁竞争,导致并发性能下降。MyISAM存储引擎偏向于使用表锁,尤其...

    MyISAM 锁1

    同时,Transaction-B试图更新`innodb_lock`表时则没有问题,因为读锁只对被锁定的`myisam_lock`表生效。 接下来,我们看看MyISAM的写锁(WRITE lock)。通过`LOCK TABLES ... WRITE`语句,Transaction-A可以获取写...

    mysql的 myisam引擎锁问题

    比如:myisam和memory存储引擎采用的是表级锁,bdb采用的是页面锁,但也支持表级锁,innodb存储引擎即支持行级锁也支持表级锁,但默认情况下是行级锁。  三种锁的特性大致归纳如下:  (1)表级锁:开销小,加锁...

    MySQL锁详解

    MyISAM和MEMORY存储引擎使用表级锁,BDB存储引擎支持页面锁,同时也可以使用表级锁,而InnoDB存储引擎则同时支持行级锁和表级锁,默认采用行级锁。 表级锁具有开销小,加锁速度快的特点,不会出现死锁,但是锁定...

    MYSQL锁机制

    MyISAM存储引擎是早期MySQL中非常流行的存储引擎之一,它仅支持表级锁。 ##### 查询表级锁争用情况 可以通过MySQL的状态变量`table_locks_waited`和`table_locks_immediate`来评估系统上的表锁定竞争情况。如果`...

    mysql insert锁机制1

    在MySQL的InnoDB存储引擎中,插入操作不仅涉及到行级锁,还可能涉及Gap锁和Next-Key Locks,这些锁机制的设计是为了防止幻读(Phantom Read)和其他并发问题。 1. 数据库版本:文中提到的是MySQL 5.6.27。不同版本...

    2021年MySQL高级教程视频.rar

    17.MySQL高级锁MyISAM表锁查看锁争用情况.avi 18.MySQL高级锁InnoDB行锁介绍及背景知识.avi 18.MySQL高级锁InnoDB行锁类型.avi 19.MySQL高级锁InnoDB行锁基本演示.avi 20.MySQL高级锁InnoDB行锁行锁升级为表锁.avi ...

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

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

    MySQL行级锁、表级锁、页级锁详细介绍

    表级:引擎 MyISAM , 理解为锁住整个表,可以同时读,写不行行级:引擎 INNODB , 单独的一行记录加锁 表级,直接锁定整张表,在你锁定期间,其它进程无法对该表进行写操作。如果你是写锁,则其它进程则读也不允许...

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

    表级锁分为表共享读锁和表独占写锁,MyISAM和MEMORY存储引擎使用表级锁。 页级锁则位于行级锁和表级锁之间,一次锁定相邻的一组记录,平衡了速度和冲突。BDB引擎支持页级锁,其特点在于加锁开销和时间介于表锁和...

    mysql锁表和解锁语句分享

    表级的典型代表引擎为MyISAM,MEMORY以及很久以前的ISAM。 行级的典型代表引擎为INNODB。 -我们实际应用中用的最多的就是行锁。 行级锁的优点如下: 1)、当很多连接分别进行不同的查询时减小LOCK状态。 2)、...

    MySQL锁的用法之表级锁

    MySQL的锁分为表级锁、页级锁与行级锁。表级锁是MySQL中粒度大的一种锁,它实现简单,资源消耗较少,被大部分MySQL引擎支持。常使用的MYISAM与INNODB都支持表级锁定。  表级锁定分为两类,读锁与写锁。读锁是预期...

Global site tag (gtag.js) - Google Analytics