您当前的位置： 首页 > 学无止境 > 心得笔记 网站首页心得笔记

mysql锁机制与事务机制实现

发布时间：2018-04-30 10:25:02编辑：雪饮阅读（）

事务：

BEGIN或START TRANSACTION；显式地开启一个事务；

COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；

ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；

默认情况下若一个事务中对某对象的操作(包含普通的查询)没有提交前，若其它会话对该对象drop则会形成阻塞，直到事务提交后，drop才能成功。

关于ACID

         A原子性:

         A想要从自己的帐户中转1000块钱到B的帐户里。那个从A开始转帐，到转帐结束的这一个过程，称之为一个事务。在这个事务里，要做如下操作：

1. 从A的帐户中减去1000块钱。如果A的帐户原来有3000块钱，现在就变成2000块钱了。

2. 在B的帐户里加1000块钱。如果B的帐户如果原来有2000块钱，现在则变成3000块钱了。

如果在A的帐户已经减去了1000块钱的时候，忽然发生了意外，比如停电什么的，导致转帐事务意外终止了，而此时B的帐户里还没有增加1000块钱。那么，我们称这个操作失败了，要进行回滚。回滚就是回到事务开始之前的状态，也就是回到A的帐户还没减1000块的状态，B的帐户的原来的状态。此时A的帐户仍然有3000块，B的帐户仍然有2000块。

我们把这种要么一起成功（A帐户成功减少1000，同时B帐户成功增加1000），要么一起失败（A帐户回到原来状态，B帐户也回到原来状态）的操作叫原子性操作。

如果把一个事务可看作是一个程序,它要么完整的被执行,要么完全不执行。这种特性就叫原子性

         C一致性：

         A有100块钱，转1块钱给另外一个帐户，还有99块钱，在整个事务执行过程中，钱数总是100块，不会变，这就是一致性。

         I隔离性:

         事务执行过程相互隔离，不会相互之间产生影响（这只是美好的愿望）。意思是多个事务并发执行的话，结果应该与多个事务串行执行效果是一样的。但并发情况下需要考虑性能，所以就需要在隔离性上做些手脚（妥协），也就是制定不同的隔离级别达到不同的并发性能。
         D持久性:

         事务每一次的执行结果都应该持久化（存储）到数据库中（磁盘数据）。想想除了select，其他的update/delete/insert都会产生这样的结果，持久化在应用场景中是必须的，除非你写了假接口。哈哈。

并发带来的数据库操作问题:

脏读：

脏读又称无效数据的读出，是指在数据库访问中，事务T1将某一值修改，然后事务T2读取该值，此后T1因为某种原因撤销对该值的修改，这就导致了T2所读取到的数据是无效的。

幻读：

幻读是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，比如这种修改涉及到表中的“全部数据行”。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入“一行新数据”。那么，以后就会发生操作第一个事务的用户发现表中还存在没有修改的数据行，就好象发生了幻觉一样.一般解决幻读的方法是增加范围锁RangeS，锁定检索范围为只读，这样就避免了幻读。

不可重复读:

这是由于查询时系统中其他事务修改的提交而引起的。比如事务T1读取某一数据，事务T2读取并修改了该数据，T1为了对读取值进行检验而再次读取该数据，便得到了不同的结果。

 

关于隔离级别

四种隔离级别即:读未提交、读已提交、可重复读、序列化读(又称串行读)

四种隔离级别对于并发带来的数据库操作问题呈现关系如下图

 



可重复读的隔离模式下update不会产生幻象读

设置隔离级别

如设置当前会话隔离级别为读未提交

set tx_isolation='READ-UNCOMMITTED';

 

InnoDB存储引擎中的锁



意向排它锁 for update的使用

FOR UPDATE 走的是IX锁(意向排它锁)。

其他的事务是可以读取的。但是不能写入或者更新。 
我们举一个项目中常见的应用场景吧。 
比如有一张表 他有三个字段。id代表商品id ,name代表商品名字,count代表该商品数量。 
我们为了显示抢购的时候显示该商品还剩余多少件。我们会手动的上锁。锁住id为1的商品。比如id为1的商品名字是某型号的mac pro。count代表了该商品还剩余多少件。当并发量很大的情况下,商品数量自减的值可能是不准确的。 
所以当我们在一个事务中对count字段进行修改的时候,其他的事务应该是只能读取指定id的count值。而不能写入或者update。这个时候for update的作用就是在此刻体现的。 

顺带一提的是,当选中某一个行的时候,如果是通过主键id选中的。那么这个时候是行级锁。 
其他的行还是可以直接insert 或者update的。如果是通过其他的方式选中行,或者选中的条件不明确包含主键。这个时候会锁表。其他的事务对该表的任意一行记录都无法进行插入或者更新操作。只能读取。

意向共享锁LOCK IN SHARE MODE的使用

LOCK IN SHARE MODE走的是IS锁(意向共享锁)，即在符合条件的rows上都加了共享锁，这样的话，其他session可以读取这些记录，也可以继续添加IS锁，但是无法修改这些记录直到你这个加锁的session执行完成(否则直接锁等待超时)。

查看数据库中锁定详情

当数据库中由于sql语句造成了锁定的时候，可以通过如下命令查看锁定详情

select * from information_schema.innodb_locks;

测试Record Lock

 

行锁直接加在索引记录上面，锁住的是key。



这两个的测试结果是相同的都是多个会话间相互锁等待的影响，和概念一样。

测试Gap Lock

间隙锁，在索引记录间隙上的锁，或者是第一条索引记录之前和最后一条索引记录之后上的间隙锁。

锁定索引记录间隙，确保索引记录的间隙不变。

特别说明：

举例来说，假如emp表中只有101条记录，其empid的值分别是 1,2,...,100,101

是一个范围条件的检索，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

再如，条件为empid>9 and empid<20，此时有两种锁的情况

(1)若有大于empid为20的记录存在，则此时另外一个会话insert一个比大于empid为20的值更大的数据不会锁

(2)无论边界empid为20是否存在都会锁

输出innodb引擎的状态信息

show engine innodb status;

该命令执行后可以获取到innodb引擎的一些状态信息包含事务等。

SHOW INNODB STATUS 中的很多统计值都是每秒更新一次的，如果你打算利用这些统计值的话，那么最好统计一段时间内的结果。

ddl操作与dml操作

DML(data manipulation language)：

它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

 

DDL(data definition language)：

DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表(TABLE)的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用

 

mdl锁



会话1的事务和会话2的事务一个是dml操作一个ddl操作，这样就给会话2造成了mdl锁。

死锁：

死锁一般在并发时出现的频率较高，一般的两个会话抢夺锁资源的时候就会造成死锁。

死锁日志开关

innodb_print_all_deadlocks变量是控制死锁日志是否记录的开关

查看死锁日志开关状态

show variables like '%dead%';

查找诊断锁常用命令

#查看innodb引擎

show engine innodb status\G;

#查看锁

SELECT r.trx_id waiting_trx_id, r.trx_mysql_thread_id waiting_thread, r.trx_query waiting_query, b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread, b.trx_query blocking_query FROM information_schema.innodb_lock_waits w INNER JOIN information_schema.innodb_trx b ON b.trx_id= w.blocking_trx_id INNER JOIN information_schema.innodb_trx r ON r.trx_id= w.requesting_trx_id;

 

#select * from information_schema.innodb_trx表中，几个最常用的字段：

trx_id:InnoDB存储引擎内部唯一的事务ID

trx_state:当前事务的状态

trx_started:事务的开始时间。

trx_wait_started:事务等待开始的时间。

trx_mysql_thread_id:Mysql中的线程ID,SHOW PROCESSLIST显示的结果。

trx_query：事务运行的sql语句。

 

#select * from information_schema.innodb_locks表中，几个最常用的字段：

lock_id:锁的ID。

lock_trx_id:事务ID。

lock_mode:锁的模式。

lock_type:锁的类型，表锁还是行锁。

lock_table:要加锁的表。

lock_index:锁的索引。

lock_space:InnoDB存储引擎表空间的ID号。

lock_page:被锁住的页的数量。若是表锁，则该值为NULL。

lock_rec:被锁住的行的数量。若是表锁，则该值为NULL。

lock_data:被锁住的行的主键值。当是表锁时，该值为NULL。

 

#select * from information_schema.innodb_lock_waits表中，几个最常用的字段： requesting_trx_id:申请锁资源的事务ID。

requesting_lock_id:申请的锁的ID。

 

blocking_trx_id:阻塞的锁的ID。