Can a DBA commit a transaction on another connection?

[VolkerBarth]

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

[Breck_Carter]

> 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 🙂

[VolkerBarth]

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

[MarkCulp · Accepted solution]

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.

[VolkerBarth · Accepted solution]

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