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.

I'm quite sure the answer is simply "No."

As a DBA, I can disconnect any connection with the help of the DROP CONNECTION statement. That will rollback the according outstanding transaction on that connection.

Is there any particular way to instead commit outstanding transactions on a different connection? (I'm very aware that this will introduce security problems, although as a DBA, I could also modify any DML Statement with the help of on-the fly added INSTEAD OF triggers, say modify an INSERT to do a real DELETE instead... - No, nothing I've done so far).


Background: Lately I noticed a "hanging" backup - and found out it did not finish because of its "WAIT AFTER END" clause - and could relate that to a DBISQL connection from a co-worker that had outstanding operations open since several hours. As in that case I was informed over the operations and was sure they should have be committed, but could not reach the person in question, I had to drop the connection to allow the backup to continue. In that situation I'd prefer to "enter that connection" to apply a COMMIT (and possibly leave that connection open). (Afterwards we modified the DBISQL options to use AUTO-COMMIT for those operations...)

asked 01 Apr '15, 16:49

Volker%20Barth's gravatar image

Volker Barth
29.3k287438645
accept rate: 32%

edited 01 Apr '15, 16:52

> we modified the DBISQL options to use AUTO-COMMIT

...and now you can expect panicked phone calls from users who made mistakes and could not roll back their errors... like an UPDATE executed with no WHERE clause :)

(02 Apr '15, 08:17) Breck Carter
Replies hidden

I appreciate the warning...

Note, it was just the DBISQL setting of one co-worker that was modified, not of normal users, and only used when applying a set of predefined stored procedures/functions to import data where WHERE clauses do not apply. Normal users do not use DBISQL in our case at all, they use applications with their according explicit transaction management.

That being said, I certainly have had my own experiences with different "automatic" settings of DBISQL/dbisqlc in that respect - particularly funny when DBISQL moved from the TSQL-oriented jConnect to the SA JDBC-drivers (i.e. server-side vs. client-side auto-commit, as you do know better than me).

(02 Apr '15, 08:41) Volker Barth

You are correct, there is no way for a user on connection A (owned by a DBA or some other user) to force a commit of a transaction on connection B. As you have mentioned, the only option is to disconnect the offending connection. To allow anything else would be very dangerous.

permanent link

answered 01 Apr '15, 17:17

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262
accept rate: 40%

1

OK, that seems all too reasonable.

I'd thought about modifying ISQL's "auto-commit" option in that particular situation - but alas, as this is stored on the client machine, a DBA cannot easily change that. (I guess in older versions like v8 when ISQL options were stored in the database, it might have been possible to change a particular user's ISQL options by a DBA though I would not know if this would have had impact on existing connections - particularly when they do not issue any further statements...).

(02 Apr '15, 08:45) 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

question asked: 01 Apr '15, 16:49

question was seen: 364 times

last updated: 02 Apr '15, 18:17