In multiuser systems, the time spent waiting for locks can be considerable. The XDB Server offers three means of adjusting the effect of locks on performance.
Adjust the isolation level to increase or decrease the length of time that locks (especially shared or read-only locks) are held. The sooner a lock is released, the sooner the resource can be locked by another transaction. The trade-off for increased concurrency is decreased consistency.
Each user can set a default isolation level in the client configuration. To specify the isolation level during a session, use the SET ISOLATION command.
On a single-workstation XDB Server that will be operating only one client session at a time, using the Exclusive Use (EU) isolation level will eliminate locking overhead.
The time to perform many batch operations that affect many records of a table can often be greatly decreased by acquiring a table lock. Table locks are acquired by issuing the LOCK TABLE command. (See the SQL Reference for more information on the LOCK TABLE command.) Performance improves because there is no need to lock individual records as they are accessed. However, in some cases it will take a considerable amount of time to acquire a table lock. Also, other processes that need to access the locked table must wait until the table lock is released.
Users can adjust the amount of time the system tries to acquire a lock. If the time is too short, performance might deteriorate because transactions cannot acquire locks and must be continuously rolled back. On the other hand, if the time is too long, performance might deteriorate because it takes longer to resolve potential deadlocks.
Users can set their own timeout periods by setting the Lock Request Timeout in the client configuration.