The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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

asked 07 Sep '12, 09:52

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 18 Sep '12, 12:06


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

permanent link

answered 08 Sep '12, 16:16

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 08 Sep '12, 16:20

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.

(18 Sep '12, 10:21) Reg Domaratzki
Replies hidden

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

(18 Sep '12, 10:29) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×47
×17
×6
×2

question asked: 07 Sep '12, 09:52

question was seen: 1,354 times

last updated: 18 Sep '12, 12:06