MySQL:数据库锁 - 版本历史

Riguz：/* InnoDB锁类型 */

2021-05-03T13:35:57Z

InnoDB锁类型

←上一版本		2021年5月3日 (一) 13:35的版本
第48行：		第48行：

	=InnoDB锁类型=		=InnoDB锁类型=
			在 InnoDB 存储引擎中，SELECT 操作的不可重复读问题通过 MVCC 得到了解决，而 UPDATE、DELETE 的不可重复读问题通过 Record Lock 解决，INSERT 的不可重复读问题是通过 Next-Key Lock（Record Lock + Gap Lock）解决的。
	==Shared and Exclusive Locks==		==Shared and Exclusive Locks==
	;共享锁（S):允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。		;共享锁（S):允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
第104行：		第105行：
	*http://blog.csdn.net/see__you__again/article/details/51584165		*http://blog.csdn.net/see__you__again/article/details/51584165
	*http://www.cnblogs.com/taek/p/4744902.html		*http://www.cnblogs.com/taek/p/4744902.html


	[[Category:Database]]		[[Category:Database]]

Riguz：/* AUTO-INC Locks */

2021-05-03T13:34:01Z

AUTO-INC Locks

←上一版本		2021年5月3日 (一) 13:34的版本
第99行：		第99行：

	==AUTO-INC Locks==		==AUTO-INC Locks==
			An AUTO-INC lock is a special <span class="article-label">table-level</span> lock taken by transactions inserting into tables with <span class="article-label">AUTO_INCREMENT</span> columns.

	==Predicate Locks for Spatial Indexes==		==Predicate Locks for Spatial Indexes==
	*http://blog.csdn.net/see__you__again/article/details/51584165		*http://blog.csdn.net/see__you__again/article/details/51584165
	*http://www.cnblogs.com/taek/p/4744902.html		*http://www.cnblogs.com/taek/p/4744902.html
	[[Category:Database]]		[[Category:Database]]

Riguz：/* Insert Intention Locks */

2021-05-03T13:33:18Z

Insert Intention Locks

←上一版本		2021年5月3日 (一) 13:33的版本
第96行：		第96行：

	==Insert Intention Locks==		==Insert Intention Locks==
			An insert intention lock is a type of gap lock set by <span class="article-label">INSERT</span> operations prior to row insertion. T

	==AUTO-INC Locks==		==AUTO-INC Locks==
	==Predicate Locks for Spatial Indexes==		==Predicate Locks for Spatial Indexes==

Riguz：/* Next-Key Locks */

2021-05-03T13:32:18Z

Next-Key Locks

←上一版本		2021年5月3日 (一) 13:32的版本
第90行：		第90行：
	(13, 20]		(13, 20]
	(20, positive infinity)		(20, positive infinity)
			</syntaxhighlight>


	By default, InnoDB operates in <span class="article-label">REPEATABLE READ</span> transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents <span class="article-label">phantom rows</span>		By default, InnoDB operates in <span class="article-label">REPEATABLE READ</span> transaction isolation level. In this case, InnoDB uses next-key locks for searches and index scans, which prevents <span class="article-label">phantom rows</span>
	~~</syntaxhighlight>~~

	==Insert Intention Locks==		==Insert Intention Locks==

Riguz：/* Next-Key Locks */

2021-05-03T13:31:36Z

Next-Key Locks

@@ 第80行： / 第80行： @@
 ==Next-Key Locks==
 ==Insert Intention Locks==
 ==AUTO-INC Locks==

Riguz：/* Gap Locks */

2021-05-03T13:28:33Z

Gap Locks

←上一版本		2021年5月3日 (一) 13:28的版本
第77行：		第77行：

	==Gap Locks==		==Gap Locks==
			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. For example, <syntaxhighlight lang="sql" inline>SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;</syntaxhighlight> prevents other transactions from inserting a value of 15 into column t.c1, whether or not there was already any such value in the column, because the gaps between all existing values in the range are locked.

	==Next-Key Locks==		==Next-Key Locks==
	==Insert Intention Locks==		==Insert Intention Locks==

Riguz：/* Record Locks */

2021-05-03T13:27:33Z

Record Locks

←上一版本		2021年5月3日 (一) 13:27的版本
第73行：		第73行：

	==Record Locks==		==Record Locks==

			A record lock is a lock on an index record. For example, <syntaxhighlight lang="sql" inline>SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;</syntaxhighlight> prevents any other transaction from inserting, updating, or deleting rows where the value of t.c1 is 10.

	==Gap Locks==		==Gap Locks==
	==Next-Key Locks==		==Next-Key Locks==

Riguz：/* Intention Locks */

2021-05-03T13:25:46Z

Intention Locks

←上一版本		2021年5月3日 (一) 13:25的版本
第62行：		第62行：
	Intention locks are <span class="article-label">table-level</span> locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.		Intention locks are <span class="article-label">table-level</span> locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.

	;意向共享锁（IS）:An intention shared lock (IS) indicates that a transaction intends to set a <span class="article-label">shared</span> lock on individual rows in a table.eg. <syntaxhighlight lang="sql" inline>SELECT ... LOCK IN SHARE MODE</syntaxhighlight>		;意向共享锁（IS）:An intention shared lock (IS) indicates that a transaction intends to set a <span class="article-label">shared</span> lock on individual rows in a table. Before a transaction can acquire a shared lock on a row in a table, it must first acquire an IS lock or stronger on the table. eg. <syntaxhighlight lang="sql" inline>SELECT ... LOCK IN SHARE MODE</syntaxhighlight>
	;意向排他锁（IX）:An intention exclusive lock (IX) indicates that a transaction intends to set an <span class="article-label">exclusive</span> lock on individual rows in a table.<syntaxhighlight lang="sql" inline>SELECT ... FOR UPDATE </syntaxhighlight>		;意向排他锁（IX）:An intention exclusive lock (IX) indicates that a transaction intends to set an <span class="article-label">exclusive</span> lock on individual rows in a table. Before a transaction can acquire an exclusive lock on a row in a table, it must first acquire an IX lock on the table. eg. <syntaxhighlight lang="sql" inline>SELECT ... FOR UPDATE </syntaxhighlight>


			Intention locks do not block anything except full table requests (for example, <syntaxhighlight lang="sql" inline>LOCK TABLES ... WRITE</syntaxhighlight>). The main purpose of intention locks is to show that someone is locking a row, or going to lock a row in the table.



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

Riguz：/* Intention Locks */

2021-05-03T13:22:39Z

Intention Locks

←上一版本		2021年5月3日 (一) 13:22的版本
第60行：		第60行：
	If a transaction <span class="article-label">T1</span> holds an <span class="article-label">exclusive (X)</span> lock on row <span class="article-label">r</span>, a request from some distinct transaction <span class="article-label">T2</span> for a lock of either type on r cannot be granted immediately. Instead, transaction <span class="article-label">T2</span> has to wait for transaction <span class="article-label">T1</span> to release its lock on row r.		If a transaction <span class="article-label">T1</span> holds an <span class="article-label">exclusive (X)</span> lock on row <span class="article-label">r</span>, a request from some distinct transaction <span class="article-label">T2</span> for a lock of either type on r cannot be granted immediately. Instead, transaction <span class="article-label">T2</span> has to wait for transaction <span class="article-label">T1</span> to release its lock on row r.
	==Intention Locks==		==Intention Locks==
	~~为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。~~		Intention locks are <span class="article-label">table-level</span> locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.
	;意向共享锁（IS）:~~事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。~~
	;意向排他锁（IX）:~~事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。~~		;意向共享锁（IS）:An intention shared lock (IS) indicates that a transaction intends to set a <span class="article-label">shared</span> lock on individual rows in a table.eg. <syntaxhighlight lang="sql" inline>SELECT ... LOCK IN SHARE MODE</syntaxhighlight>
			;意向排他锁（IX）:An intention exclusive lock (IX) indicates that a transaction intends to set an <span class="article-label">exclusive</span> lock on individual rows in a table.<syntaxhighlight lang="sql" inline>SELECT ... FOR UPDATE </syntaxhighlight>

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

Riguz：/* Intention Locks */

2021-05-03T13:19:48Z

Intention Locks

←上一版本		2021年5月3日 (一) 13:19的版本
第60行：		第60行：
	If a transaction <span class="article-label">T1</span> holds an <span class="article-label">exclusive (X)</span> lock on row <span class="article-label">r</span>, a request from some distinct transaction <span class="article-label">T2</span> for a lock of either type on r cannot be granted immediately. Instead, transaction <span class="article-label">T2</span> has to wait for transaction <span class="article-label">T1</span> to release its lock on row r.		If a transaction <span class="article-label">T1</span> holds an <span class="article-label">exclusive (X)</span> lock on row <span class="article-label">r</span>, a request from some distinct transaction <span class="article-label">T2</span> for a lock of either type on r cannot be granted immediately. Instead, transaction <span class="article-label">T2</span> has to wait for transaction <span class="article-label">T1</span> to release its lock on row r.
	==Intention Locks==		==Intention Locks==
			为了允许行锁和表锁共存，实现多粒度锁机制，InnoDB还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
			;意向共享锁（IS）:事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁。
			;意向排他锁（IX）:事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁。

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

	==Record Locks==		==Record Locks==
	==Gap Locks==		==Gap Locks==

MySQL:数据库锁 - 版本历史

Riguz：​/* InnoDB锁类型 */

Riguz：​/* AUTO-INC Locks */

Riguz：​/* Insert Intention Locks */

Riguz：​/* Next-Key Locks */

Riguz：​/* Next-Key Locks */

Riguz：​/* Gap Locks */

Riguz：​/* Record Locks */

Riguz：​/* Intention Locks */

Riguz：​/* Intention Locks */

Riguz：​/* Intention Locks */

Riguz：/* InnoDB锁类型 */

Riguz：/* AUTO-INC Locks */

Riguz：/* Insert Intention Locks */

Riguz：/* Next-Key Locks */

Riguz：/* Next-Key Locks */

Riguz：/* Gap Locks */

Riguz：/* Record Locks */

Riguz：/* Intention Locks */

Riguz：/* Intention Locks */

Riguz：/* Intention Locks */