Oracle_ Advanced Features (8) Transactions and locks

Oracle Database transactions
1. Transaction definition
A transaction is a logical unit of work in a database,
A transaction consists of one or more SQL statements that complete a set of related behaviors.
The transaction mechanism ensures that the operations performed by this set of SQL statements are executed successfully, the entire unit of work is completed, or none is executed.
Examples of bank transfers are the most classic examples of transactions:
The user transfers money from one bank account to another and needs to withdraw funds from a bank account,
And then deposited in another bank account.
Ideally, these two operations should be successful.
However, if an error occurs, then two operations should fail.
Otherwise, the amount in one account will be wrong after the operation, and the entire operation process should be atomic,
Two operations are part of an atomic transaction operation.
Transaction composition:
One or more dml,[a DDL] and a DCL.

2. Transaction characteristics
The SQL92 standard defines four characteristics of database transactions:
Atomicity (atomicity): All the SQL statements contained within a transaction are performed as a whole, indivisible, either done, or not.
Consistency (consistency): At the beginning of a transaction, the data in the database is consistent, and the data of the database should be consistent at the end of the transaction.
Isolation (Isolation): refers to the ability of a database to allow multiple concurrent transactions to read and write data at the same time and to modify it.
Isolation prevents inconsistent state of data resulting from the cross-execution of their operations commands when concurrent execution of a transaction.
Persistence (Durability): When a transaction ends, its impact on the database is permanent, and the data is not lost even if the system encounters a failure.
For a set of SQL statement operations to become transactions, the database management system must ensure that the set of operations is atomic (atomicity), consistent (consistency),
Isolation (isolation) and persistence (durability), which is the acid characteristic.

3. Data anomalies
The following data exception occurs because Oracle supports concurrent execution of multiple transactions.
3.1 Dirty Reads
When one transaction modifies the data, another transaction reads the data.
But the first transaction cancels the modification of the data for some reason, so that the data is returned to its original state.
At this point the second transaction reads data that is inconsistent with the data in the database, which is called dirty reading.
such as: Transaction T1 modified a piece of data, but not yet committed, the transaction T2 just read the modified data,
At this point T1 rolls back the transaction, and at this point the data T2 reads is dirty data.

3.2 Non-repeatable READ
When a transaction reads data from a database, another transaction updates the data.
When the first transaction reads the data again, the data is changed, which is non-repeatable reading.
Non-repeatable reads result in a different data read two times before and after a transaction.
For example, transaction T1 reads a row of records, and the transaction T2 modifies the record that T1 just read.
Then T1 again, discovering that the record was different from the first read.

Declare
Begin
Dbms_output.put_line (' first reading: ');
For RS in (SELECT * from all_objects) loop
If Rs.object_name= ' EMP ' then
For rs1 in (SELECT * from emp) loop
Dbms_output.put_line (' Ename= ' | | rs1.ename| | ' sal= ' | | Rs1.sal);
End Loop;
End If;
End Loop;

Dbms_output.put_line (' second reading: ');
For RS in (SELECT * from all_objects) loop
If Rs.object_name= ' EMP ' then
For rs1 in (SELECT * from emp) loop
Dbms_output.put_line (' Ename= ' | | rs1.ename| | ' sal= ' | | Rs1.sal);
End Loop;
End If;
End Loop;
End

3.3 Phantom Reading
If a transaction reads data based on a condition, another transaction updates the data in the same table.
When the first transaction reads the data again, a different row is returned based on the search criteria, which is the Phantom read.
For example, the transaction T1 reads a statement that specifies the where condition and returns the result set.
At this point the transaction T2 inserts a new row of records that exactly satisfies the T1 's where condition.
Then the T1 uses the same conditions to query again, the result set can see T2 inserted record, this new record is phantom read.

These exceptions encountered in a transaction are related to the isolation setting of the transaction,
The more isolation settings of a transaction, the fewer exceptions appear, but the lower the concurrency effect;
The fewer isolation settings for a transaction, the more the exception occurs, and the higher the concurrency effect.

4. Transaction ISOLATION LEVEL
The isolation level of 4 transactions is defined in the SQL92 standard for inconsistencies that may occur when reading data:
Isolation level dirty read non-repeatable read Phantom read
READ UNCOMMITTED (READ UNCOMMITTED) Yes Yes is allowed dirty read, non-repeatable read, Phantom read
Read Committed (Read committed) No Yes yes allow non-repeatable read, Phantom Read, dirty read not allowed
REPEATABLE READ (Repeatable read) No no yes allow Phantom read, dirty read not allowed, non-repeatable read
Serializable (Serial read) No no no no no no dirty read, non-repeatable read, Phantom read

The default isolation level for Oracle is read committed.
Oracle supports two of the above four isolation levels: Read committed and Serializable.
In addition, the read only and read write isolation levels are defined in Oralce.
Read only: A transaction cannot have any action statement that modifies data in the database, and is a subset of the serializable.
Read write: It is the default setting, which means that there can be access statements, modify statements, but not frequently used in a transaction.

Setting the isolation Level
To set the isolation level for a transaction:
SET TRANSACTION Isolation level READ COMMITTED;
SET TRANSACTION Isolation Level SERIALIZABLE;
SET TRANSACTION READ only;
SET TRANSACTION READ WRITE;
Note: These statements are mutually exclusive and cannot be set to two or more than two options at the same time.
SET TRANSACTION Isolation level [READ committed| SERIALIZABLE]

To set the isolation level for a single session:
ALTER SESSION SET TRANSACTION isolation level READ COMMITTED;
ALTER SESSION SET TRANSACTION isolation SERIALIZABLE;

5. Transaction control commands
5.1 Committing a transaction
The transaction can be committed using the COMMIT statement,
When a commit statement is executed, the transaction is confirmed, the transaction is closed, the savepoint is deleted, and the lock is released.
When a commit statement is used to end a transaction, other sessions will be able to view new data after the transaction has changed.
To commit a transaction explicitly:
(1) to explicitly invoke the DCL.
(2) A commit is only used to actually write to the database and is persisted.
Implicit COMMIT TRANSACTION:
(1) Set Auto Commit (valid under Sqlplus), set autocommit on (only for one connection)
(2) The following cases are automatically submitted:
1) Normal execution of the completed DDL statement: Create, ALTER, DROP, truncate
2) Normal execution of the DCL statement Grant, REVOKE
3) Normal exit Sqlplus or SQL developer and other clients
5.2 Rolling back transactions
SavePoint (savepoint): A point in a transaction that cancels a partial transaction and automatically deletes all the savepoint-defined save points when the transaction is ended.
When rollback is executed, the specified savepoint can be rolled back to the specified point.
To set the save point:
Sql> SavePoint A;
--Delete Save point:
--sql> Release savepoint A;
To roll back a part of a transaction:
Sql> Rollback to A;
Roll Back all transactions:
Sql> Rollback;

6. Database lock
A database is a shared resource used by multiple users.
When multiple users access data concurrently, in the database, multiple transactions are generated concurrently to access the same data.
If the concurrency operation is not controlled, it is possible to read and store incorrect data and compromise the consistency of the database.
There are two basic types of locks in the database: Exclusive lock (Exclusive Locks, X Lock) and shared lock (Share Locks, S lock).
When a data object is added to an exclusive lock, other transactions cannot read and modify it;
Data objects with shared locks can be read by other transactions, but cannot be modified.

6.1 Lock classification
Depending on the protection object, the Oracle database lock can be divided into:
DML Lock (data locks): Used to protect the integrity of data.
DDL Lock (dictionary locks, Dictionary Lock): The structure used to protect database objects (such as the structure definition of tables, views, indexes).
Internal Locks and latches (internal lock and latch): Protects the internal database structure.
Distributed locks (distributed lock): used in OPS (parallel server).
PCM Locks (parallel cache management Lock): Used in OPS (parallel server).
The most important lock in Oracle is a DML lock, which is designed to guarantee data integrity in the case of concurrency.
In Oracle databases, DML locks mainly include TM and TX locks, where TM locks are called table-level locks, and TX locks are called transaction or row-level locks.
Locks appear on data sharing occasions to ensure data consistency.
When multiple sessions modify a table at the same time, the data needs to be locked accordingly.
There are many types of locks, such as "shared lock", "exclusive lock", "Shared exclusive lock", etc.
And each type has a "row-level lock" (one record at a time), "page-level lock" (one page at a time, that is, the smallest assignable unit that stores records in the database), "table-level lock" (locks the entire table).

6.2 Shared Lock (S lock)
The S lock can be added via the lock table in share mode command.
In this lockdown mode, no user is allowed to update the table.
However, allow other users to issue the lock table EMP in row share mode command to add RS lock to the table.

6.3 Exclusive Lock (x Lock)
The x lock can be added via the lock table in exclusive mode command.
In this lockdown mode, other users cannot perform any DML and DDL operations on the table, which can only be queried.

6.4 Row-Level shared lock (RS lock)
This is usually added through the lock table emp in row share mode statement.
For example, when we query some records, we do not want other users to update the records of the query, you can issue such a statement.
When the data is used, the direct issue of the rollback command unlocks the lock.
When RS lock is added to the table, no other transactions are allowed to add exclusive locks to the same table.
However, other transactions are allowed to lock other rows of data in the same table through the DML statement or the lock command.

6.5 Row-level exclusive lock (RX Lock)
When a DML operation is performed, the RX lock is automatically added to the table being updated.
Alternatively, you can explicitly add the RX lock on the table by executing the lock command.
In this lock mode, other transactions are allowed to modify other rows of data in the same table through DML statements.
or add the RX lock to the same table via the lock command,
However, other transactions are not allowed to add exclusive locks (x locks) to the same table.

6.6 Shared row-level exclusive locks (SRX locks)
Add the SRX lock via the lock table in share row exclusive mode command.
This locking mode is higher than the level of row-level exclusive and shared locks, and you cannot perform DML operations on the same table or add shared locks.

RS Lock is the least restrictive lock in the above several lock modes, and the X lock is the most restrictive lock.
Their compatibility relationship is as follows:
N/a RS RX S SRX X
N/A ok ok ok ok ok ok
RS ok ok ok ok no
RX OK OK no no no
S ok ok no ok no no
SRX OK no no no no
X OK no no no no

Basically all locks can be created and released automatically by Oracle internally,
However, the DDL and DML locks are commands that can be managed by the command syntax:
LOCK Table table_name in
[Row Share] [Row Exclusive] [Share] [Share row Exclusive] [Exclusive] MODE
[NOWAIT];
The following is a list of SQL statements that generate lock mode:
Table locking mode allowed in SQL statement table lock mode
SELECT * from N/A (1) n/A, RS, RX, S, SRX, X
Lock table emp in row share mode RS (2) n/A, RS, RX, S, SRX
Insert into RX (3) n/A, RS, rx
Update RX (3) n/A, RS, rx
Delete from RX (3) n/A, RS, rx
Select: For Update rx (3) n/A, RS, RX
Lock table EMP in row exclusive mode RX (3) n/A, RS, RX
Lock table EMP in Share mode S (4) n/A, RS, S
Lock table EMP in share row exclusive mode SRX (5) n/A, RS
Lock table EMP in exclusive mode X (6) N/A

When the program commits (commits) or rolls back (Rollback) The changes made, the locked resources are freed.
This allows other users to operate.
A deadlock occurs when two transactions lock part of the data, while waiting for the other to release the lock to complete the transaction operation.

7. Database transaction Implementation mechanism
in almost all database management systems, the mechanism of transaction management is implemented by using log files,
Let's briefly describe how the log works.
When a user executes a DML statement that modifies a database, the DBMS automatically writes a record in the log file
that displays two copies of each record that is affected by the statement.
One copy shows the record before the change, and the other copy shows the changed record.
-After the log is written, the DBMS actually modifies the records on the disk.
If the user then executes the commit statement, the transaction end is also logged in the transaction log, modifying the operation of the disk.
If the user executes the rollback statement,
--dbms checks the log to find out what records were modified since the beginning of the transaction "before",
--and then uses that information to restore their previous state, effectively revoking the changes made to the database during the transaction.
If a system error occurs, the system operator typically recovers the database by running a special recovery program provided by the DBMS. The
Recovery program checks to the end of the transaction log to find transactions that were not committed before the failure. The
Recovery program rolls back transactions that are not fully completed so that only committed transactions are reflected in the database, and the transactions that are processed in the fault are rolled back. The use of the
transaction log significantly increases the cost of updating the database.
in practice, the log technology used by mainstream commercial DBMS products is more complex than the scenario described above to reduce this overhead.
In addition, transaction logs are typically stored in high-speed disk drives, unlike disks that store databases, to reduce disk access contention.
Some personal computer DBMS products allow transaction log performance to be turned off to improve the performance of the DBMS.

8. Related Data dictionary
SELECT * from V$lock;
SELECT * from V$lock_type;
SELECT * from V$locked_object;
--select * from V$lock_activity;
--select * from V$lock_element;
--select * from V$locks_with_collisions;
SELECT * FROM Dba_lock
SELECT * from dba_views where view_name= ' dba_lock ';
SELECT * FROM Dba_locks
SELECT * FROM dictionary where table_name in (' Dba_lock ', ' dba_locks ');
SELECT * from Dba_lock_internal;

--querying objects that are locked
Select L.object_id,l.session_id,l.oracle_username,l.locked_mode,o.object_name
From V$locked_object l,dba_objects o
where l.object_id=o.object_id;

Oracle_ Advanced Features (8) Transactions and locks