Oracle Lock Mechanism

The database uses a lock to ensure that at most one transaction can modify a given piece of data at any given time. Essentially, it is the lock mechanism that makes concurrency control possible.

Oracle Blocking Policy:
1. Oracle locks data at the row level only when the data is modified. Do not upgrade the lock to block or table level.
2. Oracle will never lock data for reading. For simple reading, you cannot set a lock on the Data row.
3. The data writer does not block the data reader.
4. Only when a row of data has been locked by another data writer can others write data in the row be blocked. The data reader will never block the data writer.

 

 

-------------------------------------------------------------------------
A simple view of Oracle's concurrent processing mechanism. Http://www.51testing.com/html/97/n-131297.html

The reprinted content is as follows:

During Oracle development, If you test your application independently, deploy the application, and submit it to dozens of concurrent users, it is very likely that the concurrency problems that were not previously detected will suffer. For example, if two users modify an order at the same time, they first query whether the order exists or not. If so, modify the order status. In concurrent operations, user 1 may find that his modifications are lost strangely. Of course, in addition to this, there may be situations where the concurrent problem cannot be well handled:

◆ Damage data integrity.

◆ As the number of users increases, the running speed of applications slows down.

◆ Applications cannot be scaled out to support a large number of users.

To solve these problems. The Oracle lock mechanism should be introduced first. The database uses a lock to ensure that at most one transaction can modify a given piece of data at any given time. Essentially, it is the lock mechanism that makes concurrency control possible. For the Oracle lock mechanism, see the Oracle official documentation. The following is a summary of Oracle locks.

Oracle only adds row-level locks to data when modification. Under normal circumstances, it will not be upgraded to block-level locks or table-level locks (except for a short period of time during the two-segment commit period, which is an uncommon operation ).

◆ If only data is read, Oracle will never lock the data. It will not be locked on the Data row because of simple read operations.

◆ Writer does not block the reader ). In other words, read is not blocked by write. This is almost different from all other databases. In other databases, reading is often blocked by writing. Although it seems that this feature is quite good (normally it does), if you do not fully understand this idea and want to apply integrity constraints to the application through the application logic, it is very likely that you have not done the right thing.

◆ The writer wants to write a certain row of data, but the other writer has locked this row of data, so this writer will be blocked. The reader will never block the writer.

Developers should consider the above factors as much as possible. We also need to be aware of the uniqueness of Oracle in these cases. For other databases, the lock application is slightly different.

Take DB2 as an Example

1. Oracle uses the multi-granularity blocking mechanism with intention locks to control concurrency to ensure data consistency. Its DML lock (Data lock) is divided into two levels (granularity): Table-level and row-level. Generally, DML operations only obtain the intention lock (RS or RX) at the table level, and the real blocking granularity is still at the row level; DB2 also uses a multi-granularity blocking mechanism with intention locks to control concurrency to ensure data consistency.

Its DML lock (Data lock) is divided into two levels (granularity): Table-level and row-level. Generally, DML operations obtain only the intention lock (is, six or IX) at the table level, and the real blocking granularity is also at the row level. In addition, in the Oracle database, simply reading data (select) is not locked, which improves the system's concurrency. Oracle emphasizes the ability to "read" data and quickly read data. DB2 locks emphasize "read consistency". When reading data (select), values of S, is are added based on different isolation levels (RR, RS, and CS, is lock, which is not locked only when ur isolation level is used. To ensure different applicationsProgramIt is consistent with the data read by the user.

2. while supporting high concurrency, DB2 and Oracle have different locking mechanisms: Oracle uses design techniques such as intention locks and lock marks on data rows, this reduces the cost of Oracle's Row-Level Lock maintenance and makes it advantageous in database concurrency control. In DB2, each lock will apply to allocate a certain byte of memory space in the lock memory (locklist), specifically the X lock 64 byte memory, s lock 32 byte memory (note: before DB2 V8, X locks 72 bytes of memory and S locks 36 bytes of memory ).

3. There is no lock upgrade in the Oracle database. When the row-Level Lock usage in the database table exceeds the locklist * maxlocks In the DB2 database, the lock upgrade will occur.

4. in Oracle, when a session is used to insert, update, and delete a table, another session can still read the table's front image (before image) from the mongoe rollback segment or restore the tablespace ); in DB2, when a session is used to insert, update, or delete a table, the other session is still in the lock wait status when reading the table data, unless the UR isolation level is used, the uncommitted values of the first session can be read. Therefore, different sessions in Oracle have read inconsistencies at the same time, all sessions of DB2 at the same time are read-consistent.