Multi-User concurrency and transaction processing in Oracle

Multi-User concurrency and transaction processing in Oracle

Concurrent accesses to multiple Oracle users
Transaction: an inseparable operation acting on some data
 
Lock: Write lock, mutex lock (can only be used by one process) read lock, shared lock (can be used by multiple processes)
 
Update loss
Dirty read
Non-repeated read
Phantom read
 
Isolation level:
1 read committed get a complete view for each statement
2 SERIALIZABLE transaction-level implementation of serialization
 
Oracle concurrency
1 rollback segment: data structure that stores "undo" Information
Redo logs are used to record all transactions in the database. rollback segments are used to provide transaction rollback and read consistency.
2. System Change number SCN: ensures the transaction execution sequence
3. locks in data blocks: Each lock only affects one row of data blocks.
 
Conflicting write process:
1 A update operation
2. Obtain the SCN and read the target data block.
3. Record row lock Information
4. Modify and write the redo log Cache
5. Write rollback segments
6. Update B
7. Obtain the SCN and read data blocks.
8. locks are found. If read committed is used to continue after execution is complete; if it is SERIALIZABLE, an error is returned.
9 A commit A transaction
10 if B READ COMMITTED, continue to execute
 
Workspace: separated from data changes to create a data space of a specific version.

Online Transaction Processing OLTP
 
Atomicity
Consistency
Isolation
Durability
 
General features of OLTP:
1. Large transaction throughput and large concurrent users
2. Clear Performance Requirements
3 High Availability
4. scalability
 
General concurrency and performance:
1. Non-update row lock technology: only locks the rows processed by the transaction, and does not upgrade the lock level to the level of the transaction or the table level.
2 Multi-version read operation consistency: Statement-level and transaction-level data consistency is ensured without a read lock.
3. Shared SQL: The analyzed and optimized SQL statements are stored in the shared SQL zone of the shared memory pool.
4 Storage Overview: Support for execution plan Stability
 
Scalability:
Multi-thread server MTS
Oracle Net connection pool: allows clients to share a physical network connection pool
Oracle Net Connection Manager: assigns a network connection to multiple customers. No "timeout"
 
Real-time application cluster:
High-speed cache Fusion: stores all data in the cache of each host in the real-time application cluster. This data is available for any other host in urgent need.
 
High Availability:
1 standby database:
2 transparent fault migration TAF: automatically connects to another oracle instance when the host instance fails.
3 oracle stream/advanced queue AQ: An asynchronous or deferred inter-System Communication Method
4. Oracle stream replication: provides data redundancy using the built-in replication function