A simple view on Oracle concurrent processing mechanism

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. Database uses lock) to ensure that at any given moment, only one transaction can modify a given piece of data. 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) reader is not blocked ). 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 this feature seems to be quite good in general), 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 has two levels of granularity): Table-level and row-level. Generally, DML operations only obtain the intention lock RS or RX at the table level. 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 has two levels of granularity): Table-level and row-level. Generally, DML operations only obtain the intention lock IS, SIX or IX at the table level. The real blocking granularity IS also at the row level. In addition, in the Oracle database, read 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), the values of S, IS, IS lock, which IS not locked only when UR isolation level IS used. This ensures that the data read by different applications and users is consistent.

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 be applied to allocate certain bytes of memory space in the lock memory locklist. Specifically, the X lock is 64 bytes of memory and the S lock is 32 bytes of 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 former image before image of the table from the rollback e segment or the restored 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.

What you are reading is a simple view on the Oracle concurrent processing mechanism

1. Three methods to simplify the management of Oracle table columns
2. Differences between Query rownum and rowid in Oracle
3. Table space query in Oracle