I've got a T-SQL stored procedure running on a Sybase ASE database server that is sometimes failing to commit all of its operations, even though it completes without exception. Here's a rough example of what it does.
BEGIN TRANSACTION
UPDATE TABLE1
SET FIELD1 = @NEW_VALUE1
WHERE KEY1 = @KEY_VALUE1
IF @@error <> 0 OR @@rowcount <> 1 BEGIN
ROLLBACK
RETURN 1
END
UPDATE TABLE2
SET FIELD2 = @NEW_VALUE2
WHERE KEY2 = @KEY_VALUE2
IF @@error <> 0 OR @@rowcount <> 1 BEGIN
ROLLBACK
RETURN 2
END
INSERT TABLE2 (FIELD2, FIELD3)
VALUES (@NEW_VALUE3a, @NEW_VALUE3b)
IF @@error <> 0 OR @@rowcount <> 1 BEGIN
ROLLBACK
RETURN 3
END
COMMIT TRANSACTION
RETURN 0
The procedure is called at least hundreds of times a day. In a small percentage of those cases (probably < 3%), only the INSERT statement commits. The proc completes and returns 0, but the two UPDATEs don't take. Originally we thought it might be that the WHERE clauses on the UPDATEs weren't matching anything, so we added the IF @@rowcount logic. But even with those checks in there, the INSERT is still happening and the procedure is still completing and returning 0.
I'm looking for ideas about what might cause this type of problem. Is there anything about the way SQL transactions work, or the way Sybase works specifically, that could be causing the COMMIT not to commit everything? Is there something about my IF blocks that could allow the UPDATE to not match anything but the procedure to continue? Any other ideas?