Using locks for concurrency control in Oracle

Now the mainstream database management system, both support the implementation of multiple transactions at the same time, so as to improve the operation of the database management system efficiency. Just imagine that if only one transaction is allowed to run, and the transaction takes a long time, other users must wait for the transaction to end, and how inefficient. While performing different transactions can improve performance but may compromise the integrity of the data, we must weigh the performance against the data integrity. So what is concurrency control? Concurrency control is the behavior that a database management system coordinates multiple running transactions. First, look at the three problems that are frequently encountered in concurrency control.

Dirty Read

We use a Products table to explain what is dirty read, the Products table has a list of quantity (quantity), now the value is 20. If there are now two transactions T1 and T2, all of which are to update the quantity column, T1 the column value 100,t2 to subtract this column by 10, but the T1 execution failed to rollback. It is easy to work out the correct result 20-10=10, but what can be produced if the transaction runs in the following way?

Time Transaction Steps Store values

1 T1 read out the value of quantity 20

2 T1 quantity=20+100 120

3 T1 Write Quantity value 120

4 T2 read out quantity (T1 not submitted) 120

5 T2 quantity=120-10 110

6 T1 rollback (rollback) 20

7 T2 Write Quantity value 110

The above 110 results, obviously not correct, the problem is that T2 read the T1 did not submit the data, we call this case dirty read.

Do not read repeatedly

or transaction T1 and T2, all of which are to update the quantity column, T1 add the column value 100,t2 this column minus 10, and two transactions are successful. It is easy to work out the correct result 20+100-10=110, but what can be produced if the transaction runs in the following way?

Time Transaction Steps Store values

1 T1 read out the value of quantity 20

2 T2 read out the value of quantity 20

3 T1 quantity=20+100 120

4 T2 quantity=20-10 10

5 T1 Write Quantity value (update lost) 110

6 T2 Write Quantity value 10

It is still incorrect to draw a 10 result. The problem is that T2 values cover the value of T1, which we call a non repeatable read.

Hallucinations Read

For example, T1 modifies all rows in a table, while T2 inserts a row into the table. There are also data rows that have not been modified in the T1, as if the illusion had occurred.

The SQL92 standard defines four isolation levels to address the above issues, and four isolation levels are shown in the following illustration: