ORACLE databases are the most widely used in today's database field. It is also a vast system. To fully understand it and make good use of it, one needs not only certain theoretical knowledge but also development and engineering experience. I am an enthusiast of ORACLE, and below are some of my experiences with ORACLE locks, which I hope to share with everyone.
Preliminary Knowledge:
DDL (DATABASE DEFINITION LANGUAGE): Database definition language, such as create table, drop table...
DML (DATABASE MODIFICATION LANGUAGE): Database modification language, such as insert, delete, update...
Reference Materials: Oracle8 Administrator's Guide, Release 8.0
Oracle8 Tuning, Release 8.0
ORACLE locks can be specifically categorized into the following types:
1. According to the division of the user and Pleasant Goat and Big Big Wolf series, they can be divided into automatic locks and explicit locks.
Automatic Locks: When performing a database operation, by default, the system automatically obtains all necessary locks for this database operation.
Explicit Locks: In some cases, users need to explicitly lock the data required for the database operation to make the database operation perform better. Explicit locks are set by users for database objects.
2. According to the level of the lock, they can be divided into shared locks and exclusive locks.
Shared Locks: A shared lock allows a transaction to have shared access to specific database resources - another transaction can also access this resource or obtain the same shared lock. Shared locks provide high concurrency for transactions, but poor transaction design can easily cause deadlocks or data update loss.
Exclusive Locks: After a transaction sets an exclusive lock, this transaction alone acquires this resource, and another transaction cannot acquire the same object's shared lock or exclusive lock before this transaction commits.
3. According to the operation, they can be divided into DML locks and DDL locks.
DML locks can further be divided into row locks, table locks, and deadlocks.
- Row Locks: When a transaction performs database insert, update, or delete operations, the transaction automatically acquires an exclusive lock on the rows being operated in the table.
- Table-level Locks: After a transaction acquires a row lock, this transaction will also automatically acquire the table lock (shared lock) of that row to prevent other transactions from affecting the update of the record row through DDL statements. Transactions can also acquire shared locks or exclusive locks during their execution. Only when a transaction explicitly uses the LOCK TABLE statement to define an exclusive lock does the transaction acquire an exclusive lock on the table. You can also use the LOCK TABLE statement to explicitly define a table-level shared lock (for detailed usage of LOCK TABLE, please refer to related documents).
- Deadlock: When two transactions require a conflicting set of locks and cannot continue, a deadlock occurs.
For example, Transaction 1 has an exclusive lock on row #3 of table A and is waiting for Transaction 2 to release its exclusive lock on row #4 of table A. Meanwhile, Transaction 2 has an exclusive lock on row #4 of table A and is waiting for Transaction 1 to release its exclusive lock on row #3 of table A. This mutual waiting causes a deadlock. Deadlocks generally occur due to poor transaction design.
Deadlocks can only be resolved using SQL commands like: alter system kill session 'sid,serial#'; or by using relevant operating system commands to kill processes, such as kill -9 sid under UNIX, or by using other tools to terminate the deadlock process.
DDL locks can be further divided into: exclusive DDL locks, shared DDL locks, and parse locks.
- Exclusive DDL Locks: DDL statements that create, modify, or delete a database object obtain an exclusive lock on the object being operated on. For example, when using the ALTER TABLE statement, to maintain the integrity, consistency, and legality of the data, the transaction acquires an exclusive DDL lock.
- Shared DDL Locks: DDL statements that need to establish interdependencies between database objects usually require a shared DDL lock.
For example, creating a package where the procedures and functions within the package reference different database tables, when compiling this package, the transaction acquires a shared DDL lock on the referenced tables.
- Parse Locks: ORACLE uses the shared pool to store parsed and optimized SQL statements and PL/SQL programs, making applications running the same statements faster. A cached object in the shared pool acquires a parse lock on the database objects it references. The parse lock is a unique type of DDL lock that ORACLE uses to track dependencies between shared pool objects and the database objects they reference. When a transaction modifies or deletes a database object that the shared pool holds a parse lock on, ORACLE invalidates the object in the shared pool, and the next time this SQL/PLSQL statement is referenced, ORACLE re-parses and recompiles the statement.
4. Internal Latches
Internal Latches: These are a special type of lock in ORACLE used for sequential access to internal system structures. When a transaction needs to write information to the buffer, to use this memory block, ORACLE must first acquire a latch on this memory block before writing information to it.
Article Address: 【Eden Network】http://www.edenw.com/tech/devdeloper/database/2010-07-15/4713.html