Processing method of dirty read non-repeatable read and phantom reading caused by database concurrency

There is a problem when making ERP orders to access warehouse inventory. The order will read if the item is in stock. If there is then the order minus (can order inventory, the physical inventory is not the same) no indication of insufficient inventory

In the concurrency situation will cause U1 users to order 1 of the goods 1 U2 users also order 1 of the goods 1. There are only 1 items in stock with a number of 1. So when U1 reads out the data and discovers that the inventory has one but has not yet written the inventory the operation U2 also orders the order to be successful and submits then can cause the data to be disordered originally should be only U1 will order the success U2 then prompts the inventory to be insufficient

Here you can use the isolation mechanism of things or optimistic locks to solve

A thing is a logical unit

Atomicity: Inside a thing is a logical unit that is either executed or not executed.

Consistency: If the matter before and after the processing of the state of the agreement (a account has $200 B account has $300 total of $500 a account to the B account transfer 100 Yuan. The total amount of 2 accounts after the transaction is 500 yuan)

Isolation: Every thing has its own data space, so that the outcome of things will not be affected by other things

Persistence: The data that is submitted to the object is permanently preserved.

Isolation level of things

READ UNCOMMITTED reads non-committed

This isolation level may cause dirty reads. It can read data that has not been committed since the other thing changed. For example, in the case of warehouse inventory, read the product in one thing and modify the product stock to 0 but the thing has not yet been submitted another thing next order found no inventory hint inventory this time first thing cancel cause dirty read

Read Committed reads the submitted

This isolation level prevents dirty reads. Because only the data that the thing has committed is read. But will not be repeated read or warehouse as an example of a thing next order a commodity quantity of 1 inventory also 1 The thing of the moment has not been submitted another thing has been put orders and modified inventory to 0 cause data chaos because this thing isolation level can only read committed but can modify uncommitted data

REPEATABLE Read Repeatable reads

The isolation level of this thing solves the situation. When a thing is working on a piece of data, something else can neither read nor modify but it can cause phantom reading because uncommitted data cannot be modified and read but can be inserted and deleted data such as personal account consumption as an example A thing query uses the sum of a month's consumption records to make a credit score select SUM (Money) is read from table and consumes 2000 yuan in the month. When the thing has not been submitted user A wife at this time in the beauty salon to spend 5000 then the table table added a record of 5000 of the data at this time a phantom read (things read data and actual data do not match)

Serialization of Serializable

This isolation level is highest to avoid dirty read non-repeatable read Phantom read but inefficient (when a thing is manipulating a piece of data, something else cannot read and modify the data or insert data into the table)

Choose different object isolation mechanisms based on different concurrency scenarios

Optimistic lock

Even with data inconsistent in the case of concurrency

Take warehouse inventory as an example

Add a last Modified Time field to the warehouse table and use it as a condition

Warehouse Inventory table has a product can be under a single inventory of 1

U1 user orders a product read out to determine whether the inventory is sufficient enough to order in the things and update the available inventory update Stok set Num-=1,lastupdatedatetime=datetime.now where Id=a and LASTUPDA Tedatetime= Last Modified Time

Even before U1 commits something else than he commits before U1 commits lastupdate is updated by something else the condition does not set the commit failure (the number of rows affected is 0 roll-back order operation)

Note: Oracle only supports serializable Read Committed

Processing method of dirty read non-repeatable read and phantom reading caused by database concurrency