Locking affects the Adaptive Server performance by limiting concurrency. An increase in the number of simultaneous users may increase lock contention, which decreases performance. Locks affect performance when:
Processes wait for locks to be released. Any time a process waits for another process to complete its transaction and release its locks, overall response time and throughput are affected.
Transactions result in frequent deadlocks. A deadlock causes one transaction to be aborted, and the transaction must be restarted by the application. If deadlocks occur often, the throughput of applications is severely affected.
To help reduce deadlock frequency, change the locking scheme to datapages or datarows locking, or redesign the way transactions access data.
Creating indexes locks tables. Creating a clustered index locks all users out of the table until the index is created; creating a nonclustered index locks out all updates until it is created.
Either way, create indexes when there is little activity on your server.
Turning off delayed deadlock detection causes spinlock contention.
Setting deadlock checking period to 0 causes more frequent deadlock checking. The deadlock detection process holds spinlocks on the lock structures in memory while it looks for deadlocks.
In a high transaction production environment, do not use the deadlock checking period parameter.