Three phenomena:
- Bloat reading: A transaction reads data written but not submitted by other transactions.
- Repeatable reading: a transaction has been read again and found that the data has been modified or deleted by another submitted transaction.
- Read does not exist: the transaction is re-queried according to the previous query conditions, and the returned results include new data that meets the conditions inserted by other committed transactions.
Standard isolation level of sql92
- Uncommitted read
- Read committed
- Cannot be read repeatedly
- Serializing
|
|
|
|
| Symptom |
Dirty read |
Cannot be read repeatedly |
Read does not exist |
| Isolation level |
| |
| Read uncommitted) |
Allow |
Allow |
Allow |
| Read committed) |
Not Allowed |
Allow |
Allow |
| Repeatable read) |
Not Allowed |
Not Allowed |
Allow |
| Rerializable) |
Not Allowed |
Not Allowed |
Not Allowed |
|
Oracle supports transaction isolation-level committed read: serialized isolated transactions can only see data submitted before the transaction is executed, and data modification by the insert, update, and delete statements in the transaction. The serialized and isolated transactions do not support repeated or non-existent reads. Read-only transactions can only view the data submitted before the transaction is executed, in addition, the insert, update, and delete statements set transaction isolation level read commitedset transaction isolation level serializableset transaction read only dirty data is read to the database buffer cache but not written to datafile. data, as long as no data file is written, whether submitted or not, it can be called dirty data.
Lock: a mechanism to prevent transactions that access the same resource from generating destructive interactions.
Lock mode: exclusive lock: prevents shared locked resources. The lock in this mode must be obtained when data is modified. The first exclusive transaction that locks the resource is the only transaction that can modify the resource until the exclusive lock is released: resources that are locked can be shared conditionally according to the operation type. Multiple users who read data can share the data. These users can share the lock on the resource to prevent other users from modifying the resource concurrently. Multiple transactions can apply a shared lock to the same resource.
| |
| Lock |
Description |
| |
DML lock (Data lock)
|
DML is used to protect data. For example, table lock locks the entire table, and row lock locks the selected data rows.
|
DDL lock (data dictionary lock)
|
DDL locks are used to protect the structure of solution objects. For example, table and view definitions.
|
Internal lock and latch)
|
Internal locks and latches are used to protect the internal structure of the database, such as data files. Oracle automatically manages internal locks and latches.
|
Row share table lock RS: indicates that the transaction with this lock has locked some data rows in the table, and intentionally update the data. Select .... from table .... for update ...... lock table in row share mode allow operations: after a transaction has a row share table lock for a table, other transactions can still concurrently query, insert, and update the same data table, delete or lock the data rows in the table. That is to say, other transactions can also obtain row-level locks for the same table, row exclusive sharing, and table-level locks for the shared row exclusive mode. Prohibit operation: after a transaction has a row-shared table lock for a table, only other transactions are prohibited from performing write operations on the same table through the following statement. Lock table in exclusive mode; row exclusive table lock RX: transactions that usually have this lock on the surface have updated some data in the table. Insert into table ..... update table ..... delete from table ..... lock table in row exclusive mode; operation allowed: after a transaction has a row exclusive table lock for a table, other transactions can still concurrently query and insert the same data table, update, delete, or lock data rows in the table. The row exclusive table lock allows multiple other transactions to simultaneously obtain row-level shared table locks or row-level exclusive table locks on the same table to prohibit operations: after a transaction has a row exclusive table lock for a table, other transactions are prohibited from manually locking the table for exclusive read/write operations, therefore, other statements cannot lock the same table. Lock table in share mode; lock table in Shae exclusive modelock table in exclusive mode; Share table Lock S ): lock tabe table in Shae mode: after a transaction has a table-level shared table lock, other transactions can query the table. You can use select ...... the for update statement locks the selected data row and successfully executes the lock table ....... in share mode statement. However, other transactions cannot update Tables. Multiple transactions can concurrently obtain the shared table lock on the same table. In this case, no transaction can update the table. Therefore, a transaction with a shared table lock can only perform update operations on the table without the shared table locks of other transactions: when a transaction has a table share lock, other transactions are prohibited from modifying the table, meanwhile, other transactions are prohibited from executing the following statements: Lock table in row exclusive modelock table in share row exclusive mode; lock table in exclusive mode; share row exclusive table lock SRX: Lock table in share row exclusive mode; Operation permitted: at the same time, only one transaction can obtain exclusive table-level locks for the table's shared rows. After a transaction has exclusive table-level locks for a table's shared rows, other transactions can query the table, you can use select .... the for update statement locks the selected data row. Updating a table is prohibited: transactions with exclusive table locks for shared rows will prevent other transactions from obtaining row exclusive table locks to modify data, the exclusive table lock of shared rows can also prevent other transactions from getting the shared table lock on the same table, the exclusive table lock of shared rows, and the exclusive table lock Table table in row exclusive mode; lock table in share mode; lock table in share row exclusive mode; lock table in exclusive mode; exclusive table lock X ): lock table in exclusive mode; operation allowed: Only one transaction can obtain the exclusive table lock at the same time. After a transaction acquires a table-Level Lock, other transactions can only perform query operations on the table. Prohibited operations: after a transaction acquires an exclusive table-Level Lock, other transactions are prohibited from performing any DML operations on the table, other transactions cannot obtain any types of table locks.
