mysql u锁_MySQL事务&锁

MySQL事务&锁

事务

属性

Atomicity：同一个事务里，要么都提交，要么都回滚

Consistency：在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏

Isolation：并发事务之间的数据是彼此隔离的

Durability：事务提交后，所有结果务必被持久化

操作

显式开启事务：begin/begin work/start transaction

提交事务

显式：commit

隐式：begin/begin work/start transaction/set autocommit=1/DDL/DCL

回滚事务

显式：rollback

隐式：连接断开/超时

自动提交：set autocommit|@@autocommit=0|1 --会话级

影响

脏读：读取到其他事务修改但未提交的数据，读到的数据内容可能被回滚

不可重复读：由于其他事务update/delete且已提交，两次读取相同的行，第二次检索到不同的行值/发现行被删除

幻读：由于其他事务insert且已提交，第一次当前读没有检索到，第二次当前读检索到新结果集。幻读仅专指“新插入的行”

隔离级别

Read Uncommitted：读取一个未提交的事务，存在脏读现象

Read Comminted：一致性读，同一事务中的Consistent reads每次创建新快照；\n锁定读，对读取到的记录加锁(记录锁)，存在幻读现象；Gap锁只用于foreign-key和duplicate-key检查

Repeatable Read：一致性读，同一事务中的Consistent reads在首次读操作创建快照。\n锁定读，存在唯一索引且使用完整索引搜索，对读取到的记录加锁(记录锁)，如果记录不存在则对应Gap加锁；\n其他情况，对读取到的记录加锁(记录锁)，同时对记录前的Gap加锁，不存在幻读现象

Serializable：所有的读操作均为当前读，读加读锁 (S锁)，写加写锁(X锁)，读写冲突

MVCC

RC：每一个select都会创建Read View

RR：事务中第一个select创建Read View；start transaction with consistent snapshot;立刻创建Read View

查看修改事务隔离级别select@@global.tx_isolation,@@session.tx_isolation

set [session|global] transaction isolation level [read uncommitted|read committed|repeatable read|serializable]

不指定GLOBAL、SESSION关键字，本次连接当前事务还未起作用，要到下一个(尚未启动的)\n新事务才起作用，下一个事务结束后，又将恢复成本次SESSION中原先的设置

锁

锁模式

共享锁(S)：允许持有锁的事务进行读取操作

排他锁(X)：允许持有锁的事务进行更新或删除操作

意向锁(I，表级别)

锁兼容性

锁兼容性

加锁范围

全局锁

Flush Tables With Read Lock

Query Cache Lock：清空QC的方式 reset query cache或flush tables

表级锁

表锁：LOCK TABLES … READ/WRITE; UNLOCK TABLES

元数据锁：MDL

当对表做增删改查操作的时候，加MDL读锁；当对表做结构变更操作的时候，加MDL写锁。MDL用于防止DDL和DML并发冲突

P_S查看MDL

update setup_instruments set ENABLED='YES',TIMED='YES' where name='wait/lock/metadata/sql/mdl';select *from performance_schema.metadata_locks;

lock_wait_timeout(默认1年，建议改成300s或者更低)

InnoDB行锁

Record Lock：LOCK_REC_NOT_GAP，记录锁(主键、唯一索引)

Gap Lock：LOCK_GAP，间隙锁

Next-Key Lock：LOCK_ORDINARY，普通辅助索引

Insert Intention Lock：LOCK_INSERT_INTENT，插入意向锁

补充：MySQL的行锁是在引擎层由各个引擎自己实现的。MyISAM引擎不支持行锁，只能使用表锁；InnoDB引擎默认行锁，在索引上加锁

影响

锁等待：锁L1锁定某个对象R1，锁L2等待该锁释放，如果不释放，会一直等待，或者达到系统预设的超时阈值后告错

死锁：锁资源请求产生了回路，例如：L1等待L2释放，L2等待L3释放，L3等待L1释放，死循环

锁监控

show global status like'%innodb_row_lock%'show engine innodb status

information_schema.innodb_locks/innodb_lock_waits

sys.innodb_lock_waits

performance_schema.data_locks/data_lock_waits

其他

AUTO-INC Lock：自增锁，innodb_autoinc_lock_mode

自旋锁

用于控制InnoDB内部线程调度而生的轮询检测

innodb_spin_wait_delay