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

[VolkerBarth]

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.)

[VolkerBarth · Accepted solution]

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.)

[regdomaratzki · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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)".