High collision rate on SQL locks: <average number> per write transaction, <number of> write transactions, <number of> SQL collisions

A large percentage of the write transactions are resulting in locks of SQL objects (lines and tables), which in turn are causing waits in the application, until the locking application tasks releases the lock by sending COMMIT. This problem is usually a problem with application design and not with the database. The database system attempts to execute locking tasks in the database kernel with higher priority if these locks are requested by other sessions (which are then in Vwaits) to avoid queues for SQL lock objects.

Determine whether the application is suited for isolation level 0 (dirty read) to avoid read locks ( (SAP R/3 runs on isolation level 0). Also, determine whether the time between the lock being set and the COMMIT can be reduced (avoid locks during dialog sessions).

Log writing may be the cause of another bottleneck, as the SQL locks for the transaction can only be released after the successful, physical log I/O of the COMMIT. Therefore, it makes sense to place the log in the fastest devspaces possible. By monitoring the maximum length of the log queue, you can determine whether bottlenecks occur when the system writes to the log.