行和列代表不同事务表锁
Another use for UNLOCK TABLES is to release the global read lock acquired with the FLUSH TABLES WITH READ LOCK statement, which enables you to lock all tables in all databases. See Section 13.7.8.3, “FLUSH Statement”.lock tables、start transcation命令可以隐式的释放之前持有的锁；
WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it.对于读-写-读的情况，由于锁的优先级较高，如果申请写的session迟迟获取不到锁，会阻塞后续其他session申请读锁；具体分析看Case1；
Closes all open tables and locks all tables for all databases with a global read lock.元数据锁
Statements acquire metadata locks one by one, not simultaneously, and perform deadlock detection in the process.元数据锁是一个个获取的，DML和DDL通过不同的方式定义执行的顺序；官网提供了一个rename table的顺序例子，但那个例子挺迷的；
DML statements normally acquire locks in the order in which tables are mentioned in the statement.
DDL statements, LOCK TABLES, and other similar statements try to reduce the number of possible deadlocks between concurrent DDL statements by acquiring locks on explicitly named tables in name order.
To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table's structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.如果一个session或者一个事务持有某个表的元数据锁，那么另一个session或者事务就无法执行DDL操作；
A record lock is a lock on an index record.行锁是在索引上的一个锁。这句话非常重要！
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.单靠行锁是无法解决幻读的问题的，所以innodb引入了间隙锁的概念，只在RR级别生效。间隙锁是一个范围锁，比如所以索引1和索引3之间就存在（1，3）这样一个间隙，当这个间隙被锁定的时候，就无法插入值为2的记录。
Gap locking is not needed for statements that lock rows using a unique index to search for a unique row.当查询条件是唯一索引，如果查询的值存在且是唯一的一行记录，那么是不需要加间隙锁的；因为间隙锁的出现就是为了防止幻读，对于加了唯一索引的表，同样的查询条件永远只能查出唯一的一条，既然已经保证了唯一，那么就没有间隙锁的必要了。
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.临建锁=行锁+间隙锁，是innodb RR级别默认加的锁；由于锁定的是当前索引记录行和索引前的部分，所以一般总结为左开右闭；
An insert intention lock is a type of gap lock set by INSERT operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap. Suppose that there are index records with values of 4 and 7. Separate transactions that attempt to insert values of 5 and 6, respectively, each lock the gap between 4 and 7 with insert intention locks prior to obtaining the exclusive lock on the inserted row, but do not block each other because the rows are nonconflicting.插入意向锁是间隙锁类型的一种意向锁，锁的是间隙；是在进行插入之前必须申请获得的锁，所以和间隙锁是冲突的；换句话说，如果你想插入一条语句，那么这个语句对应的间隙必须不存在锁，这样你才能加上插入意向锁，进而插入数据；
An AUTO-INC lock is a special table-level lock taken by transactions inserting into tables with AUTO_INCREMENT columns.如官方文档所说，自增锁其实是只针对于自增的字段，算是一个表级锁，一般对我们来说就是自增主键；当有多个事务同时想要插入，由于自增的值必须保持连续，所以多个事务的插入必须串行；
| 欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) | Powered by Discuz! X3.4 |