关闭

MySQL——MyISAM表级锁

216人阅读 评论(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网站的观点或立场
    个人资料
    • 访问:58315次
    • 积分:980
    • 等级:
    • 排名:千里之外
    • 原创:47篇
    • 转载:6篇
    • 译文:0篇
    • 评论:1条
    最新评论