Transaction handling in PsqlODBC does not work - Mailing list pgsql-odbc

Hi,

I am experimenting with transactions using SELECT FOR UPDATE.

I created and populated a table:

CREATE mytable1 (id serial primary key, t varchar(10));
INSERT INTO mytable1 (t) values ('A');
INSERT INTO mytable1 (t) values ('B');
INSERT INTO mytable1 (t) values ('C');
INSERT INTO mytable1 (t) values ('D');

 From two psql sessions, I can do

BEGIN WORK;
SELECT t FROM mytable1 WHERE id=2 FOR UPDATE;
ROLLBACK;

The second session correctly block on the SELECT FROM
statement until I issue a ROLLBACK or COMMIT in the
first session but I can also abort the statement
by pressing Ctrl-C in the second psql session.

I thought I try to do the same in ODBC using SQLCancel().

It would be useful e.g. to display a "please wait" dialog
before executing a SELECT FOR UPDATE and immediately closing it
when the statement executed normally but allow the user
to abort the operation when it blocks, e.g. when another user
is just modifying the same record and be able to return to
the previous state.

My test program doesn't work the way I expect although
it behaves the same with both built-in PostgreSQL drivers in
unixODBC and with the official 8.00.0101 psqlodbc.so.
At least it's consistent. :-)

The server is PostgreSQL-8.0.3 on FC3/AMD64.

I attached my program, someone may enlighten my why it doesn't work.
It may also be a bug in my program.

The program has three mandatory options: DSN, userid and password
and an optional fourth, a string that may contain these letters:
B = issue BEGIN WORK before the SELECT FOR UPDATE
R = issue ROLLBACK after the SELECT FOR UPDATE
O = issue ROLLBACK using SQLEndTran(), overrides R
C = try to cancel the second (blocked) connection
A = execute SELECTs asynchronously

The program works like this:

Allocate two ODBC environment handles and two connection handles
for both environments. Both connection is set to use ODBC 3.0+
functions and autocommit disabled. The three threads are created,
their start time is separated by 2 seconds. The first two thread
try to do the same sequence as above:

BEGIN WORK; (optional, option B)
SELECT t FROM mytable1 WHERE id=2 FOR UPDATE;
ROLLBACK; (optional, option R or O)

The third thread tries to abort the SELECT FOR UPDATE in the
second (blocked) thread using SQLCancel(). The long running time
(the blocked state) of the second thread ensures that global
variable hStmt2 is valid.

When the threads finish, the program cleans up. Problem is,
it finishes only when there is no transaction.

Bug #1:

Despite I set SQL_AUTOCOMMIT_OFF on both connection,
the SELECT FOR UPDATE doesn't block on my second connection,
e.g. the ODBC driver doesn't issue BEGIN WORK before executing
a statement outside of a transaction. I had the impression
it's implicitely included. It isn't the case. And it seems
neither SQLTransact() nor SQLEndTran() issues "BEGIN WORK",
only the COMMIT or ROLLBACK they were instructed.
So the letter B is required. With letter B given on the commandline,
my second connection blocks correctly on SELECT FOR UPDATE.

Bug #2:

With letters B and C given, SQLCancel() blocks in another thread
for the second connection, regardless of the presence or lack of
option 'A', e.g. both in sync or async mode.

Bug #3:

With letters B and R given, the first connection blocks in
SQLAllocHandle() that should allocate the statement handle
for the manual ROLLBACK.

Bug #4:

With letters B and O given, the first connection blocks in
SQLEndTran().

Bug #3 and #4 happens with either given or omitted 'C' option,
i.e. with or without SQLCancel() and with or without 'A', sync
or async mode.

It must be a problem in my program, I don't believe noone
uses transactions from ODBC on PostgreSQL servers.

BTW anyone can lookup page 303 in the ODBC 3.5 Developer's
Guide, where is says that ODBC API funtions should be executed
asynchronously only on single-threaded OSs, on multi-threaded
OSs ODBC API functions should be called in separate threads,
which I just did. For me, it means I don't have to set
statement attribute SQL_ASYNC_ENABLE to SQL_ASYNC_ENABLE_ON.

Best regards,
Zoltán Böszörményi
/*
 * Test whether SQLCancel() really works
 * from another thread on a blocked transaction.
 *
 * Create and populate a test table in advance:
 *
 * CREATE mytable1 (id serial primary key, t varchar(10));
 * INSERT INTO mytable1 (t) values ('A');
 * INSERT INTO mytable1 (t) values ('B');
 * INSERT INTO mytable1 (t) values ('C');
 * INSERT INTO mytable1 (t) values ('D');
 *
 * and create an ODBC DSN four your database.
 *
 * Compilation:
 *
 * gcc4 -Wall -o test-sqlcancel test-sqlcancel.c -lpthread -lodbc
 *
 * Usage:
 *
 * ./test-sqlcancel DSN USERNAME PASSWORD
 *
 */

#define _REENTRANT

#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include <sql.h>
#include <sqlext.h>
#include <pthread.h>

#define ODBC2    1


SQLHENV        hEnv1, hEnv2;
SQLHDBC        hCon1, hCon2;
SQLHSTMT    hStmt1, hStmt2;
pthread_t    thr1, thr2, thr3;
int        begintran, endtran, canceltran, asynctran;
char        *sqlstmt = "select t from mytable1 where id=2 for update;";
char        *dsn, *uid, *pwd;

void *upd_thread1(void *dummy) {
  SQLRETURN     ret;
  SQLHSTMT    hStmt_tr;
  char        t[64];

  memset(t, 0, sizeof(t));
#if 0 /* This doesn't work here. */
#if ODBC2
  ret = SQLTransact(SQL_NULL_HDBC, hCon1, SQL_ROLLBACK);
#else
  ret = SQLEndTran(SQL_HANDLE_DBC, hCon1, SQL_ROLLBACK);
#endif
  printf("%s():%d after BEGIN WORK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
#endif
  if (begintran) {
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon1, &hStmt_tr);
    ret = SQLExecDirect(hStmt_tr, (SQLPOINTER)"begin work;", SQL_NTS);
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt_tr);
    printf("%s():%d after BEGIN WORK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
  }

  ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon1, &hStmt1);

  if (asynctran) {
    ret = SQLSetStmtOption(hStmt1, SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON);
  printf("%s():%d after SQLSetStmtOption() ret=0x%02x\n", __FUNCTION__, __LINE__, ret);
  }

  ret = SQLExecDirect(hStmt1, (SQLPOINTER)sqlstmt, SQL_NTS);
  printf("%s():%d before SQLFetch()\n", __FUNCTION__, __LINE__);

  ret = SQLBindCol(hStmt1, 1, SQL_C_CHAR, (SQLPOINTER)t, sizeof(t), NULL);

  ret = SQLFetch(hStmt1);
  printf("%s():%d after SQLFetch(), ret = 0x%02x, t='%s'\n", __FUNCTION__, __LINE__, ret, t);

  usleep(10000000);
  printf("%s():%d after usleep(10000000)\n", __FUNCTION__, __LINE__);

  if (endtran == 2) {
#if ODBC2
    ret = SQLTransact(SQL_NULL_HDBC, hCon1, SQL_ROLLBACK);
#else
    ret = SQLEndTran(SQL_HANDLE_DBC, hCon1, SQL_ROLLBACK);
#endif
  } else if (endtran == 1) {
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon1, &hStmt_tr);
    printf("%s():%d after AllocHandle for ROLLBACK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
    ret = SQLExecDirect(hStmt_tr, (SQLPOINTER)"rollback;", SQL_NTS);
    printf("%s():%d after ROLLBACK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt_tr);
  }

  SQLFreeHandle(SQL_HANDLE_STMT, hStmt1);
  printf("%s():%d after SQLFreeHandle()\n", __FUNCTION__, __LINE__);

  pthread_exit(0);
  return NULL;
}

void *upd_thread2(void *dummy) {
  SQLRETURN     ret;
  SQLHSTMT    hStmt_tr;
  char        t[64];

  memset(t, 0, sizeof(t));

#if 0 /* This doesn't work here. */
#if ODBC2
  ret = SQLTransact(SQL_NULL_HDBC, hCon2, SQL_ROLLBACK);
#else
  ret = SQLEndTran(SQL_HANDLE_DBC, hCon2, SQL_ROLLBACK);
#endif
  printf("%s():%d after BEGIN WORK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
#endif
  if (begintran) {
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon2, &hStmt_tr);
    ret = SQLExecDirect(hStmt_tr, (SQLPOINTER)"begin work;", SQL_NTS);
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt_tr);
    printf("%s():%d after BEGIN WORK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
  }

  ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon2, &hStmt2);

  if (asynctran) {
    ret = SQLSetStmtOption(hStmt2, SQL_ASYNC_ENABLE, SQL_ASYNC_ENABLE_ON);
  printf("%s():%d after SQLSetStmtOption() ret=0x%02x\n", __FUNCTION__, __LINE__, ret);
  }

  ret = SQLExecDirect(hStmt2, (SQLPOINTER)sqlstmt, SQL_NTS);
  printf("%s():%d before SQLFetch()\n", __FUNCTION__, __LINE__);

  ret = SQLBindCol(hStmt2, 1, SQL_C_CHAR, (SQLPOINTER)t, sizeof(t), NULL);

  ret = SQLFetch(hStmt2);
  printf("%s():%d after SQLFetch(), ret = 0x%02x, t='%s'\n", __FUNCTION__, __LINE__, ret, t);

  if (endtran == 2) {
#if ODBC2
    ret = SQLTransact(SQL_NULL_HDBC, hCon2, SQL_ROLLBACK);
#else
    ret = SQLEndTran(SQL_HANDLE_DBC, hCon2, SQL_ROLLBACK);
#endif
    printf("%s():%d after ROLLBACK, ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
  } else if (endtran == 1) {
    ret = SQLAllocHandle(SQL_HANDLE_STMT, hCon2, &hStmt_tr);
    printf("%s():%d after AllocHandle for ROLLBACK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
    ret = SQLExecDirect(hStmt_tr, (SQLPOINTER)"rollback;", SQL_NTS);
    printf("%s():%d after ROLLBACK; ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);
    SQLFreeHandle(SQL_HANDLE_STMT, hStmt_tr);
  }

  SQLFreeHandle(SQL_HANDLE_STMT, hStmt2);
  printf("%s():%d after SQLFreeHandle()\n", __FUNCTION__, __LINE__);

  pthread_exit(0);
  return NULL;
}

void *cancel_thread(void *dummy) {
  SQLRETURN    ret;

  printf("%s():%d before SQLCancel()\n", __FUNCTION__, __LINE__);
  ret = SQLCancel(hStmt2);
  printf("%s():%d after SQLCancel() ret = 0x%02x\n", __FUNCTION__, __LINE__, ret);

  pthread_exit(0);
  return NULL;
}

int main(int argc, char **argv) {
  SQLRETURN    ret;
  int        err, i;

  if (argc != 4 && argc != 5) {
    printf("Usage: test-sqlcancel dsn uid pwd [B[R[O]]]\n");
    printf("B = issue BEGIN WORK before the SELECT FOR UPDATE\n");
    printf("R = issue ROLLBACK after the SELECT FOR UPDATE\n");
    printf("O = issue ROLLBACK using SQLEndTran(), implicitely activates R\n");
    printf("C = try to cancel the second (blocked) connection\n");
    printf("A = execute SELECTs asynchronously\n");
    return 0;
  }
  dsn = argv[1];
  uid = argv[2];
  pwd = argv[3];
  begintran = endtran = canceltran = asynctran = 0;
  if (argc == 5) {
    for (i = 0; i < strlen(argv[4]); i++) {
      switch (argv[4][i]) {
      case 'B':
        begintran = 1;
        break;
      case 'R':
        if (!endtran)
          endtran = 1;
        break;
      case 'O':
        if (endtran != 2)
          endtran = 2;
        break;
      case 'C':
        canceltran = 1;
        break;
      case 'A':
        asynctran = 1;
        break;
      default:
        break;
      }
    }
  }

  ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv1);
  if (ret == SQL_ERROR)
    return 0;

  ret = SQLSetEnvAttr(hEnv1, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
  if (ret == SQL_ERROR)
    goto out_henv1;

  ret = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &hEnv2);
  if (ret == SQL_ERROR)
    goto out_henv1;

  ret = SQLSetEnvAttr(hEnv2, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
  if (ret == SQL_ERROR)
    goto out_henv2;

  ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv1, &hCon1);
  if (ret == SQL_ERROR)
    goto out_henv2;

  ret = SQLConnect(hCon1,
          (SQLPOINTER)dsn, SQL_NTS,
          (SQLPOINTER)uid, SQL_NTS,
          (SQLPOINTER)pwd, SQL_NTS);
  if (ret == SQL_ERROR)
    goto out_hcon1;

  ret = SQLSetConnectAttr(hCon1, SQL_ATTR_AUTOCOMMIT,
          (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER);
  if (ret == SQL_ERROR)
    goto out_discon1;

  ret = SQLAllocHandle(SQL_HANDLE_DBC, hEnv2, &hCon2);
  if (ret == SQL_ERROR)
    goto out_discon1;

  ret = SQLConnect(hCon2,
          (SQLPOINTER)dsn, SQL_NTS,
          (SQLPOINTER)uid, SQL_NTS,
          (SQLPOINTER)pwd, SQL_NTS);
  if (ret == SQL_ERROR)
    goto out_hcon2;

  ret = SQLSetConnectAttr(hCon2, SQL_ATTR_AUTOCOMMIT,
          (SQLPOINTER)SQL_AUTOCOMMIT_OFF, SQL_IS_UINTEGER);
  if (ret == SQL_ERROR)
    goto out_discon2;

  err = pthread_create(&thr1, NULL, upd_thread1, NULL);
  usleep(2000000);
  err = pthread_create(&thr2, NULL, upd_thread2, NULL);

  if (canceltran) {
    usleep(2000000);
    err = pthread_create(&thr3, NULL, cancel_thread, NULL);
    pthread_join(thr3, NULL);
  }

  pthread_join(thr2, NULL);
  pthread_join(thr1, NULL);

  printf("threads exited\n");

out_discon2:
  SQLDisconnect(hCon2);
out_hcon2:
  SQLFreeHandle(SQL_HANDLE_DBC, hCon2);
out_discon1:
  SQLDisconnect(hCon1);
out_hcon1:
  SQLFreeHandle(SQL_HANDLE_DBC, hCon1);
out_henv2:
  SQLFreeHandle(SQL_HANDLE_ENV, hEnv2);
out_henv1:
  SQLFreeHandle(SQL_HANDLE_ENV, hEnv1);
  return 0;
}