MySQL008–MySQL锁的类型有哪些(MySQL l008 — what are the types of MySQL locks)
LOCK TABLE my_table_name READ; 用读锁锁表，会阻塞其他事务修改表数据。LOCK TABLE my_table_name WRITE; 用写锁锁表，会阻塞其他事务读和写。
1.3 页级锁(1) 描述
页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录。BDB 支持页级锁。
IS锁：意向共享锁、Intention Shared Lock。当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
IX锁：意向排他锁、Intention Exclusive Lock。当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。
< 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
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
(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
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)
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.
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.