Forum

General discussion on using the Kognitio Analytical Platform.
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Explaining autocommit behaviour

by markc » Thu Nov 27, 2014 2:23 pm

Can you explain the autocommit behaviour of Kognitio's ODBC driver?
Reply with quote Top
Contributor
Offline
User avatar
Posts: 386
Joined: Thu May 23, 2013 4:48 pm

Re: Explaining autocommit behaviour

by markc » Thu Nov 27, 2014 2:24 pm

ODBC AUTOCOMMIT RULES

When the ODBC driver is in autocommit mode, applications have to be mindful that an automatic commit can close cursors. This can cause problems if, for example, an application:
  • opens statement handle A
  • executes a SELECT statement on statement handle A
  • opens statement handle B
  • executes an UPDATE statement on statement handle B
  • tries to fetch from statement handle A.
The fetch from statement handle A will give a Function Sequence Error (HY010), because the UPDATE caused an autocommit which closed the cursor on statement handle A.

It is up to the application to code around this behaviour: http://msdn.microsoft.com/en-us/library ... 85%29.aspx

KOGNITIO ODBC BEHAVIOUR AND POINTS TO NOTE

To allow greater interoperability with third-party applications which use autocommit mode, the Kognitio ODBC driver only autocommits in certain circumstances, as detailed below.

The driver is allowed to autocommit on any SQLCloseCursor(), or, equivalently, any call to SQLFreeStmt() with the option SQL_CLOSE. Kognitio's ODBC driver will autocommit in this case if and only if the connection handle is in autocommit mode, and there is an open transaction, and there are no other open cursors on the same connection handle. This also applies to freeing a statement handle using SQLFreeHandle().

The driver is allowed to autocommit on any SQLExecute(), which may close cursors on other statement handles. However, Kognitio's ODBC driver only autocommits on an Execute if the statement was not a SELECT statement. This means that even in autocommit mode a SELECT statement will hold locks until the last statement on the connection handle has its cursor closed. It also means that an UPDATE, INSERT or DELETE statement run in autocommit mode will close any open cursors in the same connection handle.

SQLEndTran(), of course, always ends the transaction, as it would in transaction mode.

If the ODBC driver is in transaction mode, and there is a transaction open, then switching to autocommit mode automatically commits the open transaction. This is consistent with the ODBC specification. This autocommit, like any other commit, closes any open cursors on the connection handle.

WORKAROUNDS

If an application runs many statement handles concurrently, and all statement handles are associated with the same connection handle, all the statements run in the same transaction space. This means that any commit, automatic or otherwise, may close cursors on all statement handles.

To avoid this, the application has two options:
  • Don't run in autocommit mode, and instead do manual commits when it is known that there are no open cursors still to be fetched from.
  • Use more than one connection handle. In no case does any commit on one connection handle, automatic or otherwise, affect the state of any cursors on statement handles associated with a different connection.
Reply with quote Top

Who is online

Users browsing this forum: No registered users and 2 guests

cron