Example: Multiple User Locking

This example illustrates locking when multiple users initiate concurrent transactions against the same tables. In this case, users must wait for appropriate locks.

The first user (User1) initiates a transaction to update the salary of each employee in the Techsup department to 30000. Another user (User2) issues a query to read the salary and floor of the employee named Dan. Both users end their transactions with a commit statement.

Both transactions affect the tables:

Emp, which is keyed on name with a secondary index on deptno
Dept, which is keyed on dname

Because of the way these tables are indexed, only a few pages in the tables need to be accessed, so page-level locking is used.

The following tables show the first four pages of the EMP and DEPT tables.

EMP Table

The EMP table is an ISAM structure keyed on the name column, and with a secondary index on the deptno column:

Page	Name	Salary	Deptno
1	Andy Candy Dan	55000 50000 25000	9 6 7
2	Ed Fred Jeff	20000 20000 35000	2 8 4
3	Kevin Lenny Marty	40000 30000 25000	3 6 8
4	Penny Susan Tami	50000 20000 15000	9 1 6

DEPT Table

The DEPT table is an ISAM structure keyed on the dname column:

Page	Deptno	Dname	Floor
1	1 2 3	Accting Admin Develop	5 4 4
2	4 5 6	Mgr Prod Sales	3 2 3
3	7 8	Shipping Techsup	2 1
4	9 10	VP WP	5 5

Locks Granted

The following shows the locks that are requested on behalf of both users:

Table	Page	User 1 Locks	User 2 Locks
Emp	Entire table 1 2 3 4	IX X X	IS S
Dept	Entire table 1 2 3 4	IS S	IS S

Here is the sequence of operations:

User1 issues the following statements:
update emp set salary = 30000 where deptno in
(select deptno from dept
where dname = 'Techsup');
commit;
User2 issues the following statements:
select e.salary, d.floor from emp e, dept d
where d.deptno = e.dept
and e.name = 'Dan';
commit;
On behalf of User1, the following locks are requested:
- An IS lock on the DEPT table
- An S lock on the third page of the DEPT table where the record for the Techsup department is located
  The subselect statement starts executing, which retrieves the Techsup record.
On behalf of User2, the following locks are requested:
- An IS lock on the EMP table
- An S lock on the first page of the EMP table where the record for the employee named Dan is located
- An IS lock on the DEPT table
- An S lock on the third page of the DEPT table where the Shipping record is located
  The select statement starts executing, which retrieves the salary for employee Dan from the EMP table and the floor on which he works from the DEPT table, using the deptno value 7.
On behalf of User1, the following locks are requested:
- An IX lock on the EMP table
- An X on the second and third page of the EMP table where the updates is made
  The update statement starts executing, setting the value of the salary column for all employees in the Techsup department to 30000.
On behalf of User2, the commit statement is executed; releasing all locks held in User2's behalf.
On behalf of User1, the commit statement is executed; committing all updates and releasing all locks held in User1's behalf.

Waiting for Locks

If an IX lock is taken on a table on behalf of User1, User2 must wait to retrieve all the values from the table until User1 completes his transaction and releases all locks. This occurs because one user is updating at least one page in a table and the default is that no other user can read the entire table.

For example, assume that User2 in the previous example had issued the following statements instead:

select * from emp;
commit;

In this simple case, the waiting time is negligible, but had User1 issued a complicated update on a large number of rows in the EMP table, User2 must wait a long time.