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.
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.
The following steps will show how to detect and prevent lock contention:
-- SESSION 1 CONNECT sh@TESTDB/sh