本文共 9609 字,大约阅读时间需要 32 分钟。
MyISAM
和MEMORY支持表锁
Innodb
既支持行锁,也支持表锁,默认行锁
检查table_locks_waited
和 table_locks_immediate
状态变量分析
table_locks_waited 的值越高,则说明存在严重的表级锁的争用情况
MySQL的表锁有两种模式
锁模式的兼容如下表
是否兼容 | 请求none | 请求读锁 | 请求写锁 |
---|---|---|---|
当前处于读锁 | 是 | 是 | 否 |
当前处于写锁 | 是 | 否 | 否 |
可见,对MyISAM表的读操作,不会阻塞其他用户对同一表的读请求,但会阻塞对同一表的写请求;
对MyISAM表的写操作,则会阻塞其他用户对同一表的读和写请求; MyISAM表的读和写操作之间,以及写和写操作之间是串行的!(当某一线程获得对一个表的写锁后,只有持有锁的线程可以对表进行更新操作.其他线程的读、写操作都会等待,直到锁被释放为止)
对于 MyISAM 引擎
select
前,会自动给涉及的所有表加 读不需要用户直接显式用lock table
命令
对于给MyISAM显式加锁,一般是为了在一定程度上模拟事务操作,实现对某一个时间点多个表一致性读取
订单表 - orders
记录各订单的总金额total
订单明细表 - order_detail
记录各订单每一产品的金额小计subtotal
假设我们需要检查这两个表的金额合计是否相符,可能就需要执行如下两条SQL
[图片上传失败…(image-3017e3-1547370332969)]这时,如果不先给这两个表加锁,就可能产生错误的结果;
因为第一条语句执行过程中,order_detail
表可能已经发生了改变. 因此,正确写法应该如下 [图片上传失败…(image-8081d7-1547370332969)] session1 | session2 |
---|---|
获得表 film_text 的读锁 lock table film_text read; | |
可select * from film_text | 可select * from film_text |
不能查询没有锁定的表 :select * from film | 可查询/更新未锁定的表: select * from film |
插入或更新锁定表会提示错误 update…from film_text | 更新锁定表会等待 update…from film_text |
释放锁 unlock tables | 等待 |
获得锁,更新成功 |
##2.3 tips
当使用lock tables
时,不仅需要一次锁定用到的所有表 且同一表在SQL语句中出现多少次,就要通过与SQL语句中别名锁多少次 lock table actor read
会提示错误
select a.first_name.....
需要对别名分别锁定
lock table actor as a read,actor as b read;
在一定条件下,MyISAM
也支持并发插入和读取
控制其并发插入的行为,其值分别可以为
可以利用MyISAM
的并发插入特性,来解决应用中对同表查询和插入的锁争用
concurrent_insert
系统变量设为2,总是允许并发插入; 同时,通过定期在系统空闲时段执行OPTIONMIZE TABLE语句来整理空间碎片,收到因删除记录而产生的中间空洞 删除操作
不会重整整个表,只是把 行 标记为删除,在表中留下空洞
MyISAM
的读和写锁互斥,读操作串行的
MyISAM
表的读锁,同时另一个进程也请求同表的写锁,MySQL如何处理呢? 写进程先获得锁!!! 不仅如此,即使读进程先请求先到锁等待队列,写请求后到,写锁也会插到读请求之前!!!这是因为MySQL认为写请求一般比读请求重要
MyISAM
表不适合有大量更新 / 查询
操作应用的原因 大量的更新操作会造成查询操作很难获得读锁,从而可能永远阻塞 幸好,我们可以通过一些设置来调节MyISAM
的调度行为
low-priority-updates
使MyISAM引擎默认给予读请求以优先权利SET LOW_PRIORITY_UPDATES=1
使该连接发出的更新请求优先级降低LOW_PRIORITY
属性 降低该语句的优先级虽然上面3种方法都是要么更新优先,要么查询优先,但还是可以用其来解决查询相对重要的应用(如用户登录系统)中,读锁等待严重的问题
另外,MySQL也提供了一种折中的办法来调节读写冲突;
即给系统参数max_write_lock_count
设置一个合适的值; 当一个表的读锁达到这个值后,MySQL便暂时将写请求的优先级降低,给读进程一定获得锁的机会 InnoDB与MyISAM的最大不同有两点
行级锁和表级锁本来就有许多不同之处,另外,事务的引入也带来了一些新问题
一组SQL语句组成的逻辑处理单元
相对于串行处理来说,并发事务处理能大大增加数据库资源的利用率,提高数据库系统的事务吞吐量,从而可以支持可以支持更多的用户
但并发事务处理也会带来一些问题,主要包括以下几种情况
在并发事务的问题中,“更新丢失”通常应该是完全避免的;
但防止更新丢失,并不能单靠数据库事务控制器来解决,需要应用程序对要更新的数据加必要的锁来解决,因此,防止更新丢失应该是应用的责任
“脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性
问题,必须由数据库提供一定的事务隔离机制来解决
加锁
,防止其他事务对数据进行修改不加任何锁
,通过一定机制生成一个数据请求时间点的一致性数据快照
,并用这个快照来提供一定级别(语句级或事务级)的一致性读取. 从用户的角度,好像是数据库可以提供同一数据的多个版本,因此,这种技术叫做数据多版本并发控制(MultiVersion Concurrency Control,MVCC),也经常称为多版本数据库数据库的事务隔离级别越严格,并发副作用越小,但付出的代价也越大
因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”矛盾为了解决“隔离”与“并发”的矛盾,ANSI SQL定义了4种隔离级别
隔离级别/读数据一致性及允许的并发副作用 | 读数据一致性 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|---|
未提交读(Read uncommitted) | 最低级别,只能保证不读取物理上损坏的数据 | 是 | 是 | 是 |
已提交度(Read committed) | 语句级 | 否 | 是 | 是 |
可重复读(Repeatable read) | 事务级 | 否 | 否 | 是 |
可序列化(Serializable) | 最高级别,事务级 | 否 | 否 | 否 |
如果发现争用比较严重,如
Innodb_row_lock_waits
和Innodb_row_lock_time_avg
的值比较高
进一步观察发生锁冲突的表,数据行等,并分析锁争用的原因
默认情况每15秒会向日志中记录监控的内容;
如果长时间打开会导致.err文件变得非常巨大; 所以确认原因后,要删除监控表关闭监视器,或者通过使用–console选项来启动服务器以关闭写日志功能InnoDB支持以下两种类型的行锁
MySQL InnoDB默认行级锁
行级锁都是基于索引的,若一条SQL语句用不到索引是不会使用行级锁的,会使用表级锁把整张表锁住
为了允许行/表锁共存,实现多粒度锁机制,InnoDB还有两种内部使用的意向锁(Intention Locks)
这两种意向锁都是表锁
当前锁/是否兼容/请求锁 | X | IX | S | IS |
---|---|---|---|---|
X | 冲突 | 冲突 | 冲突 | 冲突 |
IX | 冲突 | 兼容 | 冲突 | 兼容 |
S | 冲突 | 冲突 | 兼容 | 兼容 |
IS | 冲突 | 兼容 | 兼容 | 兼容 |
如果一个事务请求的锁模式与当前锁兼容,InnoDB就请求的锁授予该事务;
反之,如果两者两者不兼容,该事务就要等待锁释放意向锁是InnoDB自动加的,不需用户干预.
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及及数据集加排他锁(X); 对于普通SELECT语句,InnoDB不会加任何锁.对于SELECT语句,可以通过以下语句显式地给记录加读/写锁
共享锁(S)
排他锁(X)
共享锁语句主要用在需要数据依存关系时确认某行记录是否存在;
并确保没有人对这个记录UPDATE或DELETE. 但如果当前事务也需要对该记录进行更新,则很有可能造成死锁; 对于锁定行记录后需要进行更新操作的应用,应该使用排他锁语句.session_1 | session_2 |
---|---|
set autocommit=0,select * from actor where id =1 | set autocommit=0,select * from actor where id =1 |
当前seesion对id为1的记录加入共享锁 select * from actor where id =1 lock in share mode | |
其他seesion仍然可以查询,并对该记录加入 select * from actor where id =1 lock in share mode | |
当前session对锁定的记录进行更新,等待锁 update。。。where id=1 | |
当前session对锁定记录进行更新,则会导致死锁退出 update。。。where id=1 | |
获得锁,更新成功 |
session_1 | session_2 |
---|---|
set autocommit=0,select * from actor where id =1 | set autocommit=0,select * from actor where id =1 |
当前seesion对id为1的记录加入for update 共享锁 select * from actor where id =1 for update | |
可查询该记录select *from actor where id =1,但是不能再记录共享锁,会等待获得锁select *from actor where id =1 for update | |
更新后释放锁 update。。。 commit | |
其他session,获得锁,得到其他seesion提交的记录 |
行锁是通过给索引上的索引项加锁来实现
如果没有索引,InnoDB将通过隐藏的聚簇索引来对记录加锁行锁实现特点意味着:
如果不通过索引条件检索数据,那么Innodb将对表的所有记录加锁,和表锁一样
当我们用范围条件而不是相等条件检索数据,并请求共享或排他锁时,InnoDB会给符合条件的已有数据的索引项加锁;
对于键值在条件范围内但并不存在的记录,叫做“间隙(GAP)”,InnoDB也会对这个“间隙”加锁,这种锁机制就是所谓的间隙锁(Next-Key锁).举例来说,假如emp表中只有101条记录,其empid的值分别是1,2,…,100,101,下面的SQL:
InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁; 也会对empid
大于101
(这些记录并不存在)的“间隙”加锁 间隙锁的目的
相等条件来访问更新数据
,避免使用范围条件.对于InnoDB,在绝大部分情况下都应该使用行锁
因为事务
,行锁
往往是我们选择InnoDB的理由 但在个别特殊事务中,也可以考虑使用表锁
当然,应用中这两种事务不能太多,否则,就应该考虑使用MyISAM
在InnoDB下 ,使用表锁要注意
LOCK TALBES
虽然可以给InnoDB
加表锁 表锁不是由InnoDB
引擎层管理的,而是由其上一层MySQL Server负责; 仅当autocommit=0、innodb_table_lock=1(默认设置)
,InnoDB 引擎层才知道MySQL加的表锁,MySQL Server才能感知InnoDB加的行锁; 这种情况下,InnoDB才能自动识别涉及表锁的死锁 否则,InnoDB将无法自动检测并处理这种死锁LOCK TALBES
对InnoDB
锁时要注意,要将autocommit
设为0,否则MySQL不会给表加锁 事务结束前,不要用UNLOCK TALBES
释放表锁,因为它会隐式地提交事务 COMMIT或ROLLBACK不能释放用LOCK TALBES
加的表锁,必须用UNLOCK TABLES释放表锁,正确的方式见如下语句MyISAM表锁是deadlock free的,这是因为MyISAM总是一次性获得所需的全部锁,要么全部满足,要么等待,因此不会出现死锁
但在InnoDB中,除单个SQL组成的事务外,锁是逐步获得的,这就决定了InnoDB发生死锁是可能的
发生死锁后,InnoDB一般都能自动检测到,并使一个事务释放锁并退回,另一个事务获得锁,继续完成事务
通常来说,死锁都是应用设计的问题,通过调整业务流程、数据库对象设计、事务大小、以及访问数据库的SQL语句,绝大部分都可以避免
下面就通过实例来介绍几种死锁的常用方法。
相同的顺序
访问表事先对数据排序
,保证每个线程按固定的顺序来处理记录直接申请排他锁,而不应该先申请共享锁
可重复读
下,如果两个线程同时对相同条件记录用SELECT...ROR UPDATE
加排他写锁 在没有符合该记录情况下,两个线程都会加锁成功 程序发现记录尚不存在,就试图插入一条新记录,如果两个线程都这么做,就会出现死锁 这种情况下,将隔离级别改成READ COMMITTED,就可以避免问题SELECT...FOR UPDATE
判断是否存在符合条件的记录,没有 -> 插入记录; 此时,只有一个线程能插入成功,另一个线程会出现锁等待. 当第1个线程提交后,第2个线程会因主键重出错,但虽然这个线程出错了,却会获得一个排他锁!这时如果有第3个线程又来申请排他锁,也会出现死锁. 对于这种情况,可以直接做插入操作,然后再捕获主键重异常,或者在遇到主键重错误时,总是执行ROLLBACK释放获得的排他锁如果出现死锁,可以用SHOW INNODB STATUS命令来确定最后一个死锁产生的原因和改进措施。
共享读锁和排他写锁
之间,以及排他写锁之间
互斥,即读写串行MyISAM
允许查询/插入并发,可利用这一点来解决应用中对同一表查询/插入的锁争用问题MyISAM
默认的锁调度机制是写优先,这并不一定适合所有应用,用户可以通过设置LOW_PRIPORITY_UPDATES
参数或在INSERT、UPDATE、DELETE语句中指定LOW_PRIORITY
选项来调节读写锁的争用MyISAM
表可能会出现严重的锁等待,可以考虑采用InnoDB表来减少锁冲突在了解InnoDB的锁特性后,用户可以通过设计和SQL调整等措施减少锁冲突和死锁
尽量使用较低的隔离级别
精心设计索引,并尽量使用索引访问数据,使加锁更精确,从而减少锁冲突的机会。
选择合理的事务大小,小事务发生锁冲突的几率也更小
给记录集显式加锁时,最好一次性请求足够级别的锁。比如要修改数据的话,最好直接申请排他锁,而不是先申请共享锁,修改时再请求排他锁,这样容易产生死锁。
不同的程序访问一组表时,应尽量约定以相同的顺序访问各表,对一个表而言,尽可能以固定的顺序存取表中的行。这样可以大减少死锁的机会。
尽量用相等条件访问数据,这样可以避免间隙锁对并发插入的影响。
不要申请超过实际需要的锁级别;除非必须,查询时不要显示加锁。
对于一些特定的事务,可以使用表锁来提高处理速度或减少死锁的可能
点击查看更多内容
本文原创发布于慕课网 ,转载请注明出处,谢谢合作
作者:JavaEdge 链接:https://www.imooc.com/article/273571 来源:慕课网 本文原创发布于慕课网 ,转载请注明出处,谢谢合作