Databases Reference
In-Depth Information
Detecting and preventing lock contention
If databases were used by a single user there would be no need for locks, because other
users are not accessing the same data at the same time. In this recipe we will see how two
concurrent sessions experience wait time due to locks, and how to diagnose them and what
to do to resolve and avoid these situations.
Getting ready
In this recipe we use three concurrent SQL*Plus sessions to simulate two concurrent users in
the first two sessions, while querying dynamic performance views in a third session. We will
use the
TESTDB
database in the rest of this topic.
How to do it...
The following steps will show how to detect and prevent lock contention:
1.
Connect
SESSION1
as user
SH
:
-- SESSION 1
CONNECT sh@TESTDB/sh
2.
Update a row in
SESSION1
, not completing the transaction with a
COMMIT
or
ROLLBACK
statement:
UPDATE CUSTOMERS SET
CUST_FIRST_NAME = 'TEST1'
WHERE CUST_ID = 26;
3.
Connect
SESSION2
as user
SH
:
-- SESSION 2
CONNECT sh@TESTDB/sh
4.
Update the same row
SESSION2
as in
SESSION1
:
UPDATE CUSTOMERS SET
CUST_FIRST_NAME = 'TEST2'
WHERE CUST_ID = 26;
5.
Connect
SESSION3
as
SYSDBA
:
-- SESSION 3
CONNECT / AS SYSDBA
