mysql 事物锁

https://www.cnblogs.com/jukan/p/5670950.html     

 mysql中到事物是指 一个事物（一条语句，或者多条语句）从开始到执行结束到过程，这个事物要么所有语句能够成功执行，如果期间有一条语句执行不成功，就可以使用rollback命令对前面已经执行了的语句进行回滚，让它们恢复到原来到状态。这可以保证数据到一致性。从这里我们可以看出，事物中包含锁，但是锁不包含事物，因为事物除了能够让自己访问共享资源外，还支持回滚，而锁只能达到独享资源的功能。事务有如下属性，又称事务的ACID属性

原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的。

 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

银行转帐就是事务的一个典型例子。

锁分类及其考察性能点：      

开销、加锁速度、死锁、粒度、并发性能

=表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。

=行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低, 并发度也最高。

=页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

从上述特点可见，很难笼统地说哪种锁更好，只能就具体应用的特点来说哪种锁更合适！仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。mysql中， MyISAM和MEMORY存储引擎采用的是表级锁， InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，同事它还支持事物。

下文首先说InnoDB中的事物锁

InnoDB事物锁

事物开始：

begin/start transaction       ---> 手工方式开启事物，紧接着的sql语句会加上锁。

set session autocommit = on/off  ---> 设置事务是否自动开启，如果为off，则在输入sql语句之后，需要再输入commit才会执行。

commit/rollback    ------> 提交事物或者回滚。

mysql> begin; //开始一个事物
mysql> update students_locker set age=20 where name = 'a3'; //更新一行
mysql> select * from students_locker; 
//show出来，如果在另一个session中此时show默认的设置下是看不见的。要看的见需要修改事物隔离级别。
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  1 | a1   |   18 |    1 |
|  2 | a2   |   19 |    2 |
|  3 | a3   |   20 |    1 |
+----+------+------+------+
mysql> rollback;  //回滚事物
mysql> select * from students_locker; //再次show，发现a3的age没有变成20
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  1 | a1   |   18 |    1 |
|  2 | a2   |   19 |    2 |
|  3 | a3   |   12 |    1 |
+----+------+------+------+
mysql>

如果通过 set session autocommit = on/off 方式，可以用命令 show variables like 'autocommit'; 实时查看autocommit的值。如果我们不手动设置其为off，则它一直是事务的，所以可以在session链接开始的时候就设置其为off，这样就不用频繁的开启关闭事务。

事务的隔离性

当多个session同时开启事务，而且它们的事务又会操作同一行的数据时，其中一个事务是否能够看到另一个事务的修改？这依赖于我们设置的隔离级别。标准的有4中事务隔离级别：

4种事务隔离级别比较

数据一致性及允许 的并发副作用隔离级别	读数据一致性	脏读	不可重复读	幻读
		一个事务更改未完成， 另一个事务就可以看到已经修改了的数据	一个事务中多次读取一个数据，数据的值不一样	一个事务内，两次查询期间，另一个事务对共享数据进行了增删操作，这个事务前后两次查看到到数据不一样
未提交读（Read uncommitted）	最低级别，只能保证 不读取物理上损坏的数据	会发生	会发生	会发生
已提交读（Read committed）	语句级别	不会发生	会发生	会发生
可重复读（Repeated read）	事务级	不会发生	不会发生	会发生
可序列化（Serializable）	最高级别，事务级	不会发生	不会发生	不会发生

 

 

 

 

 

 

 

 

 

 

 

InnoDB默认是Repeated read，可以通过语句 show global variables like "tx_isolation"; 查看当前事务隔离级别(mysql8已经改为show variables like 'transaction_isolation';） 

通过语句 set session transaction isolation level repeatable read;;（参数可以为：Read uncommitted，Read committed，Repeatable，Serializable）

通过语句 show status like 'innodb_row_lock%'; 可以查看锁竞争到程度：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 0     |
| Innodb_row_lock_time_avg      | 0     |//这个值比较高时
| Innodb_row_lock_time_max      | 0     |
| Innodb_row_lock_waits         | 0     | //这个值比较高时
+-------------------------------+-------+
上面的两个值比较高时，说明锁竞争激烈，可以通过下方链接查看如何详细的分析锁竞争
https://www.cnblogs.com/jukan/p/5670950.html

注意：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁。因此：

1.当用事物修改某一列时，没有在这一列上设置索引，则InnoDB自动退化为表级锁！！！

2. 当一个事物使用了范围时（比如 select name from student_locker where id > 1 and id < 4;）这会把id为2和3的行锁定，另一个事物不能操作id为这期间的行，如果操作了会被阻塞，直到前面的事物提交之后才可运行！

 

锁类型：

悲观锁：总是认为对同一行有并发操作。select * from student_locker where id = 2 for update; 这条语句后面的for update使得其它事物不能够对id=2这一行进行查询，直到它释放锁。这会降低性能。

乐观锁：总是任务对同一行的操作没有并发操作。万一出现并发了，它使用另一种方法规避。添加一个版本号列version，在update之前，先把此行数据查询处理，记录它的版本号数据（比如是0），update的时候 使用类似语句：update student_locker set age = 3, version = version + 1 where version = 0 and id = 2;  这样当执行后面的语句的时候，如果它之前查询到的version = 0；因为前面一个事物已经将version+1，所以后面一天语句找不到 where version = 0 and id = 2 这条语句了，就更新不了，然后我们rollback.

读时锁表：（在没有行锁的数据库中使用比较多） Lock table goods READ; 执行这条语句后所以的事务（包括自己）都只能读这个表。在锁表的事物中insert、update会失败，在其它session的事务中执行insert、update会发送阻塞。锁住表的session可以用unlock tables解锁。

写时锁表：Lock table goods WRITE; 这样只有锁住的session才能更新，查询表，其它session读、写都会阻塞。同样使用unlock tables;解锁。

InnoDB有两种行级锁：

共享锁（S） : 其它事务可以同时访问这一行，它会阻止其它试图修改这一行数据的操作（排它锁）。

排它锁 (X）: 在更新某一行的数据时，会先尝试获取这一行的排它锁，获取到了就能够对该行进行修改。

另外，InnoDB还有两种表级锁：

意向共享锁（IS）：事务在给一个数据行加共享锁前必须先取得该表的IS锁。

意向排它锁（IX）：事务在给一个数据行加排他锁前必须先取得该表的IX锁。

意向锁是InnoDB自动加的，不需用户干预。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁。用户也可以显示在sql语句后面添加 LOCK IN SHARE MODE（共享锁）， FOR UPDATE（排它锁）。