MySQL 当中的各种锁(中级篇)

锁是计算机协调多个进程或线程并发访问某一资源的机制。锁保证数据并发访问的一致性、有效性;锁冲突也是影响数据库并发访问性能的一个重要因素。锁是Mysql在服务器层和存储引擎层的的并发控制。
加锁是消耗资源的,锁的各种操作,包括获得锁、检测锁是否是否已解除、释放锁等。

共享锁与排他锁

  • 共享锁(读锁):其他事务可以读,但不能写。
  • 排他锁(写锁) :其他事务不能读取,也不能写。

MySQL 不同的存储引擎支持不同的锁机制,所有的存储引擎都以自己的方式显现了锁机制,服务器层完全不了解存储引擎中的锁实现:
1.MyISAM 存储引擎采用的是表级锁(table-level locking)
2.InnoDB 存储引擎既支持行级锁(row-level locking),也支持表级锁,但默认情况下是采用行级锁
3.默认情况下,表锁和行锁都是自动获得的, 不需要额外的命令

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

MyISAM表级锁模式:
表共享读锁 (Table Read Lock):不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
表独占写锁 (Table Write Lock):会阻塞其他用户对同一表的读和写操作;
MyISAM 表的读操作与写操作之间,以及写操作之间是串行的。当一个线程获得对一个表的写锁后, 只有持有锁的线程可以对表进行更新操作。 其他线程的读、 写操作都会等待,直到锁被释放为止。
默认情况下,写锁比读锁具有更高的优先级:当一个锁释放时,这个锁会优先给写锁队列中等候的获取锁请求,然后再给读锁队列中等候的获取锁请求。
这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因,因为,大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞。同时,一些需要长时间运行的查询操作,也会使写线程“饿死” ,应用中应尽量避免出现长时间运行的查询操作(在可能的情况下可以通过使用中间表等措施对SQL语句做一定的“分解” ,使每一步查询都能在较短时间完成,从而减少锁冲突。如果复杂查询不可避免,应尽量安排在数据库空闲时段执行,比如一些定期统计可以安排在夜间执行)
可以设置改变读锁和写锁的优先级:
通过指定启动参数low-priority-updates,使MyISAM引擎默认给予读请求以优先的权利。
通过执行命令SET LOW_PRIORITY_UPDATES=1,使该连接发出的更新请求优先级降低。
通过指定INSERT、UPDATE、DELETE语句的LOW_PRIORITY属性,降低该语句的优先级。
给系统参数max_write_lock_count设置一个合适的值,当一个表的读锁达到这个值后,MySQL就暂时将写请求的优先级降低,给读进程一定获得锁的机会。

MyISAM加锁方法:
MyISAM 在执行查询语句(SELECT)前,会自动给涉及的表加读锁,在执行更新操作
(UPDATE、DELETE、INSERT 等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
在自动加锁的情况下,MyISAM 总是一次获得 SQL 语句所需要的全部锁,这也正是 MyISAM 表不会出现死锁(Deadlock Free)的原因。

查看表级锁争用情况:
可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁的争夺,如果 Table_locks_waited 的值比较高,则说明存在着较严重的表级锁争用情况:
Mysql当中的各种锁(中级篇)
Table_locks_immediate:能够立即获得表级锁的锁请求次数
Table_locks_waited:不能立即获取表级锁而需要等待的锁请求次数
如果Table_locks_immediate / Table_locks_waited > 5000,最好采用InnoDB引擎,
因为InnoDB是行锁而MyISAM是表锁,对于高并发写入的应用InnoDB效果会好些!

MySQL的nnoDB锁机制
InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁。行级锁与表级锁本来就有许多不同之处,innodb正常的select ID from table where id=1;不会上任何锁,接下来详细讨论InnoDB的锁问题;

一:InnoDB行锁的介绍
共享锁(S):允许一个事务去读一行,阻止其他事务获得相同数据集的排他锁,也就是我读取的行,你不能修改,但是大家都可以读这一行,其他人也可以读表里面的其他数据或者操作其他的行数据;
排他锁(X):允许获得排他锁的事务更新数据,阻止其他事务取得相同数据集的共享读锁和排他写锁。也就是我更新的行,不允许其他的事务读取和更新相同的行;
另外,为了允许行锁和表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks),这两种意向锁都是表锁。
意向共享锁(IS):事务打算给数据行加行共享锁,事务在给一个数据行加共享锁前必须先取得该表的IS锁。
意向排他锁(IX):事务打算给数据行加行排他锁,事务在给一个数据行加排他锁前必须先取得该表的IX锁。
意向锁是InnoDB自动加的,不需用户干预。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);对于普通SELECT语句,InnoDB不会加任何锁;

关于innodb锁机制,实现原理:
InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁! 索引分为主键索引和二级索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了二级索引,MySQL会先锁定该二级索引,再锁定相关的主键索引InnoDB行锁是通过给索引上的索引项加锁来实现的,这一点MySQL与Oracle不同,后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着:只有通过索引条件检索数据,InnoDB才使用行级锁,否则,InnoDB将使用表锁! 索引分为主键索引和二级索引两种,如果一条sql语句操作了主键索引,MySQL就会锁定这条主键索引;如果一条语句操作了二级索引,MySQL会先锁定该二级索引,再锁定相关的主键索引
MySQL 当中的各种锁(中级篇)

关于innodb锁机制需要注意的是:
1)InnoDB行锁是通过给索引项加锁实现的,如果没有索引,InnoDB会通过隐藏的聚簇索引来对记录加锁。也就是说:如果不通过索引条件检索数据,那么InnoDB将对表中所有数据加锁,实际效果跟表锁一样。
2)由于MySQL的行锁是针对索引加的锁,不是针对记录加的锁,所以虽然是访问不同行的记录,但是如果是使用相同的索引键,是会出现锁冲突的。说白了就是,where id=1 for update 会锁定所有id=1的数据行,如果是where id=1 and name=’liuwenhe’ for update,这样会把所有 id=1以及所有name=’liuwenhe’的行都上排它锁;
3)当表有多个索引的时候,不同的事务可以使用不同的索引锁定不同的行,另外,不论是使用主键索引、唯一索引或普通索引,InnoDB都会使用行锁来对数据加锁。
4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL优化器通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,或者饮食转换,或者like百分号在前等等,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。4)即便在条件中使用了索引字段,但是否使用索引来检索数据是由MySQL优化器通过判断不同执行计划的代价来决定的,如果MySQL认为全表扫描效率更高,比如对一些很小的表,它就不会使用索引,或者饮食转换,或者like百分号在前等等,这种情况下InnoDB将使用表锁,而不是行锁。因此,在分析锁冲突时,别忘了检查SQL的执行计划,以确认是否真正使用了索引。

可以通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况:

show status like 'innodb_row_lock%';

MySQL 当中的各种锁(中级篇)
Innodb_row_lock_current_waits:当前锁等待的数量
Innodb_row_lock_time:自系统启动到现在,锁定的总时间,单位:毫秒 ms。
Innodb_row_lock_time_avg:平均锁定的时间,单位:毫秒 ms。
Innodb_row_lock_time_max:最大锁定时间,单位:毫秒 ms。
Innodb_row_lock_waits:自系统启动到现在,锁等待次数,即锁定的总次数。
如果发现锁争用比较严重,如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比较高

本作品采用《CC 协议》,转载必须注明作者和本文链接

胡军

huxiaobai_001
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!