Re: [Sqlrelay-discussion] Client killed during transaction

[David M. <dav...@fi...>]

If the client process is killed, then SQL Relay's behavior is determined 
by the endofsession paramter in the sqlrelay.conf file:

<instance id="oracleexample" ... endofsession="commit" ...>
    ...
</instance>

If it is set to "commit" then SQL Relay will issue a commit when the 
client disconnects.  If it is set to "rollback" then SQL Relay will 
issue a rollback when the client disconnects.  Currently, SQL Relay 
doesn't check to see whether the client disconnected on purpose or not 
though.

To get the behavior you're looking for, you could probably set 
endofsession="rollback" in sqlrelay.conf and then make sure that your 
app issues commit()'s itself for any transactions that it doesn't want 
automatically rolled-back.

Let me know if this answers your question or not.

Dave
dav...@fi...

On 07/11/2012 11:26 AM, Ville Silventoinen wrote:
> 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
>
>
>
> ------------------------------------------------------------------------------
> Live Security Virtual Conference
> Exclusive live event will cover all the ways today's security and
> threat landscape has changed and how IT managers can respond. Discussions
> will include endpoint security, mobile security and the latest in malware
> threats. http://www.accelacomm.com/jaw/sfrnl04242012/114/50122263/
>
>
>
> _______________________________________________
> Sqlrelay-discussion mailing list
> Sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlrelay-discussion
>