Oracle transactions and locks

Oracle transactions and locks 1. transaction concept: a transaction is used to ensure data consistency. It consists of a group of related dml (data operation language [add, delete, modify]) statements. The dml statements of this group are either all successful. Or all failed. * Transactions have four features: ACID atomicity: Statement-level atomicity, process-level atomicity, and transaction-level atomicity consistency: State consistency, there will be no isolation of two States in the same transaction: transactions are separated from each other (Autonomous transactions may also exist here) Persistent durability: the transaction is committed, the State is a permanent * read-only transaction: a read-only transaction is a transaction that allows only query operations, but not any other dml operations, with read-only transactions, you can only obtain data at a certain time point. Assume that the ticket sales site starts to count the current sales situation at every day, then you can use read-only transactions. After a read-only transaction is set, although other sessions may commit new transactions, the read-only transaction will not get the latest data changes, so as to ensure that the data information at a specific time point is obtained. Set read-only transactions: set transaction read only; 2. transaction advantages: * logically related operations are divided into one group. * You can preview data changes before the data changes permanently. * This ensures data read consistency. The COMMIT operation modifies the database by multiple steps and permanently writes data to the database at a time, which indicates the successful execution of database transactions. In the event of a problem, the ROLLBACK operation revokes the modification made to the database and rolls back to the status before the modification. If a problem occurs during the operation and the operation has not been submitted, you can use ROLLBACK to cancel the previous operation at any time. A savepoint is used to create some storage points in the middle of the transaction. ROLLBACK can roll back the operation to these points without canceling all the operations. Once COMMIT is completed, ROLLBACK cannot be used to cancel submitted operations. Once ROLLBACK is complete, the Undo operation must be redone and related operation statements must be re-executed. Database Transaction 3. database lock * Lock Mechanism in Oracle Database the database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions can access the same data simultaneously in the database. If concurrent operations are not controlled, incorrect data may be read and stored, compromising Database Consistency. There are two basic lock types in the database: Exclusive Locks and Share Locks ). When an exclusive lock is applied to a data object, other transactions cannot read or modify it. Data Objects with a shared lock can be read by other transactions but cannot be modified. * Oracle database lock classification (based on different protected objects): (1) DML lock (data locks): used to protect data integrity; (2) DDL lock (dictionary locks, dictionary lock): used to protect the structure of database objects (such as the schema definitions of tables, views, and indexes); (3) Internal locks and latches (Internal locks and latches ): protects the internal database structure; (4) Distributed locks (Distributed Lock): Used in OPS (Parallel Server); (5) PCM locks (parallel high-speed cache management lock ): used in OPS (Parallel Server. The most important lock in Oracle is the DML (also known as data locks) Lock. The purpose of the DML lock is to ensure data integrity in the case of concurrency. In Oracle databases, DML locks mainly include tmlocks and TX locks. tmlocks are called table-level locks, and TX locks are called transaction locks or row-level locks. The lock is used in data sharing to ensure data consistency. When multiple sessions modify a table at the same time, the data needs to be locked accordingly. Locks include read-only locks, exclusive locks, and shared exclusive locks. Each type also has row-level locks (locking a record at a time ), "Page-level locks" (one page is locked at a time, that is, the minimum allocable unit for storing records in the database) and "table-level locks" (locking the entire table ). If it is a row-level exclusive lock, other rows in the table can be updated or deleted by other users ). If it is a table-level exclusive lock, all other users can only perform the select Operation on the table, but cannot modify or delete any records. After the program commits or rolls back the modifications, the locked resources are released, allowing other users to perform operations. If two transactions lock a part of the data separately and wait for the other party to release the lock to complete the transaction operation, a deadlock will occur. 4. Transactions and locks when performing transaction operations (dml statements), oracle locks the affected table to prevent other users from changing the table structure. Commit transaction: when the execution uses the commit statement, the transaction can be committed. after the commit statement is executed, the transaction changes, ends the transaction, deletes the storage point, and releases the lock. After the transaction ends with the commit statement, other sessions can view the new data after the transaction changes; savepoint save point name; -- create a save point rollback to save point name; -- rollback to the storage point rollback transaction when performing rollback, you can roll back to the specified point by specifying the storage point -- cannot roll back after canceling all transaction commit