Update Zero Rows and then Committing?

Question

Which procedure is more performant for an update which affects zero rows?

UPDATE table SET column = value WHERE id = number;

IF SQL%Rowcount > 0 THEN
 COMMIT;
END IF;

or

UPDATE table SET column = value WHERE id = number;

COMMIT;

In other words if an Update affect ZERO rows and a commit is issued am I incurring any added expense at all?

I have a system which is being hampered by log file sync waits... and I'm wondering if issuing a commit; against a transaction which affects zero rows will write that statement to the log or not and thus cause more contention on LGWR.

Answer 1

COMMIT does force the log file sync so the system will have to wait indeed.

However, ROLLBACK does too and at some time either of them will have to happen.

So if you issue neither COMMIT nor ROLLBACK, you are just staying with an open transaction which sooner or later will cause a log sync wait.

Probably, you want to batch you UPDATE operations rather than waiting for a first successful update and committing it.

Answer 2

There are risks in this. Technically while the UPDATE may affect zero rows, it can fire before or after update triggers on the table (not at row level). Those triggers could potentially "do something" that requires a commit/rollback.

Safer to check to see if LOCAL_TRANSACTION_ID is set.

Answer 3

There are any number of reasons which can underlie waits for log file sync. It seems unlikely that the main culprit is committing SQL statements which have updated zero rows. It is true that issuing too many commits can be the cause of this problem. For instance, if the application is set up to commit after every statement (e.g. by using AUTOCOMMIT=TRUE) instead of designing proper transactions. If this is the cause then there is not much you can do, short of a major rewrite of the application.

If you want to delve deeper into the root causes of your problem I recommend you read this exhaustive (and exhausting) article by Pythian's Riyaj Shamsudeen on Tuning ‘log file sync’ Event Waits.