MySQL008–MySQL锁的类型有哪些(MySQL l008 — what are the types of MySQL locks)

1、按照锁的粒度分类:

行级锁,表级锁,页级锁,记录锁,间隙锁,临键锁。

1.1 行级锁

(1)各种引擎使用的锁

1.MyISAM和MEMORY采用表级锁(table-level locking)

2.BDB采用页级锁(page-level locking)或表级锁,默认为页级锁

3.InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁

查询引擎 行级锁 表级锁 页级锁

MyISAM

  支持  
Memory   支持  
BDB   支持 支持(默认)
InnoDB 支持(默认) 支持  

MyISAM

(2)行级锁

行级锁是mysql中锁定粒度最细的一种锁。表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突,其加锁粒度最小,但加锁的开销也最大。

行级锁分为共享锁和排他锁。行级锁开销大,加锁慢,会出现死锁。发生锁冲突的概率最低,并发度也最高。

(3)其他锁 

其实行级锁和页级锁之间还有其他锁粒度的锁,就是间隙锁和临键锁。

InnoDB有三种行锁的算法:

1,Record Lock(记录锁):单个行记录上的锁。这个也是我们日常认为的行锁

2,Gap Lock(间隙锁):间隙锁,锁定一个范围,但不包括记录本身(只不过它的锁粒度比记录锁的锁整行更大一些,他是锁住了某个范围内的多个行,包括根本不存在的数据)。GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况。该锁只会在隔离级别是RR或者以上的级别内存在。间隙锁的目的是为了让其他事务无法在间隙中新增数据。

3,Next-Key Lock(临键锁):它是记录锁和间隙锁的结合,锁定一个范围,并且锁定记录本身。对于行的查询,都是采用该方法,主要目的是解决幻读的问题。next-key锁是InnoDB默认的锁

上面这三种锁都是排它锁(X锁)

1.2  表级锁

(1) 描述

表级锁是mysql中锁定粒度最大的一种锁,表示对当前操作的整张表加锁,它实现简单,资源消耗较少,被大部分mysql引擎支持。最常使用的MyISAM与InnoDB都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)

(2) 特点

开销小,加锁快,不会出现死锁。发生锁冲突的概率最高,并发度也最低。

LOCK TABLE my_table_name READ; 用读锁锁表,会阻塞其他事务修改表数据。LOCK TABLE my_table_name WRITE; 用写锁锁表,会阻塞其他事务读和写。

MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行更新操作(UPDATE、DELETE、INSERT等)前,会自动给涉及的表加写锁,这个过程并不需要用户干预,因此,用户一般不需要直接用LOCK TABLE命令给MyISAM表显式加锁。

但是在InnoDB中如果需要表锁就需要显式地声明了。

1.3 页级锁(1) 描述

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快,但冲突多,行级冲突少,但速度慢。因此,采取了折中的页级锁,一次锁定相邻的一组记录。BDB 支持页级锁。

(2) 特点

开销和加锁时间界于表锁和行锁之间;会出现死锁;锁定粒度界于表锁和行锁之间,并发度一般。

2、按照锁的属性分类:

共享锁(读锁),排他锁(写锁),意向共享锁,意向排他锁。

读锁(共享锁):Shared Locks(S锁),针对同一份数据,多个读操作可以同时进行而不会互相影响

写锁(排它锁):Exclusive Locks(X锁),当前写操作没有完成前,它会阻断其他写锁和读锁

IS锁:意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S锁时,需要先在表级别加一个IS锁。

IX锁:意向排他锁、Intention Exclusive Lock。当事务准备在某条记录上加X锁时,需要先在表级别加一个IX锁。

IS、IX锁是表级锁,它们的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁,以避免用遍历的方式来查看表中有没有上锁的记录。就是说当对一个行加锁之后,如果有打算给行所在的表加一个表锁,必须先看看该表的行有没有被加锁,否则就会出现冲突。IS锁和IX锁就避免了判断表中行有没有加锁时对每一行的遍历。直接查看表有没有意向锁就可以知道表中有没有行锁。

注意:如果一个表中有多个行锁,他们都会给表加上意向锁,意向锁和意向锁之间是不会冲突的。

3、按照加锁策略分类:

乐观锁,悲观锁。

乐观锁则认为对于同一个数据的并发操作,是不会发生修改的(或者增删改少,查多)。在更新数据的时候,会采用不断尝试更新的方式来修改数据。也就是先不管资源有没有被别的线程占用,直接取申请操作,如果没有产生冲突,那就操作成功,如果产生冲突,有其他线程已经在使用了,那么就不断地轮询。乐观的认为,不加锁的并发操作是没有事情的。就是通过记录一个数据历史记录的多个版本,如果修改完之后发现有冲突再将版本返回到没修改的样子,乐观锁就是不加锁。好处就是减少上下文切换,坏处是浪费CPU时间。

悲观锁认为对于同一个数据的并发操作,一定是会发生修改的(或者增删改多,查少),哪怕没有修改,也会认为修改。因此对于同一个数据的并发操作,悲观锁采取加锁的形式。悲观的认为,不加锁的并发操作一定会出问题。

————————

< strong > 1. Classification by lock granularity: < / strong >

Row level lock, table level lock, page level lock, record lock, gap lock and temporary key lock.

1.1 row level lock

(1) Locks used by various engines

1.MyISAM和MEMORY采用表级锁(table-level locking)

2. BDB adopts page level locking or table level lock, and the default is page level lock

3. InnoDB supports row level locking and table level locking. The default is row level locking

查询引擎 行级锁 表级锁 页级锁

MyISAM

  支持  
Memory   支持  
BDB   支持 支持(默认)
InnoDB 支持(默认) 支持  

MyISAM

(2) Row level lock

Row level lock is the lock with the smallest locking granularity in MySQL. Indicates that only the rows of the current operation are locked. Row level locking can greatly reduce the conflict of database operations. Its locking granularity is the smallest, but the locking overhead is also the largest.

Row level locks are divided into shared locks and exclusive locks. Row level locks are expensive and slow to add locks, resulting in deadlocks. The probability of lock conflict is the lowest and the concurrency is the highest.

(3) Other locks

In fact, there are other lock granularity locks between row level locks and page level locks, namely gap locks and key critical locks.

InnoDB has three row lock algorithms:

1. Record lock: a lock on a single line record. This is also what we think of as a < strong > row lock < / strong >.

2. Gap lock: gap lock locks a range, but < strong > does not include the record itself < / strong > (only its lock granularity is larger than the whole row of the record lock. It locks multiple rows within a range, including nonexistent data). The purpose of gap lock is to < strong > prevent < / strong > two current reads of the same transaction from < strong > unreal reading < / strong >. The lock will only exist at the isolation level of RR or above. The purpose of gap lock is to prevent other transactions from adding data to the gap.

3. Next key lock: it is a combination of record lock and gap lock, locks a range, and < strong > locks the record itself < / strong >. This method is used to query rows. The main purpose is to < strong > solve the problem of unreal reading < / strong >. The next key lock is the default < / strong > lock of InnoDB < strong >

The above three locks are exclusive locks (x locks)

one point two    Table lock

(1) Description

Table level lock is a lock with the largest locking granularity in MySQL. It means locking the whole table of the current operation. It is simple to implement and consumes less resources. It is supported by most MySQL engines. Both MyISAM and InnoDB, which are most commonly used, support table level locking. Table level locks are divided into table shared read locks (shared locks) and table exclusive write locks (exclusive locks)

(2) Characteristics

Low overhead, fast locking, no deadlock. The probability of lock conflict is the highest and the concurrency is the lowest.

LOCK TABLE my_ table_ name READ; Locking a table with a read lock will block other transactions from modifying the table data. LOCK TABLE my_ table_ name WRITE; Locking the table with a write lock will block other transactions from reading and writing.

Before executing the query statement (select), MyISAM will automatically add read locks to all tables involved. Before executing the update operation (update, delete, insert, etc.), MyISAM will automatically add write locks to the tables involved. This process does not require user intervention. Therefore, users generally do not need to directly use the lock table command to explicitly lock MyISAM tables.

However, in InnoDB, if a table lock is required, it needs to be explicitly declared.

1.3 page level lock (1) description

Page level lock is a kind of lock with locking granularity between row level lock and table level lock in MySQL. Table level locking is fast, but there are many conflicts, and row level conflicts are few, but the speed is slow. Therefore, a compromise page level lock is adopted to lock a group of adjacent records at one time. BDB supports page level locks.

(2) Characteristics

The overhead and locking time are bounded between table lock and row lock; Deadlock will occur; The locking granularity is between table lock and row lock, and the concurrency is general.

< strong > 2. Classification by lock attribute: < / strong >

Shared lock (read lock), exclusive lock (write lock), intentional shared lock, intentional exclusive lock.

Read lock (shared lock): shared locks. For the same data, multiple read operations can be performed simultaneously without affecting each other

Write lock (exclusive lock): exclusive locks (x lock). It will block other write locks and read locks before the current write operation is completed

Is lock: intention shared lock and intention shared lock. When a transaction is going to add an s lock to a record, it is necessary to add an is lock at the table level first.

IX lock: intention exclusive lock and intention exclusive lock. When a transaction prepares to add an X lock to a record, it needs to add an IX lock at the table level first.

Is and IX locks are table level locks. They are only proposed to quickly judge whether the records in the table are locked when adding table level s locks and X locks later, so as to avoid checking whether there are locked records in the table by traversal. That is, after locking a row, if you plan to add a table lock to the table where the row is located, you must first check whether the row of the table is locked, otherwise there will be a conflict. The is lock and IX lock avoid traversing each row when judging whether the row in the table is locked or not. You can know whether there are row locks in the table by directly viewing whether there are intention locks in the table.

Note: if there are multiple row locks in a table, they will add intent locks to the table. There will be no conflict between intent locks and intent locks.

< strong > 3. Classification by locking strategy: < / strong >

Optimistic lock, pessimistic lock.

Optimistic lock holds that concurrent operations on the same data will not be modified (or less additions, deletions and changes, more queries). When updating data, it will modify the data by constantly trying to update. That is, first, whether the resources are occupied by other threads or not, the application operation is directly fetched. If there is no conflict, the operation is successful. If there is a conflict and other threads are already in use, the operation is continuously polled. I am optimistic that there is nothing wrong with concurrent operations without locks. By recording multiple versions of a data history, if a conflict is found after modification, and then return the version to the unmodified state, optimistic locking is not locking. The advantage is to reduce context switching, while the disadvantage is to waste CPU time.

Pessimistic lock believes that concurrent operations on the same data must be modified (or more additions, deletions, changes and fewer queries). Even if there is no modification, it will be considered modified. Therefore, pessimistic locking takes the form of locking for concurrent operations of the same data. Pessimists believe that there will be problems with concurrent operations without locks.