Multiples connections is locking

João Marcelo Loureiro do Amaral <[email protected]>
Newsgroups gmane.comp.db.tds.freetds
Message-ID <[email protected]>
Hi Frediano, I need your help, please!
I built an application using unixODBC with FreeTDS (SQLServer 2000). And I
found a critical problem when I use multiple connections!
I built a Stored Procedure in my BD that stays in slow loop, and I built an
other Stored Procedure that returns very quickly.
I created a thread (posix) to execute the first procedure, and I created an
other thread to execute the second procedure (that returns fast).
I am not sharing any handles between the threads (Enviroment, Connection or
Statement) and I am not using mutex control, but the second thread locks
when try to use any handle (Enviroment by example).
First I created thread that executes slow procedure, and after 1 second, I
create thread that executes fast procedure. But the second procedure only
returns after the first ends.
I built same example in ODBC32 and works well!
Please help me!
I am migrating from windows to linux, and I am needing this very much! Any
help are welcome!
Sorry by my english...

See the functions of the threads:

//---------------------------------------------------
//Thread 1
//--------------------------------------------------
void * conecta1(void *threadid)
{
 SQLHANDLE sqlenvhandle;
    SQLHANDLE sqlconnectionhandle;
    SQLHANDLE sqlstatementhandle;
    SQLRETURN retcode;
    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&sqlenvhandle))
        goto FINISHED;

    if(SQL_SUCCESS!=SQLSetEnvAttr(sqlenvhandle,SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3, 0))
        goto FINISHED;

    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle,
&sqlconnectionhandle))
        goto FINISHED;
    SQLCHAR retconstring[1024];
    switch(SQLDriverConnect (sqlconnectionhandle,
                NULL,
                (SQLCHAR*)"DSN=SPCB140;UID=sa;PWD=senha;",
                SQL_NTS,
                retconstring,
                1024,
                NULL,
                SQL_DRIVER_NOPROMPT)){
        case SQL_SUCCESS_WITH_INFO:
            //show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
            break;
        case SQL_INVALID_HANDLE:
        case SQL_ERROR:
            show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
            goto FINISHED;
        default:
            break;
    }

    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle,
&sqlstatementhandle))
        goto FINISHED;
    cout << "Executando stp_CON_Teste" << endl;
 if(SQL_SUCCESS!=SQLExecDirect(sqlstatementhandle, (SQLCHAR*)"Exec
INFO.dbo.stp_CON_Teste", SQL_NTS)){
        show_error(SQL_HANDLE_STMT, sqlstatementhandle);
        goto FINISHED;
    }
    else{
        char name[64];
        while(SQLFetch(sqlstatementhandle)==SQL_SUCCESS){
            SQLGetData(sqlstatementhandle, 1, SQL_C_CHAR, name, 64, NULL);
            cout << name << endl;
        }
    }
FINISHED:
    SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle );
    SQLDisconnect(sqlconnectionhandle);
    SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
    SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);
    //pthread_exit(NULL);
}

//---------------------------------------------------
//Thread 2
//--------------------------------------------------
void * conecta2(void *threadid)
{
 SQLHANDLE sqlenvhandle;
    SQLHANDLE sqlconnectionhandle;
    SQLHANDLE sqlstatementhandle;
    SQLRETURN retcode;
    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE,
&sqlenvhandle))
        goto FINISHED;

    if(SQL_SUCCESS!=SQLSetEnvAttr(sqlenvhandle,SQL_ATTR_ODBC_VERSION,
(SQLPOINTER)SQL_OV_ODBC3, 0))
     goto FINISHED;

    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_DBC, sqlenvhandle,
&sqlconnectionhandle))
        goto FINISHED;
    SQLCHAR retconstring[1024];
    switch(SQLDriverConnect (sqlconnectionhandle,
                NULL,
                (SQLCHAR*)"DSN=SPCB140;UID=sa;PWD=senha;",
                SQL_NTS,
                retconstring,
                1024,
                NULL,
                SQL_DRIVER_NOPROMPT)){
        case SQL_SUCCESS_WITH_INFO:
            //show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
            break;
        case SQL_INVALID_HANDLE:
        case SQL_ERROR:
            show_error(SQL_HANDLE_DBC, sqlconnectionhandle);
            goto FINISHED;
        default:
            break;
    }

    if(SQL_SUCCESS!=SQLAllocHandle(SQL_HANDLE_STMT, sqlconnectionhandle,
&sqlstatementhandle))
        goto FINISHED;

 cout << "Executando stp_CON_Teste2" << endl;
 if(SQL_SUCCESS!=SQLExecDirect(sqlstatementhandle, (SQLCHAR*)"Exec
INFO.dbo.stp_CON_Teste2", SQL_NTS)){
        show_error(SQL_HANDLE_STMT, sqlstatementhandle);
        goto FINISHED;
    }
    else{
        char name[64];
        while(SQLFetch(sqlstatementhandle)==SQL_SUCCESS){
            SQLGetData(sqlstatementhandle, 1, SQL_C_CHAR, name, 64, NULL);
            cout << name << endl;
        }
    }
FINISHED:
    SQLFreeHandle(SQL_HANDLE_STMT, sqlstatementhandle );
    SQLDisconnect(sqlconnectionhandle);
    SQLFreeHandle(SQL_HANDLE_DBC, sqlconnectionhandle);
    SQLFreeHandle(SQL_HANDLE_ENV, sqlenvhandle);
    //pthread_exit(NULL);
}

-- 
João M L Amaral
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.