Database lock mechanism

During this time, due to the development project, I re-learned the database concurrency control and lock mechanism. The database uses the locking mechanism to solve the concurrency problem. There are two types of locks: Shared locks and exclusive locks (also called exclusive locks ). When executing the SELECT statement, you must add a shared lock to the operation object (table or some records). Before locking, you must check whether exclusive locks exist. If no exclusive locks exist, you can apply a shared lock (n shared locks can be added to an object). Otherwise, no. The shared lock is usually released after the SELECT statement is executed. Of course, it may also be released when the transaction ends (including normal and abnormal ends, it mainly depends on the transaction isolation level set by the database. When executing insert, update, and delete statements, you need to apply an exclusive lock to the objects in the operation (I feel that the exclusive lock should be applied at the table level when executing insert statements ), before adding an exclusive lock, you must ensure that there is no other lock on the object. Once an exclusive lock is added, no other lock can be applied to the object. The release of the exclusive lock is usually at the end of the transaction (of course there are exceptions, that is, when the database transaction isolation level is set to read uncommitted (read uncommitted data, in this case, the exclusive lock will be released after the update operation is executed, rather than at the end of the transaction ). The Database supports automatic lock upgrade in a transaction. For example, if a SELECT statement is executed first in a transaction and then an update statement is executed, the two statements operate on the same object, it is also assumed that the shared lock is released at the end of the transaction. If the database does not support automatic lock upgrade, it will fail when the update statement requests an exclusive lock. Because the share lock of the SELECT statement is not released, the transaction enters the infinite waiting state, that is, the deadlock. With the automatic lock upgrade, you can upgrade the previously added shared lock to the exclusive lock when executing the update statement. However, the premise is that the shared lock must be added by the transaction itself, in addition, no other locks are applied to the operation object. Otherwise, the shared lock cannot be upgraded to exclusive locks and must wait for the release of other locks. When reading hibernate, the update lock is also mentioned. Because we usually query the update operation first, that is, we usually add the WHERE clause in the update and delete statements. In this case, some database systems may add a shared lock to the operation object and an exclusive lock to the update object during query execution. However, this may cause problems, that is, if two transactions need to update an object at the same time, a shared lock is applied to the object first. When the transaction is to be updated, both requests are requested to upgrade the lock, however, this object has a shared lock applied to the transaction of the other party .. Therefore, it cannot be upgraded. In this way, two transactions are waiting for the other party to release the shared lock and enter the Deadlock State. The update lock is used to solve this problem, that is, when the query operation is executed, it is not a shared lock but an update lock (only one update lock and n shared locks can be applied to an object ), when the update is required, the update lock is upgraded to an exclusive lock, provided that this object only has the update lock added by this transaction, and there is no other lock. In fact, I think, if the exclusive lock is applied to the transaction during query execution, can it solve the deadlock problem, but this seems to weaken the system's concurrency performance. Now let's talk about the transaction isolation level of the database. In hibernate, four database transaction isolation levels are mentioned: Read uncommitted data (this is rarely used) this isolation level does not mean that the lock is not applied when data is read or updated .. The lock is actually implemented, but the lock is released immediately after the operation is completed. Instead of releasing it after the transaction ends. Exclusive locks are used for shared locks. Read committed: Read committed data (this is often used and often the default setting of the database). This isolation-level shared lock is released immediately after the data is read .. The exclusive lock is released at the end of the transaction. Repeatable read: Shared locks and exclusive locks at the isolation level are released at the end of the transaction. Therefore, it is called "Repeatable read", that is, the data read by a firm is not updated by other transactions... You can read the data you just read at any time during transaction execution. The read data will not be changed until the transaction ends. Serializable: the serializable shared lock and exclusive lock are also released at the end of the transaction. Unlike Repeatable read, generally Repeatable read .. When reading data .. All apply a shared lock to each record. The shared locks of serializable are applied to the entire table, so that not only the data read is not modified by other transactions... Other unread data in the same table will not be modified, and you don't even have to worry about reading the data added to the new table (you cannot add data to the table at all ), therefore, such isolation levels do not involve virtual reads.