Lock Time-out
When Microsoft SQL Server Compact 4.0 cannot grant a lock to a transaction on a resource because another transaction already owns a conflicting lock on that resource, the transaction requesting the lock is blocked until the other transaction releases the lock. This situation can also result from a deadlock. SQL Server Compact 4.0 has a default lock time-out period of two seconds. There is no way to test whether a resource is locked before locking it, except to attempt to access the data and potentially time out.
Note
You can use the sys.lock_information system view to determine whether a lock request is being blocked, and to help determine what is blocking it. For more information, see Displaying Locking Information.
The LOCK_TIMEOUT setting permits an application to set a maximum time for a statement to wait for a blocked resource. When a statement has waited longer than the LOCK_TIMEOUT setting, the blocked statement is canceled automatically, and the error message SSCE_M_LOCKTIMEOUT, "The system timed out waiting for a lock," is returned to the application. However, SQL Server Compact 4.0 does not roll back or cancel any transaction that contains the statement. The application must have an error handler that can trap the error message SSCE_M_LOCKTIMEOUT. If an application does not trap the error, it can proceed without knowing that an individual statement within a transaction has been canceled. Errors can occur because statements later in the transaction might depend on the statement that was not executed.
Implementing an error handler that traps the error message SSCE_M_LOCKTIMEOUT permits an application to handle the time-out situation and take remedial action, such as automatically resubmitting the statement that was blocked or rolling back the entire transaction.
To set the current LOCK_TIMEOUT setting for a session, execute the SET LOCK_TIMEOUT syntax, as the following code example shows:
SET LOCK_TIMEOUT 2000;