Guidelines for lockingAdded by IBM on September 9, 2010 | Version 1 (Original)
|This topic presents the guidelines you should consider when tuning locking for concurrency and data integrity.
This topic presents the guidelines you should consider when tuning locking for concurrency and data integrity.
- DB2® Everyplace® locks entire tables.
You either lock an entire table or you don't lock it at all. You cannot lock specific rows in a table.
- Create small units of work with frequent COMMIT statements to promote concurrent access of data by many users.
Include COMMIT statements when the data you have changed is consistent. When a COMMIT is issued, all locks are released except those related to open cursors (in DB2 Everyplace, cursors are held across a COMMIT). After a COMMIT, all remaining locks are SHARED locks. All locks are released on ROLLBACK.
- Specify an appropriate isolation level.
Shared locks are acquired by serializable, repeatable-read and read-committed isolation levels, even in read-only applications. To release these locks, close cursors that are not in use.
The database manager ensures that your application does not retrieve uncommitted data (rows that have been updated by other applications but are not yet committed) unless you are using the uncommitted read isolation level.
- Use the LOCK TABLE statement appropriately.
Only the table specified in the LOCK TABLE statement is locked. Parent and dependent tables of the specified table are not locked. You must determine whether locking other tables is necessary to achieve the desired result in terms of concurrency and performance. The lock is not released until the unit of work is committed or rolled back.
LOCK TABLE IN SHARE MODE
You want to access data that is consistent in time; that is, data current for a table at a specific point in time. If the table experiences frequent activity, the only way to ensure that the entire table remains stable is to lock it. For example, your application wants to take a snapshot of a table. However, during the time your application needs to process some rows of a table, other applications are updating rows you have not yet processed. This is allowed with repeatable read, but this action is not what you want.LOCK TABLE IN EXCLUSIVE MODE
As an alternative, your application can issue the LOCK TABLE IN SHARE MODE statement: no rows can be changed, regardless of whether you have retrieved them or not. You can then retrieve as many rows as you need, knowing that the rows you have retrieved have not been changed just before you retrieved them.
With LOCK TABLE IN SHARE MODE, other users can retrieve data from the table, but they cannot update, delete, or insert rows into the table.
With LOCK TABLE IN EXCLUSIVE MODE, all other users are locked out; no other applications can access the table unless they are uncommitted read applications.
- Close cursors to release the locks that they hold.
In DB2 Everyplace, cursors are held across commits by default, and are closed implicitly when executing the next statement. If an application no longer needs the cursor at commit time, it should close the cursor explicitly before committing the transaction to release its shared locks. Also, the isolation level of a connection can only be set if there are no open cursors and auto commit is on; otherwise SQLSTATE HY011 is returned.
Isolation levels: XPD622
An isolation level
specifies how much one transaction is isolated from other transactions in a multiple-connection environment. DB2 Everyplace supports the following ANSI SQL isolation levels.
Connection serialization: XPD622
A DB2 Everyplace data source accepts connections from one process at a time. When more than one process tries to connect to the same data source at the same time, the requests are put into a queue through a mechanism called connection serialization
Parent topic: Tuning database applications: XPD622