Shulou(Shulou.com)06/02 Report--

This article shows you how to use Laravel sharedLock and lockForUpdate for data table row locking, the content is concise and easy to understand, can definitely brighten your eyes, through the detailed introduction of this article, I hope you can get something.

Scene

Group purchase function, when customer A starts a group (two-person group), if B and C pay at the same time, how to avoid increasing the number of group buyers at the same time.

The difference between sharedLock and lockForUpdate in Laravel

SharedLock corresponds to LOCK IN SHARE MODE.

LockForUpdate corresponds to FOR UPDATE.

What sharedLock has in common with lockForUpdate is that it prevents the same row of data from being update by other transaction.

The differences are:

SharedLock does not prevent other transaction from reading the same line

LockForUpdate prevents other transaction from reading the same line. (it is important to note that normal unlocked read reads can still read to the row, and only sharedLock and lockForUpdate reads are blocked. )

SharedLock locks only for write, lockForUpdate also prevents them from being selected

This makes sense, for example, if two transaction updates the same counter, if you don't use lockForUpdate, it will cause both transaction to read the same initial value at the same time, and then submit it to the database after increasing the count in the application layer logic, and the operation of the latter will overwrite the operation of the former.

How to test

Operate a table at the MySQL command line terminal

Mysql > begin

Query OK, 0 rows affected (0.00 sec)

Mysql > select * from users for update

+-+ +

| | id | name |

+-+ +

| | 1 | tom |

| | 2 | bob |

+-+ +

At this time, open another command line terminal.

Mysql > select * from users for update

^ C ^ C-- query aborted

ERROR 1317 (70100): Query execution was interrupted

Mysql > select * from users lock in share mode

^ C ^ C-- query aborted

ERROR 1317 (70100): Query execution was interrupted

You will find that neither for update nor lock in share mode can read the data, or rather, the query is blocked.

Only executed on the first terminal

Commit

The second terminal can get the data back.

It is important to note that the initiator must be locked in transaction to be valid, if not in transaction, locking is invalid. However, the second person will be locked whether he is in transaction or not.

I still have a few questions.

How to set the database operation timeout by Laravel

In what scenarios is it appropriate to use sharedLock?

What is the relationship between sharedLock,lockForUpdate and Pessimistic Locking

The difference between Pessimistic locking (pessimistic lock) and Optimistic locking (optimistic lock)

How to test Laravel

A user, accessing the interface in the browser (simulated payment callback), locks a row in the data table for 30s, and then commits the transaction.

B user, who accesses the same interface in the browser (simulated payment callback), cannot modify the line. What is the corresponding return?

Will wait until the database operation times out.

So the question is, how does Laravel set the database operation timeout?

A simple test method is to open two artisan tinker windows on the command line and execute

DB::transaction (function () {

Echo 1

User::where ('id', 33)-> lockForUpdate ()-> get ()

Echo 2

Sleep (10)

});

You will find the get operation in the second tinker window, and you will not get the query results until the first transaction has been executed.

It is important to note that lockForUpdate operations that are not in transaction have no locking effect.

Real scene to prevent users from repeatedly withdrawing cash

DB::transaction (function () use ($user, & $user_award) {

$user_award = UserAward::where ([

['user_id', $user- > id]

['status', 0]

])

-> lockForUpdate ()

-> first ()

If ($user_award) {

$user_award- > status = 1; / / status in cash withdrawal

$user_award- > save ()

}

});

If (! is_null ($user_award)) {

$amount = $user_award- > money * 100

}

The relationship between transaction and lock

All operations involved in the transaction are locked?

If locks are added by default, what locks will be added by default?

The operations involved in the transaction do not require explicit locking.

To sort out the relationships, you need to understand the four isolation levels of the transaction:

Uncommitted read (Read uncommitted)

Read submitted (Read committed)

Repeatable read (Repeatable read)

Serializable (Serializable)

MySQL defaults to repeatable readability (Repeatable read)

The above is how to use Laravel sharedLock and lockForUpdate for data table row locking. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.