Example: Single User Locking

This example illustrates the use of locking when a single user initiates a transaction.

In this example, a user issues a single query transaction (SQT) consisting of:

• An SQL statement to read data on the employee named Jeff from the table named EMP
• A commit

Here is the sequence of operations:

1. The user issues a select statement followed by a commit statement:
2. select * from emp where name = 'Jeff';
   commit;
3. An "IS" lock is used on the EMP table and a page-level "S" lock on the page containing the Jeff row.

   The query is restrictive (only the row specified in the where clause is to be retrieved), and the table itself has an ISAM structure indexed on 'name', so the entire table does not have to be scanned. The locking system can use the index to go directly to the row for Jeff. Thus, an S lock on the entire table is not necessary; an IS table-level lock and an S lock on the page containing the row for Jeff are sufficient.

4. The Jeff row is retrieved.
5. The transaction is terminated and the locks held are released.

After retrieving the row for Jeff, if the user were to issue an update statement and change that record before issuing the commit, and if there were no other shared locks on the page, the locking system converts the shared page-level lock to an exclusive lock, and the IS lock on the table to an IX lock.