• Define locks and transactions.
• Describe the various lock modes supported by the Oracle database.
• Identify the causes of lock conflicts and understand how to resolve deadlocks.
Focused Explanation
Introducing Locks and Transactions
Locks prevent a second user from changing the same data that another user is already in the process of changing. Locks also help in maintaining read consistency. A lock provides the user with exclusive control over the data until the user commits or rolls back the transaction that is changing the data. The user executing the DML statement must lock the rows before changing the rows in a table.
In Oracle 10g, a transaction can lock a single row, multiple rows, or an entire table. You can lock the rows both manually and automatically. The rows can be locked at the lowest possible access level to ensure data integrity. You can also configure locks to minimize the conflict with other transactions that may need to access other rows in the table.
For example, two transactions might use two different sessions to update rows in a table, EMPLOYEE. The EMPLOYEE table has three columns, EMPLOYEE_ID, MANAGER_ID, and SALARY. While the first
transaction is updating the SALARY column of the EMPLOYEE table, the second transaction cannot access the SALARY column in the EMPLOYEE table. However, the second transaction can update other rows or columns of the table which are not locked by the first transaction.
Table 9-1 shows concurrent transactions on different rows of the EMPLOYEE table.
Session 1 Time Session 2
Table 9-1: Concurrent Transactions on Different Rows of the EMPLOYEE Table
In Table 9-1, both the updates are successful because the locks are on different rows in the EMPLOYEE table and neither session is waiting for the other lock to be released.
The first transaction is updating the row where the employee ID is 76, and the second transaction is updating the row where the employee ID is 204.
A lock does not affect the queries executed during a transaction. If a transaction locks some rows of a table, a query uses the pre-lock image values of the data stored in the undo tablespace.
When multiple users request locks on the same rows in a table, the first user to request the lock acquires the lock. The remaining users are handled using the first-in, first-out (FIFO) method. You can override these automatic locks using the LOCK statement. Using the LOCK statement with the NOWAIT keyword will attempt to immediately lock the specified table. However, if a lock already exists on the specified table, Oracle immediately returns a message indicating that the table is already locked by another user. The following example shows the use of the NOWAIT keyword in the LOCK TABLE statement:
SQL> LOCK TABLE EMPLOYEE
2 IN SHARE ROW EXCLUSIVE MODE 3 NOWAIT
lock table employee *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
If you include the WAIT keyword with the LOCK statement, Oracle will wait for the table to become available and then lock the specified table and return control to the user.
When either a COMMIT or ROLLBACK statement is issued or a transaction is ended implicitly, all locks held by the transaction are released. The COMMIT or ROLLBACK statement is issued either explicitly or
implicitly by the user, when the session terminates normally or abnormally.
Identifying Lock Modes
You can use locks on different entities of a database, such as tables and views. Lock modes are used to define the access type and access level on the tables that are being used in the DML statements. For example, when a user configures a shared lock on a database object, the database object is locked for editing. Other users can access the table but no changes can be made to the table. Oracle 10g provides various lock modes on the database objects such as SHARE and EXCLUSIVE. The following are the types of lock modes used in a table:
• ROW SHARE – Permits access to the locked table but prevents other users from locking the entire table for exclusive access.
• ROW EXCLUSIVE – Permits access to the locked table but also prevents locking in the ROW SHARE mode. This lock is automatically obtained using DML statements, such as UPDATE, INSERT, or DELETE.
• SHARE – Permits simultaneous queries on a table but prevents updates to the table. This mode is required to create an index on a table and is automatically obtained when the CREATE INDEX statement is used.
• SHARE ROW EXCLUSIVE – Permits other users to query a table but prevents them from locking the table in the ROW SHARE mode or updating the rows.
• EXCLUSIVE – Permits queries on the locked tables but prevents DML statements to be performed on the locked table. This mode is required to drop a table and is automatically obtained when the DROP TABLE statement is used.
You can explicitly lock individual rows in a table using the SELECT...FOR UPDATE statement. The following SQL statement shows the rows in the EMPLOYEE table locked using the SELECT...FOR UPDATE statement:
SQL> SELECT * FROM EMPLOYEE 2 WHERE MANAGER_ID = 200 3 FOR UPDATE;
This SELECT statement retrieves the rows that satisfy the condition specified in the WHERE clause, locks the selected rows, and prevents other transactions from locking or updating these rows until a COMMIT or ROLLBACK statement is issued.
Identifying and Resolving Lock Conflicts
Sometimes you cannot avoid the occurrence of locks in a database, and these locks are placed in a queue and resolved automatically. However, in some situations, you need to manually resolve lock conflicts. For example, a user might issue a DML statement on a table at 5:20 P.M. and forget to issue a COMMIT statement before leaving for the day. This DML statement would continue to hold locks on the table. Other lock conflicts can be caused due to long-running transactions. For example, if a transaction issues thousands of DML statements overnight to update tables in a data warehouse, the transactions might not complete before the beginning of the next business day. The uncommitted transactions would hold locks on these tables and users would not be able to update the tables until the lock conflicts were resolved. A user can resolve a lock conflict by using the COMMIT or ROLLBACK statement on the current transaction.
You can identify locks in Oracle 10g using Enterprise Manager Database Control. In the Database Locks page of Enterprise Manager Database Control, you can view the locks on the database tables.
Managing Deadlocks
A deadlock is a special type of lock conflict in which users wait for resources locked by each other. As a result, the transactions will not be complete unless you resolve the deadlock by first detecting the deadlock and then rolling back one of the statements waiting on the resource.
Table 9-2 shows two sessions that attempt to update a row locked by another session.
UPDATE EMPLOYEE SET MANAGER_ID = 135 WHERE EMPLOYEE_ID = 204;
UPDATE EMPLOYEE SET MANAGER_ID = 135 WHERE EMPLOYEE_ID = 76;
CONTROL RETURNS TO USER
Table 9-2: Updating a Row Locked by a Session
After issuing the error message at 11:45 P.M., the second update for Session 1 does not succeed because the first UPDATE statement in Session 2 has a lock on the row where the employee ID is 204.
The second update statement for Session 2 will be completed because the lock on the row, where the employee ID is 76, has been released by the user of Session 1 after the completion of the first update statement of Session 1. The user of Session 2 can perform another DML statement or issue a COMMIT or ROLLBACK statement, but the user of Session 1 needs to re-issue the second UPDATE statement to perform the required task.