mysql 锁机制与事物_Mysql锁机制与事务

InnoDB 锁机制

InnoDB存储引擎支持行级锁

其大类可以细分为共享锁和排它锁两类

共享锁(S)：允许拥有共享锁的事务读取该行数据。当一个事务拥有一行的共享锁时，另外的事务可以在同一行数据也获得共享锁，但另外的事务无法获得同一行数据上的排他锁

排它锁(X)：允许拥有排它锁的事务修改或删除该行数据。 当一个事务拥有一行的排他锁时，另外的事务在此行数据上无法获得共享锁和排它锁，只能等待第一个事务的锁释放

除了共享锁和排他锁之外， InnoDB也支持意图锁。该锁类型是属于表级锁，表明事务在后期会对该表的行施加共享锁或者排它锁。所以对意图锁也有两种类型：

共享意图锁(IS)： 事务将会对表的行施加共享锁

排他意图锁(IX)： 事务将会对表的行施加排它锁

举例来说select … for share mode语句就是施加了共享意图锁，而select … for update语句就是施加了排他意图锁

InnoDB锁相关系统表

Information_schema.innodb_trx记录了InnoDB中每一个正在执行的事务，包括该事务获得的锁信息，事务开始时间，事务是否在等待锁等信息

TRX_ID InnODB 内部标示每个事务的ID

TRX_WEIGHT 表示该事务的权重，近似等于事务锁的行记录数。当发生死锁时， InnoDB会根据此值选择最小的值作为牺牲品.

TRX_STATE 事务当前状态，包括RUNNING, LOCK WAIT, ROLLING BACK, and COMMITTING.

TRX_STARTED 事务开始时间

TRX_REQUESTED_LOCK_ID 当事务状态为lock_wait时，表示需要等待的事务的锁ID，对应innodb_locks 表里的lock_id；如果是其他值则为NULL

TRX_WAIT_STARTED 当事务状态为LOCK WAIT则代表事务等待锁的开始时间；如果是其他值则为NULL.

TRX_MYSQL_THREAD_ID MySQL线程ID，对应show processlist里的值

TRX_QUERY 事务当前执行的语句

TRX_OPERATION_STATE 事务当前执行的语句类型，不执行则为NULL

TRX_TABLES_IN_USE 执行当前语句需要涉及到几个InnoDB表

TRX_TABLES_LOCKED 当前语句执行施加的行锁对应了几个表

TRX_LOCK_STRUCTS 当前事务保留的锁个数

TRX_LOCK_MEMORY_BYTES 当前事务的锁信息所占用的内存byte数

TRX_ROWS_LOCKED 近似等于当前事务施加的行锁数量，也会包含删除语句所涉及的行锁

TRX_ROWS_MODIFIED 当前事务插入或者修改的行数

TRX_CONCURRENCY_TICKETS

TRX_ISOLATION_LEVEL 当前事务的隔离级别

TRX_UNIQUE_CHECKS 唯一键约束检查是开启状态还是关闭状态，常用于批量导入数据时关闭检查

TRX_FOREIGN_KEY_CHECKS 外键约束检查是开启还是关闭状态，常用于批量导入数据时关闭检查

TRX_LAST_FOREIGN_KEY_ERROR 上一次外键约束错误的详细信息

TRX_ADAPTIVE_HASH_LATCHED

TRX_ADAPTIVE_HASH_TIMEOUT

TRX_IS_READ_ONLY 当值为1时表明事务为只读事务

TRX_AUTOCOMMIT_NON_LOCKING 当值为1代表事务中只有一个普通select语句，不会施加任何锁，且由于autocommit是开启的，所以事务只会包含该select语句

Information_schema.innodb_locks记录了InnoDB中事务在申请但目前还没有获取到的每个锁信息，以及当前事务的锁正在阻止其他事务获得锁

LOCK_ID InnoDB内部标示每个锁的ID

LOCK_TRX_ID 表示持有该锁的事务ID，对应innodb_trx表中的事务ID

LOCK_MODE 表示该锁的模式。可以是S[,GAP], X[,GAP], IS[,GAP], IX[,GAP], AUTO_INC, UNKNOWN. 除了AUTO_INC and UNKNOWN的锁模式，其他的锁模式都暗含是GAP间隔锁

LOCK_TYPE 表示锁类型，可以是RECORD表示行锁, TABLE表示表锁

LOCK_TABLE 该锁涉及的表名

LOCK_INDEX 当为行锁时，则代表该锁涉及的索引名，否则为NULL.

LOCK_SPACE 当为行锁时，则表示被锁记录所在的表空间ID；否则为NULL

LOCK_PAGE 当为行锁时，则表示被锁记录所在的数据页数量；否则为NULL

LOCK_REC 事务锁定行的数量，若是表锁则该值为NULL

LOCK_DATA 事务锁定记录主键值，若是表锁则该值为NULL

Information_schema.innodb_lock_waits记录了InnoDB中事务之间相互等待锁的信息

REQUESTING_TRX_ID 请求锁被阻止的事务ID

REQUESTED_LOCK_ID 请求锁被阻止的锁ID

BLOCKING_TRX_ID 阻止上述事务获得锁的事务ID

BLOCKING_LOCK_ID 阻止事务对应的锁ID

InnoDB 锁机制

行级锁

行级锁是施加在索引行数据上的锁，比如SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE语句是在t.c1=10的索引行上增加锁 ，来阻止其他事务对对应索引行的insert/update/delete操作。

当一个InnoDB表没有任何索引时， 则行级锁会施加在隐含创建的聚簇索引上，所以说当一条sql没有走任何索引时，那么将会在每一条聚集索引后面加X(排它)锁，这个类似于表锁，但原理上和表锁应该是完全不同的

mysql> create table temp(id int,name varchar(10));

mysql> insert into temp values(1,'a'),(2,'b'),(3,'c');

开启两个会话链接，链接同一个数据库，模拟排他锁

session1：

## update加排他锁

mysql> set autocommit=0; #模拟开启一个事务会话

Query OK, 0 rows affected (0.00 sec)

mysql> update temp set name='aa' where id=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1 Changed: 1 Warnings: 0

session2:

##update等待第一个锁释放

mysql> update temp set name='bb'where id=2;

session1

##commit释放锁

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

session2

等待结束释放锁

update操作才会成功

Query OK, 1 row affected (5.81 sec)

Rows matched: 1 Changed: 1 Warnings: 0

看看事务的详细信息：

session1:

set autocommit=0;

update temp set name='a' where id=1; # 产生一个修改操作

session2:

select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked from

information_schema.innodb_trx\G;

*************************** 1. row ***************************

trx_id: 35455759 #事务id

trx_state: RUNNING #事务状态

trx_started: 2018-10-08 18:15:26 #事务开启时间

trx_tables_locked: 1

trx_rows_locked: 4

1 row in set (0.00 sec)

update temp set name='b' where id=2;

session3:

#查看锁等待

select * from information_schema.innodb_lock_waits;

#查看锁状态

select trx_id,trx_state,trx_started,trx_tables_locked,trx_rows_locked from information_schema.innodb_trx;

#查看锁的信息