Locking affects performance of Adaptive Server by limiting concurrency. An increase in the number of simultaneous users of a server 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, the overall response time and throughput is 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, it severely affects the throughput of applications.
Using datapages or datarows locking, or redesigning the way transactions access the data can help reduce deadlock frequency.
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, you should create indexes when there is little activity on your server.
Turning off delayed deadlock detection causes spinlock contention –
Setting the 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 set this parameter to 0 (zero).
