ORACLE Database is the most widely used database in today's field. It is also a huge system. It requires not only some theoretical knowledge, but also development and engineering experience to fully understand it. I am an ORACLE enthusiast. I hope to share my experience on ORACLE locks with you.
Prerequisites:
DDL (database definition language): database definition language, such as create table and drop table ..... DML (database modification language): database modification language, such as insert, delete, update ...... reference: Oracle8 Administrator "s Guide, Release 8.0 Oracle8 Tuning, Release 8.0
ORACLE locks are divided into the following types:
1. automatic locks and display locks can be divided by users and systems.
Automatic lock: When a database operation is performed, the system automatically obtains all necessary locks for this database operation by default.
Display lock: in some cases, you need to display the data required to lock database operations to make database operations better. The display lock is set by the user for database objects.
2. Locks can be divided into shared locks and exclusive locks.
Shared lock: A Shared lock allows a transaction to share access to a specific database resource. Another transaction can also access this resource or obtain the same shared lock. Shared locks provide high concurrency for transactions, but poor transaction design + Shared locks may cause deadlocks or data update loss.
Exclusive lock: After an exclusive lock is set for a transaction, the transaction obtains the resource separately. Another transaction cannot obtain the share lock or exclusive lock of the same object before the transaction is committed.
3. Divided by operation, it can be divided into DML locks and DDL locks
+ DML locks can be divided into row locks, table locks, and deadlocks.
-Row lock: when a transaction performs database insert, update, or delete operations, the transaction automatically obtains the row exclusive lock of the operation table.
-Table-Level Lock: after a transaction acquires a row lock, the transaction automatically acquires the table lock (shared lock) of the row to prevent other transactions from affecting the update of record rows by using DDL statements. A transaction can also obtain a shared or exclusive LOCK during the process. Only when the transaction displays the definition of an exclusive LOCK displayed by the lock table statement will the transaction obtain the exclusive LOCK on the TABLE, you can also use the lock table display to define a TABLE-level shared LOCK (refer to the relevant documentation for specific usage of the lock table ).
-Deadlock: a deadlock occurs when two transactions need a set of conflicting locks and cannot continue the transaction.
For example, if transaction 1 records row #3 in Table A and waits for transaction 2 to record in Table A #4 to release the exclusive lock, transaction 2 has an exclusive lock in Table A record row #4, and waits for transaction 1 to record in Table A #3 to release the exclusive lock. Transaction 1 and transaction 2 wait for each other, therefore, a deadlock occurs. Deadlock is generally caused by poor transaction design.
Only SQL statements can be used for deadlocks: alter system kill session "sid, serial #"; or commands for kill processes in the relevant operating system, such as kill-9 sid in UNIX, or use other tools to kill the deadlock process.
+ DDL locks can also be divided into DDL locks, shared DDL locks, and analysis locks.
-Exclusive DDL lock: create, modify, and delete DDL statements of a database object to obtain the exclusive lock of the operation object. For example, when an alter table statement is used, the transaction obtains a row of DDL locks to maintain data integrity, consistency, and legitimacy.
-Share DDL lock: DDL statements that need to establish dependency between database objects usually need to share to obtain the DDL lock.
If you create a package, the process in the package is different from that in the function reference database table. When this package is compiled, the transaction obtains the shared DDL lock of the referenced table.
-Analysis lock: ORACLE uses a shared pool to store and analyze SQL statements and PL/SQL programs that have been optimized, making applications that run the same statement faster. An object cached in the Shared Pool obtains the analysis lock of the database object it references. Analytics lock is a unique DDL lock type. ORACLE uses it to track Shared Pool objects and dependencies between referenced database objects. When a transaction modifies or deletes a database object holding an analysis lock in the Shared Pool, ORACLE invalidate the object in the shared pool. The next time you reference this SQL/PLSQL statement, ORACLE re-analyzes and compiles this statement.
4. Internal locks
Internal lock: this is a special lock in ORACLE for sequential access to the internal system structure. When writing information to the buffer zone, in order to use this block of memory area, ORACLE must first obtain the latch of this block of memory area to write information to this block of memory.
