关闭

MySQL——MyISAM表级锁

272人阅读 评论(0) 收藏 举报
分类:

锁是计算机协调多个进程或者多个线程并发访问某一个资源的机制。相对其他数据库而言,MySQL数据库的锁比较简单,最显著的特点是不同的引擎持有不同的锁机制。MyISAM和MEMORY存储引擎支持表级锁,DBD引擎支持采用页面锁,但也支持表级锁,InnoDB既支持行级锁也支持表级锁,默认为行级锁。

MySQL三种锁的特性如下:

(1)、表级锁:开销小,加锁快;不会出现死锁;锁定粒度比较大,发生锁冲突的概率最高,并发度最低。

(2)、行级锁:开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度也最高。

(3)、页面锁:开销和加锁时间介于行级锁和表级锁之间;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般。

下面重点介绍行锁和表锁。

(一)、表级锁。

1.1、查询表级锁争用情况;

可以通过查询参数table_locks_waited和table_locks_immediate状态变量来分析系统上的表级锁争夺情况。

       

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

1.2、MySQL表级锁锁模式;

MySQL数据库表级锁的锁模式主要分为两种模式:表共享读锁和表共享写锁。锁模式兼容如下:


从以上表格,我们可以知道,MySQL中存储引擎为MyISAM的读操作不会阻塞其他用户对同一表的读请求,但会阻塞其他用户对同一表的写请求;MyISAM的写操作会阻塞其他用户对同一表的读和写请求。MySIAM存储引擎中,读操作和写操作之间以及写操作和写操作之间都是串行的。如下图20-2的例子可以知道,当一个线程获得一个表写锁后,只有持有写锁的线程才可以对表进行更新操作。其他线程的读写操作都必须等待,直到释放锁为止。

1.3、如何加锁;

MySQL在执行查询语句(select)前,会自动给涉及的所有表进行加锁,在执行更新语句(insert,update,delete)操作前,会自动给涉及的所有表加上写锁,这个过程并不需要用户干预,因此,用户一般不需要直接使用LOCK TABLE命令给MyISAM表自动加锁。一下实例中显示自动加锁,主要方便说明而已。

给MyISAM表显示加锁,一般是在一定程度上模拟事务的操作。实现对某一时间点多个表的一致性读取操作。

例如:有一个订单表orders,其中记录有各订单的总金额total,同时还有一个订单明细表order_detail,其中记录有各订单每一款产品的金额小计subtotal,假设这里需要查询两个表的金额合计是否吻合,需要执行一下两条语句。

select count(total) from orders;

select count(subtotal) from order_detail;

这里如果不给这两个表加上读锁,可能就会出错;在执行第一条语句的过程中,第二张表order_detail可能发生改变。

因此正确的方法是:

Lock tables orders read local,order_detail  read local;

select count(total) from orders;
select count(subtotal) from order_detail;

unlock tables;

特别说明:

(1)、上面的例子中在lock tables 时加了local,表示在满足MyISAM表并发插入条件下,允许其他用户在表尾插入记录。

(2)、在用lock tables 给表显示加锁时,必须同时取得所有涉及表的锁。并且MySQL不支持表升级。也就是说,在给表使用lock tables 后,该session只能访问显示加锁的这些表。不能访问未加锁的表。在自动加锁的情况下,MySQL的MyISAM是一次性获得所有sql中涉及的表锁,这也是MyISAM不会出现死锁的原因。

如下例子中,一个session使用lock tables 命令给表film_text加读锁,这个session可以查询锁定表的数据,但更新和访问其他未锁表都会出现错误;其他session可以查询表中的记录,但是更新就会出现锁等待。





使用lock tables 给表加锁时,必须一次性获取所有的表锁,同时一个表在sql语句中出现多少次,就要通过与sql语句相同的别名锁定多少次。否则也会出错。实例如下:



   1.4、并发插入数据。

mysql中存储引擎为MyISAM时,默认情况下,读和写是串行的,但在一定条件下MyISAM表也支持查询和插入操作的并发操作。

MyISAM存储引擎有一个系统变量concurrent_insert,专门控制其并发插入的行为。值分别为0,1,2。

(1)、concurrent_insert为0时,不允许并发插入。

(2)、concurrent_insert为1时,如果MyISAM表中没有空洞(即表的中间没有被删除的行),MyISAM表允许在读取数据的同时,另一个进程可以在表尾插入数据,这是MySQL的默认设置。

(3)、concurrent_insert为2时,无论MyISAM表是否有空洞,都允许在表尾并发插入数据。

如下例子中,session1获得表的READ LOCK 锁,该线程可以对表进行读取操作,但不能对该表进行更新操作,这里假设表中间不存在空洞。



可以利用存储引擎MyISAM表的并发插入特性来解决应用中对同一表的查询和插入的锁争用。

总结:对MyISAM存储引擎表,将concurrent_insert设置为2,总是运行并发插入数据;同时定期在系统空闲时段执行 

OPTIMIZE  TABLE 语句来整理空间碎片,收回因删除记录而产生的中间空洞。

1.5、MyISAM表的锁调度。

对MyISAM表,读锁和写锁都是互斥的,读写操作可以是串行。那么一个进程请求MyISAM表的读锁,同时另一个进程也请求同一表的写锁,MySQL如何处理这样的情况?答案是写进程先获得锁。不仅如此,即使读请求先获得锁等待队列,写请求后到,写锁也会插入到读锁之前!这是因为MySQL认为写请求比读请求更重要,这也是MyISAM表不适合大量更新操作和查询操作应用的原因。因为大量的更新操作会造成查询操作很难获得读锁,从而可能永远堵塞,这样的情况非常糟糕,还好MyISAM表,可以通过一些参数设置来调节MyISAM表锁的调度。

(1)、通过设置启动参数low-priority-updates,使得MySQL的MyISAM引擎默认给予都请求优先权利。

(2)、通过执行命令SET  LOW_PRIORITY_UPDATES=1,使得该连接发出的更新请求优先级降低。

(3)、通过指定INSERT 、UPDATE、DELETE语句的LOE_PRIORITY属性,降低该语句的优先级。


另外MySQL对于MyISAM存储引擎,提供了一个折中的办法来调节读写冲突。即给系统参数max_write_lock_count设置一个合适的值,当表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获取锁的机会。

总结语:对于一些需要长时间运作查询的应用,如果给予读锁计划比较多,也会造成写锁的进程饿死!因此,在应用中,尽量避免长时间运行查询操作,不要总想使用一条sql语句解决问题,这种看似比较巧妙的sql语句,往往比较复杂,运行时间长,在可能的情况下,可以使用中间表等措施对sql语句进行分解,是的每一个查询可以很快完成。减少锁冲突。如果负责语句不可避免,尽量安排在数据库比较空闲的时间段执行,比如一些定期统计可以安排在晚间处理。


0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

MySQL数据库锁机制之MyISAM引擎表锁和InnoDB行锁详解

MySQL中的锁概念 Mysql中不同的存储引擎支持不同的锁机制。比如MyISAM和MEMORY存储引擎采用的表级锁,BDB采用的是页面锁,也支持表级锁,InnoDB存储引擎既支持行级锁,也支持表级锁...
  • hsd2012
  • hsd2012
  • 2016-04-10 17:23
  • 9107

mysql查询更新时的锁表机制分析(只介绍了MYISAM)

为了给高并发情况下的mysql进行更好的优化,有必要了解一下mysql查询更新时的锁表机制。 一、概述 MySQL有三种锁的级别:页级、表级、行级。 MyISAM和MEMORY存储引擎采...
  • u010942020
  • u010942020
  • 2016-07-16 18:16
  • 1974

针对MyISAM锁表的解决方案(也可应用于表锁定)

最近服务器上经常出现mysql进程占CPU100%的情况,使用show processlist命令后,看到出现了很多状态为LOCKED的sql。使用show status like ‘table%’检...
  • e421083458
  • e421083458
  • 2013-12-26 00:38
  • 6909

MySQL高级九——MyISAM表锁(共享读锁)

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中,数据也是一种供许多用户共享的资源。如何保证数据访问的一致性、有效性是所有数据库的一个问题,锁冲突也是影响数据库并发访问性能的一个重要的因...
  • u013308496
  • u013308496
  • 2016-01-10 20:16
  • 393

MySQL 中MyISAM 表级锁和InnoDB 行级锁的实现特点

重点介绍了MySQL 中MyISAM 表级锁和InnoDB 行级锁的实现特点,并讨论了两种 存储引擎经常遇到的锁问题和解决办法。 对于MyISAM 的表,主要讨论了以下几点:        ...
  • zhang434
  • zhang434
  • 2014-03-05 22:06
  • 1894

关于mysql中myisam中表级锁

mysql为myisam类型提供了表级别锁定。 允许多个线程同时读取数据,比如select之间,不需要锁等待。按个排队就行。 但是如果碰到更新操作。比如update就会排斥其他查询...
  • u010703523
  • u010703523
  • 2015-05-05 15:10
  • 463

(5)mysql优化之MyISAM表锁

概述  MyISAM存储引擎只支持表锁,mysql的表锁有两种模式:读锁和写锁。他们的兼容关系是(对myisam的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写操作*)和(对myisa...
  • pursuing0my0dream
  • pursuing0my0dream
  • 2015-06-03 18:12
  • 581

MySQL 针对 MyISAM 表锁的解决方案

最近服务器上经常出现mysql进程占CPU100%的情况,使用show processlist命令后,看到出现了很多状态为LOCKED的sql。使用show status like 'table%'检...
  • climb_up
  • climb_up
  • 2013-10-01 18:11
  • 693

mysql 学习记录(十八)--MyISAM表锁

一、理论: 1.mysql的myisam和memory引擎采用的是‘表级锁’。bdb存储引擎采用的是页面锁,但也支持表级锁。innodb存储引擎既支持行级锁也支持表级锁,但默认情况下采用行级锁。 2....
  • bwshqh
  • bwshqh
  • 2015-10-27 22:08
  • 757

mysql MyISAM表锁

mysql、MyISAM、表锁
  • niclascage
  • niclascage
  • 2015-08-25 18:17
  • 400
    个人资料
    • 访问:75842次
    • 积分:1067
    • 等级:
    • 排名:千里之外
    • 原创:47篇
    • 转载:6篇
    • 译文:0篇
    • 评论:1条
    最新评论