Handling Concurrent Access on Tables with Exclusive Locks in GBase 8s

After a LOCK TABLE ... IN EXCLUSIVE MODE takes effect in GBase 8s, other users cannot acquire locks on that table. However, pending DDL operations may still encounter RSAM error -106 if concurrent sessions are accessing the same table through open cursors or Dirty Reads.

Why This Happens

An exclusive lock prevents new locks, but it does not evict existing Dirty Read operations or writes that are waiting for the lock to be released. Depending on the transaction isolation level, other users can hit lock timeouts or deadlocks, causing their transactions to fail.

Solution: The LAST COMMITTED Feature

Reading the most recently committed version of a locked row reduces lock conflicts dramatically. You can enable this in several ways:

1. Per‑Transaction SQL:
   

   SET ISOLATION TO COMMITTED READ LAST COMMITTED;
   

2. Global Parameter: Set the USELASTCOMMITTED configuration parameter to 'ALL' or 'COMMITTED READ'.

3. Session Environment Variable:
   

   SET ENVIRONMENT USELASTCOMMITTED 'ALL';
   

4. sysdbopen( ) Procedure: The DBA can embed the SET ENVIRONMENT and SET ISOLATION statements inside user.sysdbopen( ) or PUBLIC.sysdbopen, so the session starts with the correct settings automatically.

Important Limitation

The LAST COMMITTED feature works only with row‑level locking. It is not available for a specific table when another session holds a table‑level exclusive lock. Used properly, it allows high concurrency reads without being blocked by ongoing write transactions, keeping your gbase database responsive and consistent.