Row Locking

Row locks are acquired automatically by the transactions when a row is modified by the following commands: INSERT, DELETE, UPDATE, and SELECT with the FOR UPDATE clause.

The following example of the SELECT command places a row lock on the employee table:

SELECT emp_id, pay_rate
FROM employee
WHERE pay_type = `H'
FOR UPDATE;

These row locks stay in effect until the transaction is completed or rolled back. The row lock is always exclusive, which prohibits other transactions from modifying the same row. When the row lock is issued, a corresponding table lock is also issued to prevent any conflicting DDL statements from taking effect.

Other Locks

Oracle provides a variety of other "minor" locks. I call them minor not because of their importance, but because from a user's perspective, you don't usually interact with them directly.

The dictionary lock is used to protect the database objects from changing during a transaction. This lock is automatically acquired by Oracle when a DDL statement requires it. Like the locks mentioned in the previous section, the dictionary lock can be either exclusive or shared.

Internal locks protect the internal components of the database and memory. These components are inaccessible by end users. For example, locks can be placed on log files, control files, data dictionary cache files, and archive files.

Distributed locks ensure data consistency across multiple instances. Oracle automatically creates these locks as needed.

Monitoring Locks

Oracle provides several ways to monitor which locks are in place within the database. For example, examining the V$LOCK database view will list information about a lock, such as the system ID of the process holding the lock and type of lock held. Additionally, the DBA_DDL_LOCKS, DBA_DML_LOCKS, and DBA_LOCKS tables display similar data about locks.

Likewise, you can use the Oracle tool SQL*DBA. In SQL*DBA, the Lock Monitor screen gives similar information from the V$Lock database view.

Summary

Transactions are logical groups of SQL statements that begin when the statements are executed and end with either a commit or rollback. Transactions provide database users the guarantee of data concurrency and data consistency. This guarantee holds true for distributed and nondistributed databases.

Q&A

Q Why do DML statements need to be committed?
A Committing a transaction accomplishes several things: The transaction is ended, which in turn releases any locks that the transaction might have created.
Q Do I have to roll back an entire transaction if something does not process completely?
A The rollback can be issued to remove all effects of the current transaction. Additionally, you can roll back to an intermediate point in the transaction known as a savepoint.
Q What are the two types of transactions?
A The two main types of transactions are read-only and read-write.

Workshop

Use the following workshop to test your comprehension of this chapter and put what you've learned into practice. You'll find the answers to the quiz and exercise in Appendix A, "Answers."

Quiz

1. How is a transaction ended?
2. What is the difference between row locking and table locking?
3. What is the purpose of a savepoint?

Exercise

Write a PL/SQL block that establishes a savepoint, inserts a single record into the employee table, commits the data if the new record does not replicate an existing record, or rolls back the data if the new record insert fails.