Oracle Transactions and Locks

Transaction:

definition: A transaction is a set of logical units containing one or more statements, each of which is an atomic unit, the statement in the transaction as a whole, either committed together, on the database, the database data is permanently modified, or is revoked together, the data does not make any changes. Mainly used to ensure data consistency.

Example :

Account a extracts $1000, puts it into account B, and logs the operation.

Transaction Basic CONTROL statement:

Set TRANSACTION: Setting transaction properties

Commit: Commit a transaction

savepoint: Set Save point

ROLLBACK: Rolling back a transaction

ROLLBACK to SavePoint: Rollback to save point

Type of transaction:

Explicit transactions: is the use of command completion, self-setting commit/rollback, to set the commit or Rollback, Oracle is not like Java, do not set the start identity, the transaction will open; ( the first execution of a DML statement after logging into the database is the beginning of a transaction; When the last transaction ends, The first time the DML is executed begins the next transaction )

Implicit transactions: You do not need to set commit/rollback yourself. Such as

A. When a program ends normally, such as when using SQL Plus to exit, the previous SQL will commit;

B.DDL Statement Auto-commit

C.set autocommit on/off (default is off, all DML displays transactions and requires self-setting commin/rollback), on is automatically committed or rolled back each time the DML system is executed.

Save points for transactions:

Similar to dividing a long transaction into multiple short transactions, after the short transaction is over, mark a savepoint name, and when the next transaction commits a problem, you can specify that rollback to name roll back to the end of the name. The benefit of a savepoint is that it does not need to be rolled back when a problem occurs.

A. Transactions are rolled back only to the point after the savepoint

B. When you roll back to a savepoint, its future savepoint is deleted, but the previous save point is retained

C. The lock after the savepoint will be released, but the previous will be retained.

Instead of relying too much on the savepoint, you should try to change the long transaction to a shorter transaction operation.

the acid of a transaction: atomicity, consistency, separation, persistence.

Note the point:

The use of transactions in Oracle, the end of a transaction 1. When autocommit is off (default), remember to set commit/rollback,2 yourself. When the DDL is executed, commit automatically when the 3.sql*plus exits normally, When an exception exits, rollback.

When the current session execution transaction is not committed, the other session does not see the modification of the current session, but the current session is visible, for example, the current session modifies a data, and the current session is queried for the modified data

Lock:

Oracle uses very low constraints to provide maximum concurrency, and when a session modifies a row of records, only that line is locked, the other sessions can be read at any time, and the data is read or modified before the lock guarantees the separation of the transaction and prevents the transactional interaction from causing inconsistent data. Many of the locks in the database are automatically added and freed, such as committing transactions, and Oracle allows us to set them manually.

Locks can be divided into write and read locks from the operational permissions:

Exclusive (write-lock/X-lock/read-write Lock): When modifying data, exclusive, that is, when transaction T adds an exclusive lock to data A, other transactions are no longer able to add any locks to the data, allowing only transaction T to read and write to data A, and other transactions should be readable.

Shared lock (read lock/s lock/read-only lock): Shared lock data can only be read and cannot be modified. The data has been added to the shared lock case, no additional exclusive locks, but can be added to share the lock.

From the function object of the lock:

DML Lock: A data lock that is used to protect data. Refers to the locks used when executing DML, divided into row-level locks (TX, also known as transaction locks) and table-level locks (TM).

A row-level lock is an exclusive lock that locks a row of data when it modifies a row of records, and does not allow other transactions to be modified.

table-level locks are used to prevent changes in the structure of the table when data is modified. A transaction acquires a table-level lock before it modifies data, and then acquires row-level locks. Therefore, when transaction a modifies the data, no transaction is allowed to modify the table structure.

When Oracle executes DML statements, the system automatically requests a TM type lock on the table to be operated on. When the TM lock is obtained, the system automatically requests the TX type lock and resets the lock flag bit of the data row that is actually locked. In this way, checking the compatibility of the TX lock before the transaction is locked, it is necessary to check the compatibility of the TM lock mode and greatly improve the efficiency of the system. TM Lock includes RS, RX, S, SRX, X and other modes, in the database with 0-6 to represent. Different SQL operations produce different types of TM locks

(1) row sharing (row SHARE) – a shared lock that prohibits exclusive locking of tables,

(2) Row exclusive (row EXCLUSIVE) – Exclusive and shared locks are not allowed

(3) Shared lock (SHARE)-Locks the table, allows other users to query only rows in the table, prevents other users from inserting, updating, and deleting rows, and multiple users can apply the lock on the same table at the same time

(5) Shared row exclusive (SHARE row EXCLUSIVE) – More restrictions than shared locks, prohibit use of shared locks and higher locks

(6) Exclusive (EXCLUSIVE) – Restricts the strongest table locks, allowing only other users to query rows of the table. Prohibit modifying and locking tables

Row-level Locks:

(The following sentence will add a lock to all rows of the table)

SELECT * from person for update;

If the row record has been locked, you do not have to wait, the system will directly throw the wrong ora-00054

SELECT * FROM person WHERE id = ' 1 ' for update nowait

If the row record has been locked, the update waits 5 seconds, if this 5 seconds, the row record is unlocked, then return the query results, if not unlocked within 5 seconds, then the system will be thrown directly wrong ora-00054

SELECT * FROM person for update wait 5;

In addition, if you use SELECT * FROM person WHERE id = ' 1 ' for update, when the row record is locked, the system waits for the row record to be freed and then locked.

Table-Level Locks:

Row sharing: Allow users to do anything, prohibit exclusive locks

Lock table person in row share mode;

Row Exclusive: Allows the user to do anything, prohibit shared and exclusive locks

Lock table person in row exclusive mode;

Shared Lock: Other users can only see, cannot modify

Lock table person in share mode;

Shared row Exclusive: More restrictions than shared locks

Lock table person in share row exclusive mode;

Exclusive Lock: Other users can only see, cannot modify, cannot add other locks

Lock table person in exclusive mode;

For locks that are actively added through the Lock Table command, if you want to release them, you only need to issue the rollback command to

According to the type of lock, there are 6 kinds of

1, NULL, this lock can be generated in some cases, such as a query for a distributed database.

2, RS, table structure shared lock

3, RX, table structure shared lock + manipulated record of the exclusive lock

4, S, table structure shared lock + all record share lock (table structure read-only and all row Records read only)

5, SRX, table structure shared lock + all record exclusive lock

6, X table structure row It lock + all records exclusive lock, you can modify and view the table structure and data, other transactions can only read

SELECT * from Able_name for update is RX, it is said that the old version of the document is RS, after the RX

Http://blog.chinaunix.net/uid-25909722-id-3387609.html

650) this.width=650; "class=" En-media "src="/shard/s5/res/1efca714-a472-4f4b-bed3-0e99bcb1ec58 "alt=" 1efca714-a472-4f4b-bed3-0e99bcb1ec58 "/>

650) this.width=650; "class=" En-media "src="/shard/s5/res/f3713e6f-a123-41e8-a707-7b7f2fd7ca11 "alt=" F3713e6f-a123-41e8-a707-7b7f2fd7ca11 "/>

Lock and Unlock: Executing DML in Oracle automatically adds TM locks to the table, can be manually added with lock, and unlocked with rollback

DDL Locks: used to protect the structure of objects, data dictionaries, table structures, etc., users cannot display the requirement to use DDL locks

internal latch: protects the internal results of the database and is fully invoked by the system itself.

This article from "11950462" blog, declined reprint!

Oracle Transactions and Locks