SQL | Understanding table locks and setting an exclusive lock
A. A Mini DBA Lesson
If you hang around Database Administrators (DBAs) for any length of time, you’ll inevitably hear them speak of such things as ‘table locks’, ‘transactions’, ‘commits’, ‘rollbacks’ and ‘deadlocks’. Although most developers are not DBAs, it’s worth briefly looking at what some of these terms mean. When the DBA chases you up for doing something wrong, you’ll at least have a basic understanding of what they’re talking about.
Normally as a PeopleSoft developer, you do not need to worry about table locks. The system performs all the necessary locking tasks for you, based on the SQL statements being executed. However, in some cases you may wish to control the locking as part of your process. For instance, you may have a process that needs exclusive access to one of the key tables for the entire duration of the process. By setting the exclusive lock upfront, you can ensure no other users will sneak in and perform updates while the process is running.
Any time a user interacts with the database via SQL (this could be an end user or a developer), they will place a lock on the underlying tables included in the SQL statements. A ‘shared’ lock is typically used for ‘select’ statements. While the user performs the SQL select, other users can continue to read and write from the same table. An ‘exclusive’ lock grants update access to one user only. In this case, other users can still read from the table, but they cannot perform an update until the first user has released the exclusive lock.
As a user carries out a series of SQL statements to the database (perhaps as part of an App Engine process), the Oracle database creates a single ‘transaction’ for all the SQL operations being performed. Only once the process has completed successfully is all the data saved to the underlying database (via a ‘commit’). If for whatever reason the transaction has to terminate early, all the work done up to the point of failure will be cancelled and the database returned to its original state (via a ‘rollback’). A transaction will remain open until a commit or rollback is performed.
Finally, you may have the situation of two users attempting to update the same table at the same time, but each cannot get an exclusive lock because the other user already has a shared lock. In this case, a ‘deadlock’ occurs as both users sit and wait ‘forever’ for the other lock to be released. DBAs and database management software will keep an eye out for deadlocks, usually resolving the issue by releasing one of the locks.
B. Finding Current Table Locks
The following SQL script can show you all current locks in the system. Note that it requires high-level, system-owner access in order to run. The tables included in the query are generally only available to database owners. If you lack the access, you either ask the DBA to grant you access to the tables, or you can get the DBA to run the SQL on your behalf.
select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine, b.program, b.type, b.client_info, b.logon_time from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;
C. Setting an Exclusive Table Lock
To set an exclusive lock on a table use the following SQL command:
lock TABLE ps_table_name IN exclusive mode;
By default, the system will halt the code until the exclusive lock can be obtained. If you’d prefer the system not to wait until this happens, use the ‘NOWAIT’ parameter:
lock TABLE ps_table_name IN exclusive mode nowait;
The ‘NOWAIT’ parameter will cause an error message to be generated, rather than waiting indefinitely for the other lock to be released.
The lock is automatically released upon a ‘Commit’ or ‘Rollback’ statement.