Locking

So far you have seen that the database server can access data faster when the
number of requests to the database is minimized, by using efficient queries and
caching.
Another issue is if multiple users want to lock the same record at the same time.
Only one user can lock the record, and the rest wait in a queue until the record is
released. This has a substantial effect on the time used to perform a function.
Locks are typically done in transactions where more than one record is locked at
a time. The following example illustrates two different processes which lock
different items:

Process 1	Process 2
Item C - Locked	-
Item A - Locked	-
Item B - Locked	Item G - Locked
Item G - Waiting for lock from Process 2	Item A - Waiting for lock from Process 1

This situation is called a dead lock; one of the transactions must be aborted to
resolve the issue.
Locking is the biggest issue with scalability, which means that many users are
working with the same functions and data.
To reduce locking, consider using Optimistic Concurrency Control (OCC). When
the risk of overlapping transactions modifying the same data is small, optimistic
locking can increase concurrency and thus transaction throughput. Enable OCC
for a table by setting the property OccEnabled to Yes.

NOTE: Only a few tables in the standard application do not use Optimistic
Concurrency Control.Strategies to use to avoid or reduce the locking problem are as follows:

• Keep the transactions as short in time as possible. Do not
compromise the integrity of the data.

• Try to avoid locking central resources.

• Try to lock central resources in the same order. This avoids the dead
lock situation discussed previously. This serializes the process,
which means that only one user can perform this at a time.

• Never implement dialog with the user inside a transaction.

Locking is held for the time it takes to finish the process. If the process has poor
performance because of missing optimization of AOS and database
communication, the locking is a bigger issue and needs to be addressed first.

Best Regards,
Hossein Karimi