For one of the most classic inventory oversold, assume that a data that is the case,

Id, brand name, a list of inventory such structure

Id product_sku inventory

10 1 SKU - 1



Looked at under a lot of online blog said, a common way is to add pessimistic or optimistic locking,
Implementation is about: the beginning and read lock: for update, other transactions can only be blocked waiting for the current transaction is committed, able to read lock on the row data
Optimistic locking with a version number field, the update submit time analysis of the current affairs in the process, any other process to modify the current data, if changed the failure

My understanding is:
Process a:
Select the inventory from the table where id=1 for update;
Find out the inventory;
Then $inventory=9;

Update the set inventory=9 WHERE id=1;
commit;


When the update is direct assignment,
Join the update when the update directly set the inventory=inventory - 1, not just don't need to add the lock?



Because of the update statement itself is not exclusive lock?

The simplest way of direct:
Process: the begin; Update the set inventory=inventory - 1 WHERE id=1;

Process 2: begin; Update the set inventory=inventory - 2 WHERE id=1;

Process at this time it is our update, there are exclusive lock lock line, process two direct blocking,

Wait for the process to a transaction is committed, releases the lock, 2 to execute process,

So don't have to solve problems of oversold? Why do you also need to use for update to achieve?

Not more simple to solve?

Great god solve,

CodePudding user response:

Requires the user to write explicit affairs, general business is a bit more complicated scenarios, you don't have to understand the key points,

Easy to understand with bank withdrawals,
If you have a 1000 yuan deposit, then:
A. on your mobile phone bank transfer of RMB 600,
B. your girlfriend took your bank card, 600 yuan on an ATM withdrawals,
A, B two operations, separate execution is no problem, but have A problem with implementation,
Transaction, that is, in order to avoid this kind of question,

You imagine, I said above scenario, no transaction, can do it?

CodePudding user response:

reference 1/f, gypsy song response:

requires the user to write explicit affairs, general business is a bit more complicated scenarios, you don't have to understand the key points,

Easy to understand with bank withdrawals,
If you have a 1000 yuan deposit, then:
A. on your mobile phone bank transfer of RMB 600,
B. your girlfriend took your bank card, 600 yuan on an ATM withdrawals,
A, B two operations, separate execution is no problem, but have A problem with implementation,
Transaction, that is, in order to avoid this kind of question,

You imagine, I said above scenario, no transaction, can do it?

I know what you mean,,, my above operations are the premise of the transaction,
That set the balance cannot be negative,

I take 600, for example,
A transaction:
begin;
The update table set account=account - 600 WHERE id=1;

Uncommitted transaction,

Girl friend take money 600,
Transaction 2:
begin;
The update table set the account WHERE id=1=account - 600 and the account & gt; 0;

A uncommitted transaction, transaction 2 block waiting for, such as transaction after a submit, will perform transactions two operations,
This time won't appear the situation of the buckle ah,

Why use optimistic locking, add the version number?

Is it because idempotent?? Such as repeated submissions?

CodePudding user response:

Simple, can be like you,
Not any more complicated,
Such as: to get the id, and then to operation, back to the table to the associated account do increase the balance,

In addition to this, there is a two users to enter modify a record at the same time, it is more common,
Even in the absence of competition, also want to consider,
Such as: two leading modify one employee wages at the same time,
Led 1 person first salary change from 2000 to 5000
Led 2 will change this person wage from 2000 to 5500

If allowed both directly modify, actually 2 leadership is not seen 1 modify the value of the leadership, because time is the same as the two men into the page, just submit the time different,
If the led 2 know someone to mend the workers' wages, probably won't change,

This kind of situation, use timestamp as the version number is very reasonable,
Leadership 2 in the presentation, the timestamp change the direct clue records have been changed, then back to the page at the next higher level,