Optimistic and pessimistic database lock

Optimistic locks of database transactions and pessimistic locks a series of operations performed by a single logical unit of database transactions are SQL statements or multiple operations. An update operation is a transaction. Things have four features: 1. atomicity things must be atomic units of work, or they must all be executed successfully (that is, all SQL statements are executed successfully), or none

Optimistic locks of database transactions and pessimistic locks a series of operations performed by a single logical unit of database transactions are SQL statements or multiple operations. An update operation is a transaction. Things have four features: 1. atomicity things must be atomic units of work, or they must all be executed successfully (that is, all SQL statements are executed successfully), or none

Optimistic and pessimistic database lock

A series of operations performed by a single logical unit of database transactions are SQL statements or multiple operations. An update operation is a transaction.

Things have four features

1. atomicity

The transaction must be an atomic unit of work, or both the execution is successful (that is, all SQL statements are successfully executed) or not (all SQL statements are not executed ).

Www.2cto.com

2. Consistency

When the transaction is completed, all data must be consistent.

3. Isolation

Multiple users are allowed to access the data concurrently without breaking the complete row and correctness of the Data. At the same time, the modification of concurrent things must be isolated from other concurrent things, which are generally implemented through locking.

4. Durability

After a transaction is completed, the data must be permanently stored in the database.

Concurrent transaction access if the isolation level of the transaction is not set, the following problems will occur:

1. dirty reads read the uncommitted data of other things. One thing modifies a piece of data and has not been committed. One thing reads this data, and the modified things will be rolled out for some reason, at this time, read the dirty data.

2. Repeated read is not allowed.

A transaction reads a piece of data twice, but the reading results are different. during the second reading, another object modifies this piece of data.

Www.2cto.com

3. Phantom read

One thing is read twice, and other things are read and inserted into the data.

In view of the above situation, the database provides four isolation levels to solve the problems caused by transaction concurrency.

1. read uncommitted)

Writing will block writing, but will not block reading, so it cannot solve the problem of reading dirty data and reading things will not block other things. This isolation level cannot solve any of the above problems.

2. read committed)

Writing things blocks writing and reading things. Therefore, reading dirty data is avoided, but reading things does not block writing things and does not solve the problem of repeated reading.

3. Repeatable read)

Reading things will block writing and reading things, so they can be read repeatedly, but other things can be inserted, which cannot solve the phantom read problem.

4. Serializable)

Things must be executed one by one to solve the above problems, but things basically have no concurrency.

Concurrent transaction control

When multiple people modify the same data concurrently, a control system must be implemented so that the modification of one person does not negatively affect the modification of others.

Optimistic lock and pessimistic lock control concurrency

1. Optimistic lock

Optimistic that the probability of other users attempting to access and change the objects you are accessing is very low. Even if there is a big deal, once in a while, the overhead is not very high, if the overhead is large, the pessimistic lock must be used. the implementation of optimistic locks needs to be controlled in the program. You can add a data version number to control the implementation of optimistic locks. For example, if two things read the same record, they must be updated.

SQL code

Select * from person

Select * from person

Update person set name = 'xiaoming', version = version + 1 where id = '1'

And version = 0;

This update statement will fail to be executed, because the data with a good version of 0 cannot be found, and the first transaction has been updated.

Pdate person set name = 'xiaoming', version = version + 1 where id = '1'

And version = 0;

Www.2cto.com

If the isolation level of the physical object is set to read committed, the optimistic lock can be used to solve the repeatability, and the system must allow non-repeated reading.

Pessimistic lock

We are pessimistic about the fact that other users have a high probability of accessing or modifying the objects you are accessing or modifying. the pessimistic lock is implemented by locking. When we want to change the data, we will lock it, other things cannot be operated. The SQL statement is as follows:

SQL code

Select * from person for update

Update person set name = 'ff 'where id = '1'

The lock can be released only when the above transaction is committed, and other things can be operated. The concurrency of pessimistic locks is reduced. Therefore, in most cases, optimistic locks are used for concurrent modification.