Monitoring Lock Waits and Lock Errors

If the application executes SET LOCK MODE TO WAIT, the database server waits for a lock to be released instead of returning an error. An unusually long wait for a lock can make users think that the application is hanging.

In Figure 20, the onstat -u output shows that session ID 84 is waiting for a lock (L in the first column of the Flags field). The address field shows the address of the lock for which the user is waiting. To find out the owner of the lock, use the onstat -k command.You can cross-reference the owner of the lock back to the onstat -u output. In the example, session ID 81 is the owner of the lock.

To eliminate the contention problem, you can kill the session that owns the lock, as described in Stopping Queries. If this action is not possible, you can kill the application process or remove the session with onmode -z.

Figure 20. onstat -u Output That Shows Lock Use