Databases Reference
In-Depth Information
There's more...
In this recipe we have seen how to detect a lock in the system and retrieve more information
about the locks.
There are many possible causes of contention. We have seen what happens when we have
uncommitted changes like in our example. The same behavior can happen when there are
long-running transactions, such as long batch jobs, or unnecessary high level locking.
The Oracle database automatically ensures the appropriate level of locking for DML and DDL
operations, ensuring maximum availability and scalability. However, there are many products
on the market that were not specifically designed for the Oracle database. They can require
unnecessary high locking level; sometimes this behavior is also found in frameworks or APIs
used in a multilayer application, which the developer is unaware of, causing locking problems.
As a rule of thumb, in developing applications for Oracle databases, always write code that
acquires locks in the same order and don't use excessive locking for operations such as
UPDATE
and
SELECT
FOR
UPDATE
. Long-running batch jobs in OLTP environments should
be scheduled in off-peak hours to avoid issues.
See also
See
Investigating
transactions
and
concurrency
in this chapter.
Investigating transactions and concurrency
In this recipe we will see more details on locking and, specifically, on deadlocks.
Getting ready
In this recipe we will use two SQL*Plus sessions, to simulate two users concurrently
accessing the database.
How to do it...
This recipe deals wits transactions and concurrency. Follow these steps:
1.
Connect
SESSION
1
to the database as
SH
user:
-- SESSION 1
CONNECT sh@TESTDB/sh
