Databases Reference
In-Depth Information
13. Acquire the
SERIAL#
and
SID
of the locked session (
SESSION2
):
SELECT SERIAL# FROM V$SESSION WHERE SID = 17;
14. Kill the locked session:
ALTER SYSTEM KILL SESSION '17, 15';
15. Complete the transaction in
SESSION1
to avoid other locking:
-- SESSION 1
ROLLBACK;
How it works...
We use two sessions of SQL*Plus connected as user
SH
to simulate the concurrent use of
the same table/row by different users. In step 1 we connect the first session.
In step 2 the user updates one record in the
CUSTOMERS
table, not committing the
transaction that is left open. In this situation a lock is held by the user in session 1 on the
updated row until the transaction ends.
In step 3 we connect the second session as user
SH
and in step 4 we try to update the same
record in step 2. By doing so, our session gets locked, waiting for the transaction in session 1
to be committed or rolled-back. We can see this situation in the following screenshot, where
the terminal on the left is for session 1 and the terminal on the right is for session 2:
In the previous screenshot we can see that session 2 on the right is waiting.
In step 5 we connect another session to the database as
SYSDBA
, and in step 6 we execute
a query to see the locks in the database, obtaining the following output:
