Question Re: Can I delay other transactions while I'm doing COMMITs? in Technology Q&A

Can I delay other transactions while I'm doing COMMITs?

VolkerBarth — Fri, 07 Sep 2012 09:52:27 GMT

This has come up in the context of that FAQ...

When there are concurrent transactions running, can a particular connection (with DBA priviledge) prevent others from committing for a certain amount of time (not to rollback but to "delay" or "suspend")?

Say, I want to do two "small" transactions and want no other transaction to commit/rollback in the interim. Is this doable (e.g. by priorities)?

Or, if not, is there a way to tell if any other transaction has committed/rollbacked between my two commits (possibly documented by two CurrentRedoPos property values)?

The goal would be to have an "exclusive commit mode" while allowing concurrent connections.

I'm aware that I can prevent others from accessing a particular table/row by means of locking but I'm asking for a general solution. (Aside: Even the exclusive table-specific access would require a WITH HOLD lock to make the lock endure the first transaction's COMMIT - and then the lock would be hold until the connection itself is terminated.)

Re: Can I delay other transactions while I'm doing COMMITs?

VolkerBarth — Sat, 08 Sep 2012 16:16:27 GMT

Well, for the following issue:

"...is there a way to tell if any other transaction has committed/rollbacked between my two commits (possibly documented by two CurrentRedoPos property values)?

One could surely get the following connection properties "Commit" and "Rollback" for all but the current connection

before the first commit
and after the second commit

and check whether there values have changed. If they are unchanged, then obviously no other connection has committed/rollbacked in the interim.

A simple approach would be use something like

select Number, connection_property('Commit', scl.Number),
  connection_property('Rlbk', scl.Number)
from sa_conn_list() scl
where Number <> connection_property('Number')
order by 1;

to get those properties, to store them in the "before first commit" situation in a local temporary table with NOT TRANSACTIONAL and compare that with the result set in the "after second commit" situation - say, with EXCEPT. If this returns an empty result set, then our connection has exclusively committed...

(FWIW, the "Rlbk" property could be ignored here fully, unless we have used isolation level 0 and would have to know about possible dirty reads. For all other isolation levels, interim rollbacks should have no effect at all on our connection.)

Re: Can I delay other transactions while I'm doing COMMITs?

regdomaratzki — Tue, 18 Sep 2012 10:21:24 GMT

I don't think there's a way to do this, and I'm not sure that your partial solution takes into account a connection that may connect, commit a change, and then disconnect between the first and second commit that you are tracking.

Re: Can I delay other transactions while I'm doing COMMITs?

VolkerBarth — Tue, 18 Sep 2012 10:29:03 GMT

A good point, as usually.

FWIW, if I really want to have "exclusive commit access", preventing new connections by means of "sa_server_option('ConnsDisabledForDB')" would be the easier part, so that shouldn't be a showstopper for my partial solution, I think.

That would also prevent new connections that still run, which would otherwise only be detected if the EXCEPT would be done biliterally, i.e. as "(old EXCEPT new) UNION (new EXCEPT old)".