[Sqlrelay-discussion] Client killed during transaction

[Ville S. <vil...@gm...>]

Hi,

What does SQL Relay connection do if the client process is killed (either
with SIGTERM or SIGKILL) during a transaction?

I realize that this is probably database specific. I've run couple of tests
using SQL Relay 0.43 (C API) and Oracle 11.2.0. My client program does this:

1. Start a transaction (auto-commit off).
2. Delete a row.
3. Sleep 60 seconds.
4. Commit.

During the 60 seconds I have killed the program either using "kill" and
"kill -9". In both cases the transaction has been committed. I also tried
shutting down SQL Relay. Again, the transaction is committed. I found this
slightly surprising, I thought the transaction would be rolled back.

This may be how Oracle behaves. I'd just like to understand what SQL Relay
connection does when the client process is killed. Does it close the
connection? In Oracle documentation (
http://docs.oracle.com/cd/B28359_01/server.111/b28318/transact.htm), it
says:

---
A transaction ends when any of the following occurs:

- A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.

- A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER. If
the current transaction contains any DML statements, Oracle Database first
commits the transaction, and then runs and commits the DDL statement as a
new, single statement transaction.

- A user disconnects from Oracle Database. The current transaction is
committed.

- A user process terminates abnormally. The current transaction is rolled
back.
---

So if SQL Relay disconnects from Oracle when the client is killed, the
current transaction is committed. Would there be a way to configure SQL
Relay to do a rollback instead if it notices that auto-commit is off and
commit hasn't been done?

I guess Oracle would actually do a rollback if the SQL Relay connection
process would terminate "abnormally".

Many thanks for any advice,
Ville