Data Constraints are essential in protecting the integrity of the data in any relational database. The Oracle Database provides four types of declarative constraints that help implement various types of data rules. These are: Primary Key, Unique Key, Foreign Key and Check Constraint. Although these constraints can be configured to be enforced at transaction time (when the transaction is committed) by setting them to be deferred, the default behavior of the constraints is to enforce the integrity rule at statement level. That means that when a transaction performs multiple DML statements, the constraints are validated during execution of the statement. When the statement is done, the integrity is ensured (otherwise the statement would have failed) and additional statements can be executed.
In the multi session environment that is the Oracle Database, you could be wondering – as was I – how the Oracle Database ensures that other sessions executing DML operations can not undermine the integrity of the data touched by the current transaction. And how it can do so without needlessly preventing other sessions to perform data manipulation using various forms of locking.
It turns out that the database use a smart, fine grained way of locking to prevent sessions from corrupting the integrity established by statement level validation. I will explain what it does.
Let’s take for example table EMP. You know, the one with the 14 employees that have ENAMEs, EMPNOs and other columns. By default, no constraints are defined on table EMP. Let’s add a unique constraint on ENAME:
Let’s now assume we have two concurrent database sessions, X and Y.
In session X, the EMP record for SCOTT is updated. SCOTTs ENAME is set to SCOTTY. This statement succeeds, because SCOTTY does not already exist in the ENAME column. Session X does not commit the transaction [yet]. In the concurrent session Y, the name change for SCOTT cannot yet be seen as it has not yet been committed. In this session Y, another record is updated: MILLER is updated to become SCOTT.
At this point, the question is:
- is this update statement accepted by the database (because by now SCOTT is already SCOTTY because of the update in session X)
- will it fail because in the context of Session Y there already is a SCOTT (because the transaction in Session X is not committed and the update of SCOTT therefore is not visible in Session Y)
- will table EMP be locked for any updates in session Y because session X is already updating it?
- will something else happen?
Try for a moment to answer this question yourself. What do you think will happen?
Before I give you the correct answer, I will introduce a second situation: In session X, a new employee is inserted. The statement succeeds as the constraint validation finds no fault with this new record. The transaction in session X is not yet committed. In session Y, the SMITH record is updated, to WILSON, a value that does not currently exist in table EMP.
The question again is: will the update statement in Session Y succeed? Fail? Be blocked?
In this case: the update in Session Y will succeed as it is completely unrelated to the insert in session X. Different records are involved, different – non conflicting values for ENAME are used. There is no problem.
The next figure shows how this second example continues. Session Y goes on to insert a new Employee record with ENAME WILMA. This statement too succeeds. Then session Y attempts to rename Employee KING; the ENAME value for KING is updated to FRED – which is also the ENAME inserted for the new Employee from Session X.
Session X did not yet commit. The new FRED record is not yet visible in session Y. On the other hand, the insert succeeded. If the database would also accept this update in session Y too because no FRED currently exists in table EMP, then after both transactions have been committed we would end up with two FREDs – in violation of the Unique Key constraint.
So what do you think will happen in session Y with the update statement:
- is this update statement accepted by the database (because the new FRED from session X is invisible in session Y so there is no reason to conclude a uniqueness violation)
- will it fail because clearly we cannot end up with two FRED records and since the insert in session X is already accepted there is no alternative but to reject this update
- will table EMP be locked for any updates in session Y because session X is already manipulating it? (well clearly this cannot be the case because in session Y the update of SMITH was accepted without any problem
- will something else happen?
Again, try for a moment to answer this question.
As it turns out, the update statement in session Y will be blocked. It runs into a lock held by session X. This lock is not held on a record, nor is it held on the table. It is a lock on the Unique Key constraint on ENAME in combination with the value FRED. It is a fine grained lock that does not prevent other sessions from performing insert and update statements that set ENAME to other values than FRED. But using the value FRED is prevented by this lock. A lock that session X will hang onto until the transaction either commits or rollback.
Once the lock is released after the commit in Session X, the update statement in session Y can continue. And it will fail because the validation of the unique key constraint runs into the FRED employee record that is now committed.
The former case is now simple to understand: the update of MILLER in session Y runs into a similar lock as in the second example with KING’s update to FRED. The lock is held by session X. When session X commits, the lock is released, session Y’s update can continue and the row update will be succesful because at that time, SCOTT has formally been renamed to SCOTTY.
