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
   

2. Update a row on the CUSTOMERS table in SESSION 1:

   UPDATE CUSTOMERS SET
     CUST_FIRST_NAME = 'TEST1-1'
     WHERE CUST_ID = 26;
   

3. Connect SESSION 2 to the database as SH user:

   -- SESSION 2
   CONNECT sh@TESTDB/sh
   

4. Update a row on the CUSTOMERS table in SESSION 2, different from the one updated in step 2 by SESSION 1:

   UPDATE CUSTOMERS SET
     CUST_FIRST_NAME = 'TEST2-1'
     WHERE CUST_ID = 30;
   

5. Try to update, in SESSION 1, the same row updated in step 4 by SESSION 2:

   -- SESSION 1
   UPDATE CUSTOMERS SET
     CUST_FIRST_NAME = 'TEST1-2'
     WHERE CUST_ID = 30;
   

6. Try to update, in SESSION 2, the same row updated...