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 book.

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...