Mysql的锁问题：

Mysql的锁问题

1.1锁的概述

​ Mysql锁的机制比较简单不同的存储引擎支持不同的锁机制MyISAM和MEMORY存储引擎支持表级锁DBD支持页面锁但是它也支持表级锁InnoDB既支持行级锁也支持表级锁但是默认情况下支持行级锁

​ 表级锁开销小

1.2MyISAM表锁

1.2.1 查询表锁争用情况

	mysql> SHOW STATUS LIKE 'table%';

​ ±---------------------------±------+
​ | Variable_name | Value |
​ ±---------------------------±------+
​ | Table_locks_immediate | 7 |
​ | Table_locks_waited | 0 |
​ | Table_open_cache_hits | 0 |
​ | Table_open_cache_misses | 0 |
​ | Table_open_cache_overflows | 0 |
​ ±---------------------------±------+
Table_locks_waited 的值比较高就说明存在着比较严重的表级锁竞争

1.2.2 MYSQL表级锁的锁模式

​ 表共享读锁Table Read Lock

​ 表独占写锁Table Write Lock

​ MySAM 表的读操作不会塞其他用户对同一表的读请求但会阻塞对同一表的写请求;对 MyISAM 表的写操作则会阻塞其他用户对同一表的读和写操作
MyISAM 表锁的读操作与写操作之间以及写操作之间是串行的当一个线程获得对一个表的写锁后只有持有锁的线程可以对表进行更新操作。其他线程的读操作都会等待直到锁被释放为止。

1.2.3 如何加表锁

​ MyISAM 在执行查询语句(SELECT)前会自动给涉及的所有表加读锁在执行更新操作(UPDATE、DELETE、INSERT 等)前会自动给涉及的表加写锁这个过程并不需要用户干预因此用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。

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

​ 什么是显示加锁指的是把 Lock table table_name read 的加锁命令写出来

​ 代码示例

​ 1、当我用命令行链接数据库时候模拟session1用户用Navicat链接作为session2用户

​ session1进行写锁定session2进行读操作可以看第一截图session明显是阻塞在那了。

当session1立即释放锁后session2立马拿到了读操作得结果

锁之间的读写阻塞的关系大家可以在找个环境多试试一下这里就不过多的演示了。

​

1.2.4 并发插入Councurrent Insert

​ MyISAM 表的读和写是串行的,但这是就总体而言的。在一定条件下,MyISAM表也支持查询和插入操作的并发进行。MyISAM 存储引擎有一个系统变量 concurrent insert专门用以控制其并发插人的行为其值分别可以为 0、1或 2。

​ 当concurrent insert设置为0时不允许并发插入

​ 当concurrent insert设置为1时如果MyISAM表中没有空洞(即表的中间没有被册除的行)MyISAM 允许在一个进程读表的同时另一个进程从表尾插入记录。这也是MySOI的默认设置or Auto。
​ 当concurrent insert设置为 2时无论MyISAM表中有没有空洞都允许在表尾并发插入记录。

​ 可以利用 MyISAM 存储引擎的并发插人特性来解决应用中对同一表查询和插人的锁争用例如将 concurrent_insert 系统变量设为 2总是允许并发插人;同时通过定期在系统空闲时执行 OPTIMIZE TABLE 语句来整理空间碎片收回因删除记录而产生的中间空洞。

1.2.5 锁调度

​ MyISAM 存储引擎的读锁和写锁是互斥的读写操作是串行的。那么一个进程请求某个MyISAM 表的读锁同时另一个进程也请求同一表的写锁MySQL 如何处理呢?答案是写进程先获得锁。不仅如此即使读请求先到锁等待队列写请求后到写锁也会插到读锁请求之前!这是因为 MySQL 认为写请求一般比读请求要重要。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因因为大量的更新操作会造成查询操作很难获得读锁从而可能永远阻塞。不过我们可以通过一些设置来调节 MyISAM 的调度行为。

​ ● 通过指定启动参数 low-priority-updates使MyISAM引擎默认给予读请求以优先的权利。
​ ● 通过执行命令SETLOW PRIORITY UPDATES=1使该连接发出的更新请求优先级降低。
​ ● 通过指定INSERT、UPDATE、DELETE 语句的 LOW_PRIORITY 属性降低该语句的优先级。
虽然上面 3 种方法都是要么更新优先要么查询优先的方法但还是可以用其来解决查询相对重要的应用(如用户登录系统 )中读锁等待严重的问题。

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

max_write_lock_count设置一个合适的值当一个表的读锁达到这个值后MySQL 就暂时将写请求的优先级降低给读进程一定获得锁的机会。

​ 这里还要强调一点:一些需要长时间运行的查询操作也会使写进程“饿死”! 因此应用中应尽量避免出现长时间运行的询操作不要总想用一条 SELECT 语来解决问题因为这种看似巧妙的 SQL 语句往往较复杂执行时间较长在可能的情况下可以通过使用中间表等措施对 SQL 语句做一定的“分解”使每一步查询都能在较短时间完成从而减少锁冲突。如果复杂查询不可避免应尽景安排在数据库空闲时段执行比如一些定期统计可以安排在夜间执行。