When running the Database Services Utility with multiple user mode to load (INSERT) or unload (SELECT) rows from a DB2 Server for VSE & VM database, you may encounter lock escalation, particularly when using isolation level CS or RR. Lock escalation reduces the ability to access the database and increases the likelihood of deadlock conditions, which terminate processing. SQL LOCK DBSPACE or LOCK TABLE statements override the database manager automatic locking mechanism; they can be used to reduce deadlock conditions during Database Services Utility processing. Using isolation level UR to unload rows from a DB2 Server for VSE & VM database may also reduce lock escalation and deadlock conditions; however, it is not recommended because it can cause data integrity problems.
A user-issued SQL LOCK statement is useful only during multiple user mode
processing for table data in a public dbspace that is not defined with locking
at the dbspace level. A user-acquired database lock remains in effect
until the end of the logical unit of work in which it was issued.
You cannot lock any database manager catalog tables--regardless of the
database authority you have. To lock an eligible dbspace or table, you
(the user connected to the database) must meet the requirements in Table 14:
Table 14. Requirements to Lock a Dbspace or Table
| To Lock: | You Must |
|---|---|
| DBSPACE |
|
| Table |
|
If you insert many rows into the database with a RELOAD command or a DATALOAD command without the COMMITCOUNT option specified, consider using the SQL LOCK DBSPACE statement to eliminate or reduce lock escalation. An exclusive lock on the dbspace where the tables being loaded are defined does not appreciably increase lock contention and reduces the likelihood of deadlock with another user.
| Note: | An exclusive lock on a table being loaded does not prevent lock escalation and is not recommended. |
To exclusively lock a dbspace, issue the following command before the
DATALOAD or RELOAD command:
Format:
>>-LOCK DBSPACE--dbspace_name--IN EXCLUSIVE MODE;-------------->< |
You can also avoid lock escalation during multiple user mode DATALOAD processing by issuing a SET AUTOCOMMIT ON command before the DATALOAD command and specifying a sufficiently low COMMITCOUNT value in the DATALOAD INFILE subcommand. Use of DATALOAD COMMITCOUNT processing reduces the likelihood of the locking required by DATALOAD processing delaying other users accessing the table being loaded or other tables in the same dbspace where the table being defined resides. If the target table is in a dbspace defined with ROW level locking, a COMMITCOUNT value of approximately 200 should be sufficiently low. If the dbspace is defined with PAGE locking, the COMMITCOUNT value can be higher (1000, for example) and lock escalation is still avoided. Do not arbitrarily set the COMMITCOUNT value too low because frequent commit points increase DATALOAD run time.
If you are running the Database Services Utility with the isolation level setting of repeatable read (the default Database Services Utility processing mode) and you know that a particular SELECT, DATAUNLOAD, or UNLOAD operation is going to access many rows from one or more tables in the database, lock escalation then normally occurs. You should consider acquiring a SHARE lock on the table(s) being accessed. If all the tables being accessed reside in the same dbspace, you should consider acquiring a SHARE lock on the dbspace being accessed. This action can reduce lock contention and the likelihood that a SELECT, DATAUNLOAD, or UNLOAD causes a deadlock with another user. Other users can modify other tables in the same dbspace where the table being accessed resides.
To acquire a SHARE lock on a table or dbspace being accessed, issue the
following command before the SELECT, DATAUNLOAD, or UNLOAD statement:
Format:
>>-LOCK TABLE--table_name--IN SHARE MODE;----------------------><or >>-LOCK DBSPACE--dbspace_name--IN SHARE MODE;------------------>< |