Oracle DTL Data Transaction language

DTL Data Transaction Language

Definition of a transaction:
Refers to a set of related SQL operations, all of our operations are in the transaction.

Note: In a database, the basic unit of execution of a business is "transaction", not a single SQL.
By default, a transaction is open, that is, it is in a transaction, and the end of a transaction represents the opening of the next transaction.
When a commit or rollback instruction is executed, the current transaction is ended

Function: To ensure the smoothness and predictability of data
Example: Bank transfer business
"Sql1:a account transfer to B account 10000
Update Tbl_account set balance=balance-10000 where Accountno=a account;
SQL2:
Update Tbl_account set balance=balance+10000 where accountno=b account;
SQL1 and SQL2 must be in the same transaction, guaranteeing both success or failure at the same time. 】

Four characteristics of a transaction (ACID):

　　Atomic, atomicity, affairs are indivisible, either succeed at the same time, or fail at the same time;
Consistency, consistency, transaction once the end, in-memory data and data in the database are consistent;
Isolation, isolation, non-interference between transactions, the end of a transaction means the opening of the next transaction;
Duration, persistence, once a transaction is committed, the data is persisted to the database and persisted

In Oracle, the command to manipulate the transaction:
1.commit, COMMIT Transaction
Persist all data in a transaction to disk

2.rollback to rollback point, rollback TRANSACTION
Cancels all operations in the transaction and returns to the initialization state

3.savepoint rollback POINT, set rollback point
Rollback to the starting point when a transaction is rolled back

Summarize:
1. The current mainstream database is support transactions, and the best Oracle support
2. One transaction cannot read data that has not yet been committed by another transaction
The 3.DDL statement will automatically commit the transaction
The 4.DML statement does not commit the transaction automatically and requires a manual commit commit

Multi-transaction concurrency processing mechanism:
Cause: Multiple transactions manipulate the same row of data in a table at the same time, and if these operations are modified at the same time, concurrency problems can occur, and if not processed, data inconsistencies can occur

The concurrency problems that a database can produce include:
1. Dirty Reading
Refers to a transaction that is accessing the data and making modifications to the data that have not yet been committed to the database, and that the other transaction has accessed the data and used that data.
WORKAROUND: When a transaction modifies data, the data cannot be accessed by other transactions

2. Non-repeatable reading :
Refers to a transaction that reads the same record more than once, and if another transaction accesses and modifies the data at this point, there will be multiple reads with inconsistent data, which turns out to be non-repeatable data.
Workaround: The data can only be read after the modified transaction is fully committed

3. Phantom Reading
Refers to a transaction that modifies multiple rows of records in a table, but when another transaction inserts data into the table, the first transaction finds rows in the table that are not modified, as if there were illusions;
Workaround: Other transactions cannot add data until a transaction submits data

"The use of ' Lock ' in Oracle for concurrent processing"
1. Table level exclusive lock (X) Exclusive mode
2. Table-Level shared locks (S) Share mode
3. Table row level exclusive lock share row Exclusive
Note: These three kinds of locks are applied through special commands.

Grammar:
Lock table tbl_name in mode;

For example:
--To share the lock table
Lock table Tbl_emp in share mode;

--to lock the table in rows
Lock table Tbl_emp in exlusive mdoe;

4. Row-level shared lock (RS) Row share

5. row Level exclusive lock (RX) row exclusive
Note: These two locks do not need to be requested by a special command, but are applied via DQL and DML

Attention:
1. All DQL statements will automatically apply for RS lock by default
2. All DML statements are automatically requested by default for Rx locks, and each row record has a unique RX lock
3. In the project, in order to meet the business requirements, the general SELECT statement needs to apply for the RX lock

The SELECT statement requests the RX lock with the FOR update:
SELECT * from S_emp for update;
SELECT * FROM s_emp for update wait 5; Wait 5 seconds
SELECT * from s_emp for update nowait;

Oracle DTL Data Transaction language