I'm sorry! No you are not correct. vstatus is updating the same record that status is.

So...

A) Client 1 locks record 3 via TABLEUPDATE('vstatus')
B) Client 3 locks record 1 via TABLEUPDATE('vstatus')
C) Client 1 locks record 3 via tableupdate('status')
D) Client 3 locks record 1 via tableupdate('status')

Could the whole table getting locked somehow? Regardless I think SET REPROCESS should handle the problem. Still confused.


>Well, you didn't really answer my question (<g>), but I'll assume I'm correct that the tableupdate("vstatus") is updating a different record than tableupdate("status") is. So the problem is you're hitting a deadlock.
>
>The thing to remember is that when in a transaction, the TABLEUPDATE() doesn't actually modify the table. It simply places all the necessary locks to prevent any other user from modifying those same records before we have a chance to complete the transaction. And those locks are kept in place until the END TRANSACTIN or the ROLLBACK. So in your case, what is happening is something like this:
>A) Client 1 locks record 3 via TABLEUPDATE('vstatus')
>B) Client 3 locks record 1 via TABLEUPDATE('vstatus')
>C) Client 1 now tries to lock record 1 via TABLEUPDATE('status'), but that record is already locked by Client 3.
>D) Client 3 now tries to lock record 3 via TABLEUPDATE('status'), but that record is already locked by Client 1.
>
>The two clients are now deadlocked, each trying to get a lock on a record that the other has locked.
>
>I'm not sure why SET REPROCESS TO 30 SECONDS isn't having the desired effect. But I think your best course of action may be to switch to buffermode 4, so the necessary locks are taken before entering the transaction. The SET REPROCESS should produce the desired behavior when the locks are taken outside of a transaction.
>
>Another option is to avoid the deadlock, but that could take some major re-design of your app. The easiest way is to use some sort of semaphore that a client must lock before entering the transaction. One way to do this that would fit into your current structure is to have a semaphore table with one record, and have each client lock that record before entering the transaction. This would make a significant bottleneck (only one transaction at a time), and would remove any benefit of using transactions.
>
>You can expand the semaphore concept to use mutually exclusive categories. For example, you could lock a specific record in the semaphore table according to certain rules:
> 1) Lock record one if the tableupdates will be updating odd numbered records in both vstatus and status
> 2) Lock record two if updating even numbered records in both cursors.
> 3) Lock record three if updating an odd record in vstatus and an even record in status
> 4) Lock record four if updating an even record in vstatus and an odd record in status.
>
>The above example would allow up to 4 simultaneous transactions, and still prevent deadlocks.
>
>Another option is to SET REPROCESS TO 1, perhaps just for the second TABLEUPDATE(), and implement a method of retrying the transaction X times before reporting an error.
>
>I hope all these ramblings weren't too confusing. :) Transactions are powerful tools, but hitting a deadlock within a transaction is a bad thing.
>
>-Tom
>
>
>This post is provided AS-IS with no warranties, and confers no rights.]