高校二级网站建设意义,嵌入式软件开发文档,wordpress连载小说插件,专门做汽配的网站一、前言#xff08;以下均为读完 高性能Mysql第四版 后的个人理解#xff0c;建议阅读#xff0c;挺不错的#xff09;在写锁机制前先简单贴出mysql InnoDB引擎中的事务特性与隔离级别#xff1a;事务的ACID标准(1)原子性-atomicity#xff1a;一个事务作为一个不可分割…一、前言以下均为读完 高性能Mysql第四版 后的个人理解建议阅读挺不错的在写锁机制前先简单贴出mysql InnoDB引擎中的事务特性与隔离级别事务的ACID标准 (1)原子性-atomicity一个事务作为一个不可分割的工作单元要么全部执行提交成功要么全部执行失败回滚。 (2)一致性-consistency数据库总是从一个一致性状态转换到下一个一致性状态如若一个事务没有被提交成功那么该事务所作的所有修改都不会保存到数据库中。 (3)隔离性-isolation一个事务在最终提交之前其所作的修改对其他事务是不可见的。 (4)持久性-durability一个事务一旦提交其所做的修改将永久保存到数据库即使系统奔溃数据也不会丢失。隔离级别 (1)READ UNCOMMITTED-读未提交该隔离级别事务中可以读取到其他事务未提交的修改可能产生脏读。 (2)READ COMMITTED-读已提交该隔离级别事务只能读取到其他事务提交后的修改属于不可重复读可能产生在同一事务中同一条查询语句产生两种不同的结果且可能产生幻读。 (3)READ COMMITTED-可重复读(InnoDB默认隔离级别)该隔离级别解决了 读已提交 隔离级别的不可重复读问题保证在同一个事务中多次读取相同行数据的结果是一样的但可能产生幻读。 (4)SERIALIZABLE-可串行化强制事务按顺序执行解决事务之间的冲突属于加锁读。二、共享锁与排他锁 1.共享锁简称S锁也称读锁多事务可共享一把锁可读不可写实践证明先获得共享锁的事务可修改数据后续其他事务不可修改。 申请行级共享锁示例select id from table1 where id 1 lock in share mode 2.排他锁简称X锁也称写锁排他锁不可与其他锁共存包括共享锁和排他锁如事务1获取到某记录的排他锁且未释放时那么其他事务在获取该记录的共享锁和排他锁时将被阻塞。 申请行级排他锁示例select id from table1 where id 1 for update updat delete insert语句均会自动申请排他锁 3.普通select语句会不会申请共享锁或排他锁 不会普通select语句属于快照读没有任何锁机制。三、间隙锁 上面说过在InnoDB默认隔离级别可重复读级别下会产生幻读问题实际上InnoDB默认使用了间隙锁策略来防止幻读产生。 什么是幻读当某个事务在读取某个范围内的记录时另外一个事务又在该范围内插入了新的记录当之前的事务再次读取该范围的记录时会产生幻行此时就会产生幻读。 简介 (1)行锁:锁直接加在索引记录上面锁住的是key。 (2)间隙锁:锁定索引记录间隙确保索引记录的间隙不变。间隙锁是针对事务隔离级别为可重复读或以上级别。 (3)行锁和间隙锁组合起来叫Next-Key Lock。 默认情况下InnoDB工作在可重复读隔离级别下并且会以Next-Key Lock的方式对数据行进行加锁这样可以有效防止幻读的发生。Next-Key Lock是行锁和间隙锁的组合当InnoDB扫描索引记录的时候会对索引记录加上行锁再对索引记录两边的间隙加上间隙锁。加上间隙锁之后其他事务就不能在这个间隙修改或者插入记录。 间隙锁在InnoDB的作用就是防止其他事务的插入操作以此防止幻读的发生。 (4)间隙锁产生条件 当使用范围条件去检索唯一索引列未加索引列非唯一索引列并申请共享/排他锁时会申请间隙锁。 当使用等值条件去检索未加索引列非唯一索引列并申请共享/排他锁时会申请间隙锁。 注意若检索条件未加索引sql可能会走聚簇索引的全表扫描进行过滤这时表中每条记录都会加上排他锁但是对于不满足条件的记录会在加锁后立马释放锁最终持有锁的是满足条件的记录。不满足条件的记录会有上锁又释放锁的过程。四、意向锁 InnoDB支持多粒度锁表锁和行锁可同时存在意向锁为表锁中的一种。 1.意向锁分为意向共享锁和意向排他锁 (1)意向共享锁-事务有意向给表中某行申请共享锁。 (2)意向排他锁-事务有意向给表中某行申请排他锁。 示例该事务申请某些行的共享锁之前必须先申请该表的意向共享锁
select id from table1 where id 1 lock in share mode该事务申请某些行的排他锁之前必须先申请该表的意向排他锁
select id from table1 where id 1 for update 2.意向锁的互斥性 (1)首先意向锁是表级锁不与行级锁互斥也就是说意向锁不会与行级的共享/排他锁互斥可以共同存在。 (2)意向共享锁与表级的共享锁互相兼容可共同存在但与表级排他锁互斥。 (3)意向排他锁与表级的共享/排他锁均互斥 示例事务A申请了id1的记录的排他锁并未提交事务此时表table1存在两把锁即表table1上的意向排他锁和id1记录上的行级排他锁
select id from table1 where id 1 for update事务B获取表table1的表级共享锁
LOCK TABLES table1 READ;此时事务 B 检测事务 A 持有 table1 表的意向排他锁就可以得知事务 A 必然持有该表中某些数据行的排他锁那么事务 B 对 table1 表的加锁请求就会被阻塞而无需去检测表中的每一行数据是否存在排他锁极大提高效率。五、死锁 1.死锁是指两个或多个事务相互持有和请求相同资源上的锁产生了循环依赖。当多个事务试图以不同的顺序锁定资源时会导致死锁。当多个事务锁定相同的资源时也可能会发生死锁。例如设想运行下面两个针对主键为stock_iddate的StockPrice表的事务 事务A 事务B 当两个事务同时执行完第一条sql此时互相持有id3和id4的数据行的排他锁而锁并没有被释放当执行第二条sql时双方均永远等待对方释放锁这就导致死锁的产生。 2.如何解决 (1)InnoDB实现了死锁检测和锁超时机制一般来说都能自动检测到并使一个或多个事务释放锁并回滚剩下的事务获得锁继续完成事务。 (2)查看表是否在使用
show open tables where in_use 0
如果查询结果不为空继续。查看数据库当前进程看一下有无正在执行的慢SQL线程
show processlist当前运行的所有事务
SELECT * FROM information_schema.INNODB_TRX当前出现的锁
SELECT * FROM information_schema.INNODB_LOCKs锁等待的对应关系
SELECT * FROM information_schema.INNODB_LOCK_waits看事务表INNODB_TRX里面是否有正在锁定的事务线程看看ID表INNODB_TRX的trx_mysql_thread_id字段是否在show processlist里面的sleep线程中如果是就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了需要手动kill掉。
