mysql锁表机制及相关优化

释放双眼,带上耳机,听听看~!

(该文章为方便自己查阅,也希望对大家有所帮助,转载于互联网)
1、 锁机制
当前MySQL支持 ISAM, MyISAM, MEMORY (HEAP) 类型表的表级锁,BDB 表支持页级锁,InnoDB 表支持行级锁。
很多时候,可以通过经验来猜测什么样的锁对应用程序更合适,不过通常很难说一个锁比别的更好,这全都要依据应用程序来决定,不同的地方可能需要不同的锁。
想要决定是否需要采用一个支持行级锁的存储引擎,就要看看应用程序都要做什么,其中的查询、更新语句是怎么用的。例如,很多的web应用程序大量的做查询,很少删除,主要是基于索引的更新,只往特定的表中插入记录。采用基本的MySQL的MyISAM 表就很合适了。
MySQL中对表级锁的存储引擎来说是释放死锁的。避免死锁可以这样做到:在任何查询之前先请求锁,并且按照请求的顺序锁表。
1)MySQL中用于 WRITE(写) 的表锁的实现机制如下:
如果表没有加锁,那么就加一个写锁。
否则的话,将请求放到写锁队列中。
2)MySQL中用于 READ(读) 的表锁的实现机制如下:
如果表没有加写锁,那么就加一个读锁。
否则的话,将请求放到读锁队列中。
当锁释放后,写锁队列中的线程可以用这个锁资源,然后才轮到读锁队列中的线程程。
这就是说,如果表里有很多更新操作的话,那么 Select 必须等到所有的更新都完成了之后才能开始。
现在版本的MySQL可以通过状态变量table_locks_waited 和table_locks_immediate 来分析系统中的锁表争夺情况:
mysql> show status like 'table%';
+———————–+———-+
| Variable_name | Value |
+———————–+———-+
| Table_locks_immediate | 15184994 |
| Table_locks_waited | 20108 |
+———————–+———-+
2 rows in set (0.00 sec)
在 MySQL 3.23.7(在Windows上是3.23.25)以后,在 MyISAM 表中只要没有冲突的Insert 操作,就可以无需使用锁表自由地并行执行Insert 和Select 语句。也就是说,可以在其它客户端正在读取MyISAM 表记录的同时时插入新记录。如果数据文件的中间没有空余的磁盘块的话,就不会发生冲突了,因为这种情况下所有的新记录都会写在数据文件的末尾(当在表的中间做删除或者更新操作时,就可能导致空洞)。当空洞被新数据填充后,并行插入特性就会自动重新被启用了。
mysql>lock tables real_table write, insert_table write;
mysql> insert into real_table select * from insert_table;
mysql> truncate table insert_table;
mysql> unlock tables; (自己斟酌)
InnoDB 使用行级锁,BDB 使用页级锁。对于 InnoDB 和 BDB 存储引擎来说,是可能产生死锁的。这是因为 InnoDB 会自动捕获行锁,BDB 会在执行 SQL 语句时捕获页锁的,而不是在事务的开始就这么做。
行级锁的优点有: (innodb)
在很多线程请求不同记录时减少冲突锁。
事务回滚时减少改变数据。
使长时间对单独的一行记录加锁成为可能。
行级锁的缺点有:
比页级锁和表级锁消耗更多的内存。
当在大量表中使用时,比页级锁和表级锁更慢,因为他需要请求更多的锁资源。
当需要频繁对大部分数据做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 操作,但是没有任何写表。
2、 锁表
为了能有快速的锁,MySQL除了 InnoDB 和 BDB 这两种存储引擎外,所有的都是用表级锁(而非页、行、列级锁)。
对于 InnoDB 和 BDB 表,MySQL只有在指定用 LOCK TABLES 锁表时才使用表级锁。在这两种表中,建议最好不要使用 LOCK TABLES,因为 InnoDB 自动采用行级锁,BDB 用页级锁来保证事务的隔离。
如果数据表很大,那么在大多数应用中表级锁会比行级锁好多了,不过这有一些陷阱。
表级锁让很多线程可以同时从数据表中读取数据,但是如果另一个线程想要写数据的话,就必须要先取得排他访问。正在更新数据时,必须要等到更新完成了,其他线程才能访问这个表。
更新操作通常认为比读取更重要,因此它的优先级更高。不过最好要先确认,数据表是否有很高的 Select 操作,而更新操作并非很‘急需’。
表锁锁在一个线程在等待,因为磁盘空间满了,但是却需要有空余的磁盘空间,这个线程才能继续处理时就有问题了。这种情况下,所有要访问这个出问题的表的线程都会被置为等待状态,直到有剩余磁盘空间了。
表锁在以下设想情况中就不利了:
一个客户端提交了一个需要长时间运行的 Select 操作。
其他客户端对同一个表提交了 Update 操作,这个客户端就要等到 Select 完成了才能开始执行。
其他客户端也对同一个表提交了 Select 请求。由于 Update 的优先级高于 Select,所以 Select 就会先等到 Update 完成了之后才开始执行,它也在等待第一个 Select 操作。
下列所述可以减少表锁带来的资源争夺:
让 Select 速度尽量快,这可能需要创建一些摘要表。
启动 mysqld 时使用参数 –low-priority-updates。这就会让更新操作的优先级低于 Select。这种情况下,在上面的假设中,第二个 Select 就会在 Insert 之前执行了,而且也无需等待第一个Select 了。
可以执行 SET LOW_PRIORITY_UpdateS=1 命令,指定所有的更新操作都放到一个指定的链接中去完成。
用 LOW_PRIORITY 属性来降低 Insert,Update,Delete 的优先级。
用 HIGH_PRIORITY 来提高 Select 语句的优先级。
从MySQL 3.23.7 开始,可以在启动 mysqld 时指定系统变量 max_write_lock_count 为一个比较低的值,它能强制临时地提高表的插入数达到一个特定值后的所有 Select 操作的优先级。它允许在 WRITE 锁达到一定数量后有 READ 锁。
当 Insert 和 Select 一起使用出现问题时,可以转而采用 MyISAM 表,它支持并发的Select 和 Insert 操作。
当在同一个表上同时有插入和删除操作时,Insert DELAYED 可能会很有用。
当 Select 和 Delete 一起使用出现问题时,Delete 的 LIMIT 参数可能会很有用。
执行 Select 时使用 SQL_BUFFER_RESULT 有助于减短锁表的持续时间.
以下是MySQL锁的一些建议:
只要对同一个表没有大量的更新和查询操作混在一起,目前的用户并不是问题。
执行 LOCK TABLES 来提高速度(很多更新操作放在一个锁之中比没有锁的很多更新快多了)。将数据拆分开到多个表中可能也有帮助。
当MySQL碰到由于锁表引起的速度问题时,将表类型转换成 InnoDB 或 BDB 可能有助于提高性能。
******************************************************************************************************************************************************************
*********************************************************************************
*********************************************************************************
锁是计算机协调多个进程或线程并发访问某一资源的机制 。在数据库中,除传统的计算资源(如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状态变量来分析系统上的表锁定争夺:
Java代码 收藏代码
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:
Java代码 收藏代码
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
这时,如果不先给两个表加锁,就可能产生错误的结果,因为第一条语句执行过程中,order_detail表可能已经发生了改变。因此,正确的方法应该是:
Java代码 收藏代码
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表获得读锁:
Java代码 收藏代码
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)但是通过别名访问会提示错误:
Java代码 收藏代码
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)需要对别名分别锁定:
Java代码 收藏代码
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)按照别名的查询可以正确执行:
Java代码 收藏代码
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语句做一定的“分解”,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行。

给TA打赏
共{{data.count}}人
人已打赏
安全运维

OpenSSH-8.7p1离线升级修复安全漏洞

2021-10-23 10:13:25

安全运维

设计模式的设计原则

2021-12-12 17:36:11

个人中心
购物车
优惠劵
今日签到
有新私信 私信列表
搜索