Oracle Transactions and Locks
2017-12-13
Directory
1 Summary of database transactions
1.1 Transaction Definitions
1.2 Transaction life cycle
1.3 Characteristics of Things
1.4 Dead Lock
2 Transaction-related statements
2.1 Summary of transaction-related statements
2.2 Establishing the Transaction isolation level
3 lock
3.1 Lock mode
3.2 Optimistic lock and pessimistic lock
Reference
1 Summary of database transactions
Return
1.1 Transaction Definitions
A database transaction is a sequence of operations performed as a single logical unit of work, either completely or completely Transaction.
1.2 Transaction life cycle
Start and end of a transaction
Start transaction: Connect to Database, execute DML, DCL, DDL statement
End transaction: 1. Execute DDL (for example, create TABLE), DCL (for example, Grant), the system automatically executes a COMMIT statement
2. Executive Commit/rollback
3. Exit/Disconnect the database automatically executes a COMMIT statement
4. Unexpected process termination, transaction auto rollback
5. Transaction commit will generate a unique system change number (SCN) saved to the transaction table
Commit and rollback of a transaction: Commit/rollback
Save Point (SavePoint): You can set the savepoint anywhere in the transaction so that rollback
1.3 Characteristics of Things
The four features that a firm should have:
- Atomicity (atomicity): atomicity means that transactions in a database are executed as atoms. That is, the entire statement is either executed or not executed.
- Each T-SQL statement can be thought of as being wrapped in a transaction by default, and SQL Server is atomic for each individual statement.
- If the user wants to define the size of the atom themselves, it needs to be included in the transaction to form the user-defined atomic granularity.
The custom atomicity of a transaction implementation is often business-related, such as bank transfers, minus 100 from a account, and a 100 increase in the B account, if the two statements do not guarantee atomicity, for example, after subtracting 100 from a account, the server loses power and the B account does not increase by 100. As a customer, you don't want this to happen.
- Consistency (consistency): The integrity constraints of the database are not compromised until the transaction begins and after the transaction has ended.
Consistency is divided into two levels:
- Database mechanism level
Database-level consistency is that the data conforms to the constraints (unique constraints, foreign KEY constraints, check constraints, and so on) and trigger settings that you set before and after a transaction executes. This is guaranteed by the database.
- Business level
For the business dimension, consistency is what keeps business consistent. This business consistency needs to be guaranteed by the developer. Many aspects of business coherence can be ensured by moving to the database mechanism level. For example, the product has only two models, you can transfer to use a check constraint so that a column must only be stored in these two models.
- Isolation (Isolation): Isolation. The execution of a transaction is non-disruptive, and a transaction cannot see the data in the middle of a time when other transactions are running.
The interaction between transactions is divided into 4 categories:
- Dirty reads: The data that a transaction reads is data that is being processed by another transaction. Another transaction may be rolled back, causing the data read by the first transaction to be incorrect.
- Non-repeatable reads: Two reads of data in one transaction, but another transaction has changed the data involved in the first transaction, causing the first transaction to read into the old data.
- Phantom reading: A phenomenon that occurs when a transaction is not executed independently. For example, the first transaction modifies the data in a table, which involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred.
- Update lost: Multiple transactions read a data at the same time, one transaction successfully processed the data, the other transaction is written back to the original value, causing the first transaction update to be lost.
- Persistence (Durability): means that the changes made to the database by the transaction are persisted in the database and are not rolled back after the transactions have been completed.
Even if there is an accident such as a power outage, the transaction is persisted in the database once it is committed.
1.4 Dead Lock
When two transactions wait for each other to release resources, a deadlock is formed
2 Transaction-related statements
Return
2.1 Summary of transaction-related statements
SET TRANSACTION -- Set transaction Properties SET CONSTRAINT -- Set Constraints SavePoint - -establishing a storage point -- releasing the storage point ROLLBACK -- rollback commit -- commit
2.2 Establishing the Transaction isolation level
SET TRANSACTION READ only --there cannot be any action statement in the transaction that modifies data in the database, including INSERT, UPDATE, DELETE, create statementSET TRANSACTION READWRITE--the default setting, which indicates that there can be access statements, modify statements in the transaction--level of isolation supported by Oracle: (Dirty read not supported)SET TRANSACTION Isolation Level READ COMMITTED --do not allow dirty reads, allow fantasy read and non-repeatable readSET TRANSACTION Isolation Level SERIALIZABLE --None of the above three types allow serialzable to perform DML operations--The SQL standard also supports READ UNCOMMITTED (three types are allowed) and repeatable read (does not allow non-repeatable reads and dirty reads, allowing only fantasy reads)
Note : These statements are mutually exclusive and cannot be set to two or more than two options at a time
3 lock
Return
The transaction isolation level is the overall solution for concurrency control, which in effect solves concurrency problems by combining various types of lock and row versioning.
3.1 Lock mode
Two mode locks: Exclusive (x Lock) and shared (s) Lock:
- An exclusive lock is also called a write lock. This mode of lock prevents the sharing of resources and is used as data modification.
If a transaction t adds the lock to data A, the other transaction will not be able to add any locks to a, so only T is allowed to read and modify the data until the transaction finishes releasing the lock of that type.
- A shared lock can also be called a read lock. The data locked by this mode can only be read and cannot be modified.
If a transaction t adds a shared lock to data A, then the other transaction cannot add an exclusive lock, only the shared lock. The data that is added to the lock can be read concurrently. Locks are the primary means of implementing concurrency, many of which are automatically managed by the database and automatically released when the transaction is committed.
Further subdivision:
- RS (row-level shared lock, row SHARE): This mode does not allow other parallel sessions to use exclusive locks on the same table, but allows them to use the DML name or the lock command to lock other records in the same table. Select ... the from-for update name is the RS lock that is added to the record. .
- RX (Row-level exclusive lock, row EXCLUSIVE): This mode allows parallel sessions to modify other data in the same table, but does not allow parallel sessions to use exclusive locks on the same table.
- S (Shared Lock, SHARE): In this mode, the session is not allowed to update the table, but allows RS lock to be added to the table.
- SRX (shared row-level exclusive lock, SHARE row EXCLUSIVE): In this mode, you cannot perform DML operations on the same table or add S locks.
- X (exclusive lock, EXCLUSIVE): In this mode, other parallel sessions cannot operate on the table DML and DDL, and the table is read-only.
Table 1, Oracle Table-level lock 5 modes of compatibility
Table 2, table-level lock mode generated by various statement locks in Oracle
3.2 Optimistic lock and pessimistic lock
Optimistic lock
- An optimistic lock does not lock anything, that is, it does not depend on the transaction mechanism of the database, and the optimistic lock is a purely application-level thing.
- If you use optimistic locking, the database must have a version field, otherwise you can only compare all fields, but because floating-point types cannot be compared, it is not feasible to actually have a version field.
Pessimistic lock
- Pessimistic locking refers to the assumption that concurrent update conflicts occur, so the lock mechanism is used regardless of whether the conflict actually occurs.
- Pessimistic locks perform the following functions: Lock the Read record and prevent other transactions from reading and updating the records. Other transactions will continue to block until the transaction ends.
- Pessimistic lock is the use of database transaction isolation function, based on exclusive access to resources, so as to ensure the consistency of read data to avoid loss of modification.
- Pessimistic locks can use the repeatable read transaction, which fully satisfies the pessimistic lock requirements.
Reference
[1] Oracle Transactions and Locks
[2] Pessimistic lock and optimistic lock
[3] Acid analysis of transactions in SQL Server
[4] Oracle View locked table and unlock
[5] Oracle Partial SQL for lock table queries
[6] Oracle Lock Table Query and Unlock method
Oracle Transactions and Locks
