Getting Locking Information

Derby provides a tool to monitor and display locking information. This tool can help you create applications that minimize deadlock. It can also help you locate the cause of deadlock when it does occur.

To diagnose locking problems, perform the following action:

• constantly monitor locking traffic by logging all deadlocks (using the derby.locks.monitor property)

The derby.stream.error.logSeverityLevel property determines the level of error you are informed about. By default, derby.stream.error.logSeverityLevel is set at 40000. If derby.stream.error.logSeverityLevel is set to display transaction-level errors (that is, if it is set to a value less than 40000), deadlock errors are logged to the derby.log file. If it is set to a value of 40000 or higher, deadlock errors are not logged to the derby.log file.

The derby.locks.monitor property ensures that deadlock errors are logged regardless of the value of derby.stream.error.logSeverityLevel. When derby.locks.monitor is set to true, all locks involved in deadlocks are written to derby.log along with a unique number identifying the lock.

To see a thread's stack trace when a lock is requested, set derby.locks.deadlockTrace to true. This property is ignored if derby.locks.monitor is set to false.

Note:

Use derby.locks.deadlockTrace with care. Setting this property can alter the timing of the application, severely affect performance, and produce a very large derby.log file.

For information on how to set properties, see Tuning Derby. For specific information about the properties mentioned in this section, see the properties chapter in that book.

Here is an example of a victim's error message when Derby aborts a transaction because of a deadlock:

--SQLException Caught--
 
SQLState: 40001 =
Error Code: 30000 
Message: A lock could not be obtained due to a deadlock,
cycle of locks and waiters is: Lock : ROW, DEPARTMENT, (1,14)
Waiting XID : {752, X} , APP, update department set location='Boise'
    where deptno='E21'
Granted XID : {758, X} Lock : ROW, EMPLOYEE, (2,8)
Waiting XID : {758, U} , APP, update employee set bonus=150 where salary=23840
Granted XID : {752, X} The selected victim is XID : 752

Note:

You can use the derby.locks.waitTimeout and derby.locks.deadlockTimeout properties to configure how long Derby waits for a lock to be released, or when to begin deadlock checking. For more information on these properties, see the chapter that discusses controlling Derby application behavior, in the Derby Developer's Guide.