mysql的锁

WuYiLong原创大约 5 分钟mysqlmysql的锁

MYISAM存储引擎(支持表级锁)

锁的模式(两种)

  • 共享锁 对 MyISAM 表的读操作(共享锁),不会阻塞其他进程对同一表的读请求,但会阻塞对其的写请求。当读锁释放后,才会执行其他进程的写操作。

  • 排他锁 对 MyISAM 表的写操作(排他锁),会阻塞其他进程对同一表的读写操作,当该锁释放后,才会执行其他进程的读写操作。

怎么加锁

  • 隐式 在一般情况下,执行读操作都会加上共享锁,写操作都会加上排他锁

  • 显式

一般情况下,是为了模拟事务的操作,实现对某一时间点多个表的一致性读取

LOCK tables orders read local,order_detail read local;
SELECT SUM(total) FROM orders;
SELECT SUM(subtotal) FROM order_detail;
Unlock tables;

上面加local的作用是:在满足MyISAM表并发插入条件的情况下,允许其他用户在表尾插入记录

优化

  • 设置系统变量concurrent_insert的值 concurrent_insert=0 时,不允许并发插入。 concurrent_insert=1 时,如果 MyISAM 表中没有空洞(即表中没有被删除的行),允许一个进程读表时,另一个进程向表的尾部插入记录(MySQL 默认设置)

注:空洞是行记录被删除以后,只是被标记为“已删除”其存储空间没有被回收,也就是说没有被物理删除。

因为空间长度问题,删除以后的物理空间不能被新的记录所使用,从而形成了空洞。

解决方法: 通过定期在系统空闲时段执行OPTIMIZE TABLE 语句来整理空间碎片,收集因删除记录而产生的中间空洞

当concurrent_insert设置为2时,无论MyISAM表中有没有空洞,都允许在表尾插入记录,都允许在表尾并发插入记录

  • 在 my.cnf 的配置方法 [mysqld] low-priority-updates 通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。 通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。

  • 设置系统参数max_write_lock_count的值,解决并发时锁的冲突

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

  • 尽量不要写长的sql,复杂的sql会增加查询时间,锁的冲突就多

INNODB存储引擎(支持行级锁)

对于碎片化的空洞处理

alter table mct_merchant engine='InnoDB';  // 删除碎片化的空洞 

锁的模式

  • 共享锁(s):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁。
  • 排他锁(X):允许获取排他锁的事务更新数据,阻止其他事务取得相同的数据集共享读锁和排他写锁。
  • 意向共享锁(IS):事务打算给数据行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
  • 意向排他锁(IX):事务打算给数据行加排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。

对于事务来说可以显示的加上共享锁和排他锁:

共享锁(S):SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE

排他锁(X):SELECT * FROM table_name WHERE ... FOR UPDATE

InnoDB行锁实现方式

InnoDB行锁是通过索引上的索引项来实现的,InnoDB这种行锁实现特点意味者:只有通过索引条件检索数据,InnoDB才会使用行级锁,否则,InnoDB将使用表锁!

在实际应用中,要特别注意InnoDB行锁的这一特性,不然的话,可能导致大量的锁冲突,从而影响并发性能。

间隙锁(Next-Key锁)

对一个范围内的记录加锁的时候,我们称之为间隙锁。

Select * from table_gapwhere id > 10 for update;
这是一个范围条件的检索,InnoDB 不仅会对符合条件的 id 值为 10 的记录加锁,会对 id 大于 10 的“间隙”加锁,即使大于 10 的记录不存在,例如 12,13。

锁的查询

表级锁可以通过两个变量的查询:

show status like 'table%';

Table_locks_immediate,产生表级锁的次数。 Table_locks_waited,数显表级锁而等待的次数。

table_locks_waited 的值越高,则说明存在严重的表级锁的争用情况 行级锁可以通过下面几个变量查询:

show status like 'InnoDB_row_lock%';

Innodb_row_lock_current_waits,当前正在等待的数量。 Innodb_row_lock_time(重要),从系统启动到现在锁定总时长。 Innodb_row_lock_time_avg(重要),每次等待所花平均时间。 Innodb_row_lock_time_max,从系统启动到现在等待最长的一次花费时间。 Innodb_row_lock_waits(重要),从系统启动到现在总共等待的次数

设置监控

Create table innodb_monitor (i int) engine=innodb 通过建立这个表就启动了innodb_monitor,监控的结果并不会记录到这个表中,而是记录到了mysql的err日志中,如果我们想监控更我的关于innodb的锁信息还可更进一步的建立表create table innodb_lock_monitor (i int) engine=innodb 这样在日志中会加入更多的锁信息,如果要关闭监控只要简单的删除这两个表就可以了.Drop table innodb_monitor; drop table innodb_lock_monitor;

show ENGINE innodb status;

// 停止监控
drop table innodb_monitor
上次编辑于:
贡献者: wuyilong