Using Set Lock Wait

[johneveritt]

If one issues "set lock wait <nn>", and a subsequent update
(or select) command times out waiting for a lock, Sybase
rolls back the whole of the current transaction
(error=12205, severity=17). Is there a way of preventing
this, so that only the last attempted command (update or
select) fails, but the previous of the transaction (with its
locks) remains active? We use Sybase ASE 12.5.3 on HP-UX.

[Tommy Phillips]

The lack of answers is probably enough of an answer: There is no
straightforward way to accomplish this.

There is, however, a sneaky indirect way to do it, if you are willing to
accept some risks.

1. Create a "loopback" entry in sysservers that points back to this
same ASE.
2. Encapsulate the "set lock wait" and the DML into a stored procedure.
3. Execute "loopback.<datbase>.<owner>.<wait-update-proc>"

Now, if the wait expires, the stored procedure's transaction will be
rolled back, but it will only contain the single update. To ASE, this
looks like a remote connection to/from another ASE, so the transaction
is separate from your calling transaction.

Disadvantage: If you need to roll back your entire transaction later,
you will need to add code that is smart enough to undo the remote
transaction; and, if the rollback happens because of another error, you
may not have enough control to do this. You could end up with a design
that includes some kind of pseudo-transaction-log table. Icky.

-Tommy Phillips

[ziona...@gmail.com]

Hi Tommy,

I can see that you have an experience with this kind of problem,
I have an issue trying to "force" sybase (12.5.4) executing a dynamic piece of sql in a stored procedure called from a trigger fired also in a dynamic sql sentence.
In the trigger I called the stored procedure using the syntax you said ie Exec loopback.ems..mySp and I got the same tragic 12005 error, how do you think that I can manage this problem ?
Thanks