Concurrency

This ability is automatically supplied by the Db2 for i database manager. Locks are implicitly acquired on tables and rows to protect concurrent users from changing the same data at precisely the same time.

Typically, Db2 for i acquires locks on rows to ensure integrity. However, some situations require Db2 for i to acquire a more exclusive table-level lock instead of row locks.

For example, an update (exclusive) lock on a row currently held by one cursor can be acquired by another cursor in the same program (or in a DELETE or UPDATE statement not associated with the cursor). This will prevent a positioned UPDATE or positioned DELETE statement that references the first cursor until another FETCH is performed. A read (shared no-update) lock on a row currently held by one cursor will not prevent another cursor in the same program (or DELETE or UPDATE statement) from acquiring a lock on the same row.

Default and user-specifiable lock-wait timeout values are supported. Db2 for i creates tables, views, and indexes with the default record wait time (60 seconds) and the default file wait time (*IMMED). This lock wait time is used for data manipulation language (DML) statements. You can change these values by using the CL commands Change Physical File (CHGPF), Change Logical File (CHGLF), and Override Database File (OVRDBF).

The lock wait time used for all data definition language (DDL) statements and the LOCK TABLE statement is the job default wait time (DFTWAIT). You can change this value by using the CL command Change Job (CHGJOB) or Change Class (CHGCLS).

If a large record wait time is specified, deadlock detection is provided. For example, assume that one job has an exclusive lock on row 1 and another job has an exclusive lock on row 2. If the first job attempts to lock row 2, it waits because the second job is holding the lock. If the second job then attempts to lock row 1, Db2 for i detects that the two jobs are in a deadlock and an error is returned to the second job.

You can explicitly prevent other users from using a table at the same time by using the SQL LOCK TABLE statement. Using COMMIT(*RR) will also prevent other users from using a table during a unit of work.

To improve performance, Db2 for i frequently leaves the open data path (ODP) open. This performance feature also leaves a lock on tables referenced by the ODP, but does not leave any locks on rows. A lock left on a table might prevent another job from performing an operation on that table. In most cases, however, Db2 for i can detect that other jobs are holding locks and events can be signaled to those jobs. The event causes Db2 for i to close any ODPs (and release the table locks) that are associated with that table and are currently only open for performance reasons.

Unless the LOCK TABLE statement is used to acquire table locks, or either COMMIT(*ALL) or COMMIT(*RR) is used, data which has been read by one job can be immediately changed by another job. Typically, the data that is read at the time the SQL statement is executed and therefore it is very current (for example, during FETCH). In the following cases, however, data is read before the execution of the SQL statement and therefore the data may not be current (for example, during OPEN).

• ALWCPYDTA(*OPTIMIZE) was specified and the optimizer determined that making a copy of the data performs better than not making a copy.
• Some queries require the database manager to create a temporary result table. The data in the temporary result table does not reflect changes that are made after the cursor was opened. For information about when a temporary result table is created, see DECLARE CURSOR in the SQL reference topic collection.
• A basic subquery is evaluated when the query is opened.

The concurrent access resolution option can be used to minimize transaction wait time. This option directs the database manager on how to handle record lock conflicts under certain isolation levels.

The concurrent access resolution values of USE CURRENTLY COMMITTED and SKIP LOCKED DATA can be used to improve concurrency by avoiding lock waits. However, care must be used when using these options because they might affect application functionality. For details on how to specify the concurrent access resolution option, see Improve concurrency by avoiding lock waits

USE CURRENTLY COMMITTED details and considerations

To find the previously committed version of a row, the database manager searches the journal for the most recently committed row image based upon relative record number. The journal search could fail to find the row or the search could timeout. The Maximum record wait time (WAITRCD) influences how long the database manager will search before failing. The WAITRCD for a file can be changed using the Change Physical File (CHGPF) command.