Transaction table without locking issues

ronvdw

In one of our solutions we use a transaction table which has the following layout:
Entry No. -> this field has been provided with the AutoIncrement property
Record ID

The purpose of this table is to register record id's at several NAV processes. This function may not block existing processes. The processing of these record id's is done in a separate process (Job Queue or a web service). Records are deleted from the transaction table after processing. Deleting these records, however, may place a lock on the table which prevents new records to be added.

Does anyone has a proper solution for this scenario?

ara3n

I suggest to test it with option Always rowlock (File->database->Alter->Advanced tab);

that's the only way to can control how sql decides to lock the records. But even then if you are running a serialized transaction (i.e. Posting) , SQL may decide to lock the whole table, or page, or index.

I have had a table with PK Batch No.,"entry no." Where Entry no was auto incremented and as long as there weren't any secondary indexes, sql didn' t lock two processes deleting and inserting concurrently.