Database things and locks, what exactly is the lock?

Blue Moon Lake and software test 2017-02-20 10:32

Lock

In our normal work, contact with the database of friends, we have more or less heard or contacted the database transaction and database lock, for not yet clear database transactions and database lock What is the big friend, can spend a few minutes to understand the next simple. No matter to the work, the new year also some friends consider to jump-job, may also understand these concepts, do not ask Sanbuzhi, think sorting out or useful, I hope that everyone can help.

Transactions

Database transaction is a logical unit in the execution process of database management system, which consists of a limited database operation sequence.

A database transaction typically contains a sequence of read/write operations to the database. Its existence consists of the following two purposes:

1. Provides a way for a database operation sequence to recover from a failure to a normal state, while providing a way for the database to remain consistent even in an abnormal state.

2. When multiple applications are accessing the database concurrently, an isolation method can be provided between these applications to prevent interference with each other's operations.

When a transaction is submitted to the DBMS (database management system), the DBMS (database management system) needs to ensure that all operations in the transaction are completed successfully and that the results are persisted in the database, and that if any operations in the transaction are not completed successfully, all operations in the transaction need to be rolled back. Back to the state before the transaction executes, and the transaction has no effect on the execution of the database or other transactions, and all transactions appear to be running independently.

But in a realistic situation, the risk of failure is high. During the execution of a database transaction, it is possible to encounter a transaction operation failure, a database system/operating system failure, or even a failure of the storage media, and so on. This requires the DBMS to perform a recovery operation on a failed transaction, restoring its database state to a consistent state (the consistent state of the data is guaranteed). To achieve the ability to restore the database state to a consistent state, the DBMS typically needs to maintain the transaction log to track all operations in the transaction that affect database data.

The above is a description of Wikipedia, using an example to deepen the understanding of the transaction: the same bank transfer, a to 1000 yuan to B, there will be two operations, one operation is a account deduction of 1000 yuan, the other operation is a B account increase of 1000 yuan, the two constitute a transfer of this transaction. If you put two operations inside a transaction and are supported by the underlying transaction provided by the database, there will be no problem, either all succeed, or all will not succeed. If two operations in two separate transactions, the first transaction succeeds, the second transaction fails, there will be a middle state (a user's money buckle, B user money is not accounted for), so the improper use of transactions will also have problems.

Not an arbitrary sequence of operations on a database is a database transaction. Database transactions have the following four characteristics, which are customarily referred to as ACID properties. What is the acid characteristic? Let's see.

Atomicity (atomicity): A transaction is executed as a whole, and the operations contained therein are either completely executed or not executed.

Consistency (consistency): a transaction should ensure that the state of the database transitions from one consistent state to another consistent state. The meaning of a consistent state is that the data in the database should satisfy the integrity constraints.

Isolation (Isolation): When multiple transactions are executing concurrently, the execution of one transaction should not affect the execution of other transactions.

Persistence (Durability): Committed transaction modifications to the database should be persisted in the database.

To learn more about Dabigatran 588678536, group files can be downloaded from more documents.

Lock

When concurrent transactions access a resource at the same time, it is possible to cause inconsistent data, so a mechanism is needed to order data access to ensure consistency of database data. A lock is one of the mechanisms.

1. Shared lock

A shared lock (S-Lock) allows concurrent transactions to read (SELECT) resources under closed concurrency control. When there is a shared lock (S lock) on the resource, no other transaction can modify the data. As soon as the read operation is complete, the shared lock (S lock) on the resource is freed, unless the transaction isolation level is set to repeatable read or higher, or a shared lock (S lock) is reserved with a lock hint for the duration of the transaction.

2. Update lock (U lock)

Update locks are a combination of shared and exclusive locks. An update lock means that when an update is made, a shared lock may be converted to an exclusive lock after the scan has completed qualifying data.

There are two steps in this:

1) When the scan gets the where condition, this part is an update query, and this is an update lock.

2) If a write update is performed. The lock is now upgraded to an exclusive lock. Otherwise, the lock is turned into a shared lock.

Update locks can prevent common deadlocks.

3. Exclusive lock

An exclusive lock (X Lock) prevents concurrent transactions from accessing resources. An exclusive lock is not compatible with any other lock. When an exclusive lock (x Lock) is used, no other transaction can modify the data, and the read operation is performed only when the NOLOCK hint or uncommitted read isolation level is used.

Pessimistic lock

Pessimistic locking refers to the assumption that concurrent update conflicts occur, so the lock mechanism is used regardless of whether the conflict actually occurs.

Pessimistic locks perform the following functions: Lock the Read record and prevent other transactions from reading and updating the records. Other transactions will continue to block until the transaction ends.

Pessimistic lock is the use of database transaction isolation function, based on exclusive access to resources, so as to ensure the consistency of read data to avoid loss of modification.

Pessimistic locks can use the repeatable read transaction, which fully satisfies the pessimistic lock requirements.

Optimistic lock

An optimistic lock does not lock anything, that is, it does not depend on the transaction mechanism of the database, and the optimistic lock is a purely application-level thing.

If you use optimistic locking, the database must have a version field, otherwise you can only compare all fields, but because floating-point types cannot be compared, it is not feasible to actually have a version field.

To learn more about Dabigatran 588678536, group files can be downloaded from more documents.

This article is for the headline author and does not represent today's headline position.