欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

每天进步一点点MySQL锁_MySQL

程序员文章站 2022-05-17 21:01:18
...

一、 锁

MySQL对MyISAM和MEMORY引擎实现行表级锁,对BDB存储引擎进行页级锁,对InnDB存储引擎表进行行行级锁。

按照粒度分:从大到小(MySQL仅支持表级锁,行锁需要存储引擎完成;所有引擎都有自己锁策略)

表锁:锁定整张表,开销小,加锁快;不会出现死锁;锁定粒度大,发生锁冲突概率最高,并发度最低。

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

行锁:锁定一个行。开销大,加锁慢;会出现死锁;锁定粒度介于表锁和行锁之间,并发度一般。

一般来说,表锁适合以查询为主,只有少量按索引条件爱你更新数据的应用,如web应用。

行级锁适合大量按索引条件并发更新少量不同数据,同时又有并发查询能力的应用,如一些在线事务系统(OLTP)

1. 锁语句

手动加锁:lock tables 表名 [read|write]

给t9表上只读锁

mysql>lock table t9 read;

Query OK, 0 rows affected (0.00 sec)

查看锁

mysql> show global status like"table_locks%";

+-----------------------+-------+

| Variable_name | Value |

+-----------------------+-------+

| Table_locks_immediate | 122 | 发生表锁定操作, 但表锁定后马上释放

| Table_locks_waited | 0 | 发生表锁定,并因此具有锁等待

+-----------------------+-------+

2 rows in set (0.00 sec)

给t9表上写锁

mysql>lock table t9 write;

Query OK, 0 rows affected (0.00 sec)

解锁表

mysql>unlock tables;

Query OK, 0 rows affected (0.00 sec)

2. MyISAM表锁

MyISAM存储引擎只支持表锁。

查看表级锁的征用情况

mysql> show status like 'table%';

+----------------------------+-------+

| Variable_name | Value |

+----------------------------+-------+

| Table_locks_immediate | 1258 |

|Table_locks_waited | 0 | 锁等待

| Table_open_cache_hits | 99 |

| Table_open_cache_misses | 1 |

| Table_open_cache_overflows | 0 |

+----------------------------+-------+

5 rows in set (0.00 sec)

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

mysql>lock table emp read local

Query OK, 0 rows affected (0.00 sec)

如果在locktables 时加了local,作用是在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾插入记录。

mysql>select * from emp1;

ERROR 1100 (HY000): Table 'emp1' was notlocked with LOCK TABLES

在使用locktables 给表加锁时,必须同事取得所有涉及表的锁,并且MySQL不支持锁升级。也就是说,如果是读锁,只能执行查询操作,不能执行更新操作。

mysql>update emp set store_id=30 where id=25;

ERROR 1099 (HY000): Table 'emp' was lockedwith a READ lock and can't be updated

并且不能通过别名进行访问,所以需要对别名也要加锁。

mysql>select a.id from emp a;

ERROR 1100 (HY000): Table 'a' was notlocked with LOCK TABLES

并发插入

MyISAM表的读和写是串行的,但是在一定条件下,MyISAM表也会支持查询和插入操作的并发进行。

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

当为0时:不允许并发插入。

当为1时:如果MyISAM表中没有空洞,MyISAM允许在一个进程读取表,另一个进程从表尾插入记录。(默认)无论MyISAM表中有没有空洞都允许在表尾并发插入记录。

MyISAM调度锁

MyISAM存储引擎的读写锁是互斥的,如果一个进程请求某个MyISAM表的读锁,同时另一个进程请求同一个表的写锁,那么MySQL会让写进程先获得锁。不仅如此,即使读请求先到锁等待队列,写请求后到,写锁也会插入到读锁请求之前。这是因为MySQL认为写请求比一般的读请求重要。因此,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。这种情况非常糟糕!

幸好我们可以通过一些设置来调节MyISAM的调度行为。

通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求,以有线的权利。

通过执行命令SETLOW_PRIORITY_UPDATES=1,使该链接发出的更新请求优先级降低。

通过指定 insert、update、delete语句的LOW_PRIORITY属性,降低该语句的优先级。

并且也可以通过给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁到达这个值后,MySQL就暂时将写请求的优先级降低,给读进程一个获得锁的机会。

这里强调一点:一个需要长时间的运行的查询操作,也会使写进程“饿死”!因此要尽量避免长时间查询操作。