In mysql, there are four types of locks for InnoDB, including row locks and table locks, respectively

  • Basic Locks – [Shared Locks and Exclusive Locks]
  • Intention lock – [Intention lock, divided into intention shared lock (IS lock) and intention exclusive lock (IX lock)]
  • [Record Locks, gap Locks, next-key Locks, Insert Intention Locks]
  • Auto-add locks – [auto-inc locks]

Here is the compatibility between the various locks:

​

​

InnoDB three row locking algorithms:

  • Record Lock: A Lock on a single row Record that locks only the Record itself
  • Gap Lock: A Gap Lock that locks a range but excludes the record itself. The purpose is to prevent two current readings of the same thing, the illusion of reading
  • Next-key Lock: 1+2, locks a range and locks the record itself. Purpose: To solve illusory reading

A Shared lock

Shared locks(also known as read locks) allow other things to have S locks, but not X locks

Locking mode:

select... lockin share modeCopy the code

Note:

  • For transactions that use a shared lock, other transactions can only be read, not written
  • If an update is performed, wait until the current transaction is commit or rollback
  • If the current transaction also executes the SAME SQL statement that the other transactions are waiting on, the current transaction will execute successfully and the other transactions will report a deadlock
  • And allow other locks to coexist

Exclusive lock

Also known as write Locks, Exclusive Locks cannot be added to other transactions

Locking mode:

select ... for updateCopy the code

→ For update: InnoDB uses row locks by default, when there is a specified primary key, it uses row locks; Otherwise, a table lock is used. Details of use are as follows:

  • Specify the primary key explicitly, and from this record, row-level locking. Ex. : 

    select name,age from tb_user where id = '1' forUpdate (id is primary key)Copy the code
  • Specify primary key/index explicitly, if no record, no lock. Ex. : 

    select name,age from tb_user where id = '1' forUpdate (id is primary key, but no data with id = 1)Copy the code
  • No primary key/index, table level lock. Ex. : 

    select name,age from tb_user where age = 12 forUpdate (age is a normal field)Copy the code
  • Unclear primary key/index, table level locking. Ex. : 

    select name,age from tb_user whereAge = 12, id ='1' forUpdate (id is primary key, age is not, but database has this data)Copy the code

Note:

  • For an exclusive locked transaction, other things can be read but cannot be updated
  • For UPDATE is only used with InnoDB, and transactions must be enabled between BEGIN and COMMIT
  • When a transaction has a “for UPDATE”, another transaction has a “for update”, it will wait until the previous transaction commit or rollback or disconnect to release the lock.
  • InnoDB engine. Update, DELETE, INSERT, and select statements are not locked by default

Optimistic locking

Read retrieves the record and reads the version with it, performing the update operation and +1 to the recorded version number. At this point, the version number of the record to be submitted is compared with that of the corresponding table of the database. If the version number is greater than the original version number of the database, it will be updated. Otherwise, the data is considered expired and the update fails. The purpose is to solve concurrency problems.

Example: A, B two people modify the same record at the same time, suppose the original amount of the database is 100 yuan, A pair of amount +100, B to the database -50, the normal result is 150, but due to concurrency, the result may be 200, or 50

Solution: AT the same time, A and B read and read the data version 1, and A changed the data version to 2, and submitted the data. At this time, the data version is 1, and the update is successful. B reads the data version 1, and the amount is -50. The result is 50 at this time, and changes the data version to 2. Submits the data, and compares the original database version 2, but the data is not higher than the original version

Clearance lock

A gap lock is a lock on the gap between index records, or the gap before or after the last index record

Using a unique index to search for a single row does not require a gap lock.

  • Premise :(id is the primary key index) since the search result is the only record, gap locking is not used 

    select id,name,age from tb_user where id = '1'Copy the code
  • Select id,name,age from tb_user where id = ‘1’ and age = 13

Presentation:





CREATE TABLE `tb_user` (
  `id` int(10) NOT NULL,
  `name` varchar(255) NOT NULL DEFAULT ' ',
  KEY `index_id` (`id`),
  KEY `index_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8Copy the code

→ Initialize data:



Ps: I am using the database’s default isolation level: REPEATable Read and started both clients locally for testing.

→ Client 1: start the transaction and modify the data between ID 1 and 6 in the client. At this time, the record line id=2 does not exist



→ Client 2: start transaction, add the record with ID 2 to the database will find that the operation will be blocked!



The above situation indicates the existence of a gap lock

The read commited isolation level is set to read commited. The read commited isolation level is set to read commited.

Note:

  • The only function of a gap lock in InnoDB is to prevent the insertion of other transactions to prevent phantom read, so there is no shared lock or exclusive lock gap lock.
  • If InnoDB scans a primary Key/unique index, InnoDB will only use a Record Lock, not a next-key Lock.
  • A gap lock only prevents other things from inserting into the gap, and does not prevent different things from acquiring a gap lock on the same gap.
  • Setting the isolation level to read_commited or enabling the innodb_locks_unSAFE_for_binlog system variable (now deprecated) explicitly disallows the use of gap locks

MVCC (Snapshot Read vs Current Read)

MVCC, based on multiple versions of the concurrency control protocol, is typically read without lock and read without conflict. It contains two types of read operations, namely snapshot read and current read.

  • Snapshot read: Reads the visible version of a record without locking it.
  • Current read: Read the latest version of the record, the current read return record, are locked, to ensure that other things will not modify the record

Which operations are current reads and which are snapshot reads?

→ Snapshot read: A simple read operation. Snapshot read is not locked. (There are some minor exceptions, though)

Example: select * from tb_user where?

→ Current read: A special read operation belongs to the current read and needs to be locked.

  • select * from table where ? lock in share mode;

  • select * from table where ? for update;

  • Insert into table values (…) ;

  • update table set ? where ? ;

  • delete from table where ? ;

All of the above statements belong to the current read, the latest version of the read record. In addition, after reading, it is necessary to ensure that other concurrent transactions cannot modify the current record and lock the read record. Except for the first statement, which locks the read record with S (shared lock), all other operations are locked with X (exclusive lock).

The above is my research database lock these days to make a summary, what is not welcome to correct ha ~

Finally, I recommend a blog written by danniu, which is very detailed: hedengcheng.com/?p=771#_Toc…