Shared locks and exclusive locks

InnoDB storage engine has two types of locks: shared lock in Share mode and exclusive lock for Update. These two types of locks are all row-level locks and are added to indexes. If indexes fail, these two types of locks will expand to table-level locks (MyISAM defaults to table-level locks).

If a transaction’s SQL contains for UPDATE, all it does at the bottom is check to see if other transactions hold the lock. If they don’t, lock it. If they do, wait for other transactions to release the lock.

lock in share mode

It is called a shared lock because once it is acquired by one transaction, other transactions can acquire it, but the other transaction cannot perform CUD operations on it, only read operations.

The first transaction that fetches it can perform a CUD operation, but this is not recommended and may cause a deadlock.

InnoDB storage engine handles deadlocks by making a transaction stop waiting (select the transaction that holds the least row-level locks to stop waiting)

Note that a shared lock is placed on the transaction and no transaction is committed.

Open a query window again and try to obtain the lock and find that it can be obtained.

Try to execute the CUD operation in the new query window, wait for some time and find the lock timeout, proving that the S lock cannot be modified in other transactions

What if you don’t manually add lock in share mode to other queries

It is found that it can be executed, in fact, in the last step to perform the modification of the operation as you can see, there is no manual add S lock, but in the execution of CUD operation, the default add X lock (for update). After adding s lock, other transactions are not allowed to add X lock, so it forms a wait timeout error.

As mentioned above, either an X lock or an S lock is nothing more than a tag in SQL code. SQL that holds the tag will be detected by the underlying execution, and will not be detected by the underlying execution of SQL that does not carry the tag (except for CUD operations). This principle is covered in the For Update demo.

for update

In other words, if an X lock is added to a transaction, other transactions are not allowed to perform CRUD operations, and other transactions are not allowed to add S locks to it. Only after the x lock is released by the current transaction, can everything be normal.

Lock x on the transaction

Other transactions perform query operations and find that the query is blocked and no data is queried

If you do not add “for UPDATE”, the query will be successful. If you do not add “for update”, the query will be successful. If you do not add “for update”, the query will be successful

The same problem can be blocked if CUD operations do not add for update, because as described above, CUD operations add x lock by default

instructions

If you set autoCOMMIT = 1, the transaction will be committed automatically. When executing a single SQL statement, the transaction will be implicitly started and the transaction will be implicitly committed.

The lock is not related to the transaction. The reason for writing the X lock or S lock in the transaction is to increase the scope, so that it is easy to test. If the following SQL statement is executed simultaneously under certain conditions, it will also cause wait.

select id from student for update;
select s_sex from student for update;
Copy the code