Using rs_threads to resolve conflicts externally

The rs_threads table is located in the replicate database. It contains a row for each DSI executor thread. To simulate row-level locking, it has two columns, id and seq, and enough dummy columns so that only one row fits on a page. The id column is used as a unique clustered index.

At the beginning of a transaction, the DSI executor thread updates its row in the rs_threads table with the next available sequence number. When it is ready to commit the transaction, the thread sends a select statement to the replicate data server to select, from the rs_threads table, the sequence number of the transaction that should have committed prior to the transaction.

Because the preceding transaction holds a lock on this row in rs_threads, this thread is blocked until the preceding transaction commits.

If the sequence number that is returned is less than the expected value, the thread determines whether it should roll back the transaction or retry the select operation. Because the DSI formats many commands into a single batch before submitting it to the Adaptive Server, a thread may be ready to commit before the preceding transaction has submitted any commands to the Adaptive Server. In this case, the select in the rs_threads table may be submitted several times.

If the sequence number that is returned matches the expected value, the transaction can commit.

If a transaction is ready to commit, but cannot because it is not next in proper commit order, and this transaction is holding locks on resources that are needed by a transaction that must commit before this one, a database resource deadlock occurs at the replicate database. The database resource deadlock consists of the lock on rs_threads held by the next transaction in commit order, and the locks held on resources needed by that transaction. The database resource deadlock is detected by the replicate database, which chooses a transaction to roll back. Since Replication Server must guarantee commit order, when this rollback is forced by the replicate database, Replication Server rolls back all transactions executing against the replicate database and reapplies them serially in commit order.

Replication Server manipulates the rs_threads system table with the system functions listed below. These functions have function-string-class scope. They are executed only when more than one DSI thread is defined for a connection.