Database (vi), lock

In the database (v), within the transaction we speak of the acid property of the transaction, the most important of which can be the repair of the exception and the processing of the concurrent connection.

The repair of abnormal condition is mainly done through the log , then the processing of the concurrent connection is mainly through the lock . This chapter focuses on the related knowledge of locks .

Why do I need a lock?

Bob now has 1000 dollars in his account, and the program suddenly comes with two requests, one to transfer Bob's money to Smith 20 and one to turn Bob's money to Joe 30. The two asked for Bob's account, found that Bob now has 1000 pieces, so ask a to figure out that Bob should have 980, ask B to figure out that Bob should be 970. The data required for a is covered by the data required for B.

This is a problem, obviously should buckle 50 yuan, now but just deducted 30 pieces.

A lock is a problem that is used to resolve such concurrent access. Each time you access Bob's account, a lock is added to prevent others from accessing it, and only the person waiting to hold the lock will be released .

Pessimistic lock and optimistic lock pessimistic lock

If transaction a locks Bob's account, transaction B naturally cannot manipulate Bob's account, which means that other threads can only wait outside.

This way of locking is pessimistic lock . Every time it reads and writes data, it always thinks that the data will be modified by others, so lock the data and put it in a locked state to keep it from being accessed.

The downside is that if the lock is held too long, other users will have to wait a long time.

Pessimistic lock is mainly used for concurrent scramble for more serious scenes.

Optimistic lock

The problem with pessimistic locking is obvious, if the data is locked, the other threads are inaccessible and can only wait. If you hold the lock for too long, you need to wait a lot of time.

So we introduced the optimistic lock , the so-called optimistic lock is that the general situation will not have too many people to modify the balance, all without lock, only in the last update to see if there is a conflict.

How exactly do you do that?

You can add a version field to the log,

• Each time you read , you need to read not only the balance, but also the version number.

• After you have modified the balance, you need to check the version number before you write back, and see if the version number is the same as when you read it.

  • If not, the data has been changed, so you need to discard the write operation, re-read the balance and the version number

  • If the same, write the new Balance back and add 1 to the version number.

Like what

Transaction 1 Subtracts Bob's balance by 30, when it reads (Bob balance = 1000, version =1)

Transaction 2 also needs to subtract Bob's balance by 50, he also reads (Bob balance = 1000, version =1)

Then transaction 1 first completed the calculation, the new Balance value of 970 is written back, version plus 1, became version 2.

When transaction 2 is written back, it is found that the latest version number becomes 2, which means that the previously read data has changed, so it needs to be read again

This is the optimistic lock, which is suitable for a few conflict scenarios , if the conflict is many, the data contention is fierce, will lead to continuous attempts, but reduced performance.

Conditions for deadlock-lock generation

If any of the following occurs

• There are two threads participating at the same time

• These two threads lock the same resource in different directions

• Scramble for the same resources

Then there's a good chance of a deadlock.

For example, transaction 1 is Bob's transfer to Smith, and transaction 2 is Smith's transfer to Bob.

When these two transaction units occur at the same time, there is a problem.

Transaction Unit 1 Locks Bob First and then locks Smith, and transaction Unit 2 Locks Smith First and then locks Bob

Transaction 1 waits for transaction 2 to release Bob, while transaction 2 waits for transaction 1 to release Smith.

How to Solve

So how to solve the deadlock? The best way is to avoid deadlocks as much as possible, which is certainly difficult. Or, if the lockout time expires, Force release, but this method is less efficient, because if the user's transaction time is very long, then the detection time for each deadlock will be very long.

So the best solution is to predict deadlocks and record the locks that the transaction unit waits for.

For example, transaction Unit 1 holds lock Bob's locks and is now applying for a lock Smith, and before applying, you can see which transaction units have also applied for "lock Smith". Obviously transaction Unit 2 has also applied for this lock. OK, the next step is to see what lock the Transaction Unit 2 is applying for, and find that it is actually applying "lock Bob" to this lock, and this lock is currently held by transaction Unit 1. So now there is the possibility of a deadlock, that is, a collision . So it can be remedied in advance.

U lock

The following is a discussion of a deadlock situation. Such as

Transaction 1 TRX1

Start Transaction 1
Read a (read lock)
A-100 (read lock needs to be upgraded to write lock)
Commit a Transaction 1

Transaction 2 TRX2

Start Transaction 2
Read a (read lock)
A-100 (read lock needs to be upgraded to write lock)
Commit TRANSACTION 2 (unlock)

The read lock of transaction 1 and transaction 2 can be parallel, so the read lock can enter the critical section at the same time, but the write lock cannot be blocked outside. At this point, transaction 2 initiates a write lock. Then the awkward situation arose.

Write locks on transaction 1 need to release resources such as read locks for transaction 2.

A write lock on transaction 2 waits for a read lock on transaction 1 to release resources.

So the deadlock was formed. In fact, the condition of the deadlock is very simple, only need to read and write for the same data . update set A=A-1 where id = 100For example, if you run multiple times, a deadlock will occur.

The solution is to introduce a u -Lock, which can be directly promoted to write lock.

For transaction 1, it is written immediately after reading, so write locks are used directly instead of read locks.

The same is true for transaction 2.

Database (vi), lock