Java Programming with Oracle JDBC

Implicit Locking and Visibility

It’s important for you to understand the implications that a transaction has on implicit locking and visibility. Implicit locking refers to how Oracle automatically locks a database row during an insert, update, or delete operation. To help you understand the effects of implicit locking, I’ve created a table, TEST_TRANS, that has two columns: COL1, which is the table’s primary key, and COL2. Figure 17-1 is a timeline for SQL statements against table TEST_TRANS. On the left are SQL statements entered from the same transaction and session. On the right are the effects of those SQL statements on the other session.

Figure 17-1. Implicit locking during manual transactions

To begin, session one performs an insert on table TEST_TRANS:

insert into test_trans ( col1 ) values ( 'X' )

From the moment the INSERT statement is successfully executed, Oracle places an implicit lock on the new row. Since the new row has not been committed, it is not visible to an UPDATE, DELETE, or SELECT statement from session two. However, if attempts are made in session two to insert a row with the same primary key, that session will be blocked indefinitely until the first transaction is either committed or rolled back. When session one commits, the INSERT statement in session two will fail with a duplicate key error. After the commit, session one’s new row is visible to an UPDATE, ...