Deadlock when delete rows with blob.

[Former Member]

Hello

I have the following issue. I try to clean a table that contains a BLOB column.

To do this I use SQLDBC I create a statement that contains the LOB column, I fetch the line, load the Blob. and before fetching the next line, I send a delete request. and I fall in the deadlock.

If I fetch the next line before sending the delete request there is no deadlock, there is also no deadlock if i do not request le LOB column in the select.

In fact when I made select with LOB, there is the follwing line in DOMAIN.LOCKS

SESSION;TRANSCOUNT;PROCESS;USERNAME;DATE;TIME;TERMID;LASTWRITE;LOCKMODE;LOCKSTATE;APPLPROCESS;APPLNODE;SCHEMANAME;OWNER;TABLENAME;TABLEID;ROWIDLENGTH;ROWIDHEX;ROWID;

295513; 4290;438;ESKDBADM;2010-06-17;12:05:46;ly-delorme-u..1674;?;tab_share;?;5748;LY-DELORME.esker.corp;?;?;?;0000800000046919;0;?;?

when i send the delete request the following lock appears in DOMAIN.LOCKS. A exclusif lock on the column I want to delete. It's ok

SESSION;TRANSCOUNT;PROCESS;USERNAME;DATE;TIME;TERMID;LASTWRITE;LOCKMODE;LOCKSTATE;APPLPROCESS;APPLNODE;SCHEMANAME;OWNER;TABLENAME;TABLEID;ROWIDLENGTH;ROWIDHEX;ROWID;

295521; 4287;167;ESKDBADM;2010-06-17;12:05:49;ly-delorme-u..1674; 5;row_exclusive;write;5748;LY-DELORME.esker.corp;ESKDBADM;ESKDBADM;DBM350_AUTOTESTXML;0000000000000934;5;00C51133800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000;

BUT there is a dead lock , I think because of the following entry in DOMAIN.LOCK_WAITS

TABLENAME;TABLEID;H_TERMID;H_PROCESS;H_APPLPROCESS;H_APPLNODE;H_ROWIDHEX;H_ROWID;H_LOCKMODE;H_DATE;H_TIME;H_LOCKTIMEOUT;R_TERMID;R_PROCESS;R_APPLPROCESS;R_APPLNODE;R_ROWIDHEX;R_ROWID;R_REQMODE;R_DATE;R_TIME;R_REQTIMEOUT;

?;0000800000046919;ly-delorme-u..1674;438;5748;LY-DELORME.esker.corp;?;?;tab_share;2010-06-17;12:05:46;?;ly-delorme-u..1674;167;5748;LY-DELORME.esker.corp;?;?;tab_exclusive;2010-06-17;12:05:49; 3585

The database request a tab_exclusive lock on tableid 0000800000046918 but there is already a tab_share lock on this.

Do you why there is a lock on the table : tableid 0000800000046918 ?

How to avoid this lock using SQLDBC ?

Thanks for you help.

Yann.

[Former Member]

Hello

The call to setFetchSize(1) fix my issue.

There is no lock in this case.

Thanks you very much.

regards.

Yann.

[former_member185699]

Hi,

if you run a query in SQLDBC then several rows will be fetch to the client for best performance. I guess in your case all these rows are looked that are currently fetched. If you fetch the next chunk then the looks for the rows fetched before will be released.

What can do?

1. You can switch to a single row fetch by setting the fetch size to 1 via SQLDBC::SQLDBC_ResultSet::setFetchSize ( SQLDBC_Int2 fetchsize ). Then the look for the current row will be released when you fetch the next row.

2. Or you collect all columns that you would like to delete first and delete them afterwards when you finished fetching the resultset.

3. Another possibility would be to use the UpdatableResultSet API of SQLDBC.

Hope that helps.

Best regards,

Marco

[Former Member]

Hello,

>

> select fileid, root, type, primaryfileid from files where fileid='000080000004691D'
>

return nothing

Statement 'select fileid, root, type, primaryfileid from files where fileid='000080000004691D'' successfully executed in 30 ms. Started: 2010-06-21 15:28:01 - No result

The isolation level is 1, because I do not want uncommitted lines.

select sessionid, applicationprocess, isolationlevel  from sessions
SESSIONID;APPLICATIONPROCESS;ISOLATIONLEVEL;
295699;7472;1

I agree with you this is not really a dead lock. But a process can freeze my application by calling this kind of request.

What I do not understand is why the row is unlocked if I fetch the next row ? or how can I unlock the row after reading the blob using SQLDBC ?

I seems that when I execute the same request with the ODBC driver (ISOLATION LEVEL=1) the lock is not set ? So I think I forget something but what ?

I have already read tSQLLock document but thay do not explain how to unlock a blob column with SQLDBC ? I don't think I have to send a UNLOCK statement manually ?

Yann.

[Former Member]

// the end of the source file.

SQLDBC_Connection *connectDB(SQLDBC_Environment * env)

{

char errorText[200];

SQLDBC_Retcode rc;

if (g_env == NULL)

{

/*

• Every application has to initialize the SQLDBC library by getting a

• reference to the ClientRuntime and calling the SQLDBC_Environment constructor.

*/

SQLDBC_IRuntime *runtime;

runtime = SQLDBC::GetClientRuntime(errorText, sizeof(errorText));

if (!runtime) {

fprintf(stderr, "Getting instance of the ClientRuntime failed %s\n", errorText);

usage();

}

env = new SQLDBC_Environment(runtime);

g_env = env;

}

/*

• Create a new connection object and open a session to the database.

*/

SQLDBC_Connection *conn = env->createConnection();

printf("Connecting to '%s' on '%s' as user '%s'\n",

(char)connectArgs.dbname, (char)connectArgs.host, (char*)connectArgs.username);

rc = conn->connect(connectArgs.host, connectArgs.dbname,

connectArgs.username, connectArgs.password);

if(SQLDBC_OK != rc) {

fprintf(stderr, "Can't connect to '%s'.\nERROR: %d:'%s'\n",

connectArgs.dbname, conn->error().getErrorCode(), conn->error().getErrorText());

exit(1);

}

return conn;

}

static void parseArgs (int argc, wchar_t **argv)

{

argv0 = wcsdup(argv[0]);

/*

• setting defaults for demo database

*/

connectArgs.username = "ESKDBADM";

connectArgs.password = "DELORME";

connectArgs.dbname = "EDP350";

connectArgs.host = "ly-delorme";

/*

• use values from command line

*/

if (argc > 5) {

connectArgs.request = argv [5];

}

if (argc > 4) {

connectArgs.host = argv [4];

}

if (argc > 3) {

connectArgs.dbname = argv [3];

}

if (argc > 2) {

connectArgs.password = argv [2];

}

if (argc > 1) {

if (!wcsicmp(argv [1], L"-h"))

usage();

else {

connectArgs.username = argv [1];

}

}

strupper(connectArgs.username);

strupper(connectArgs.password);

}

void exitOnError(SQLDBC_ErrorHndl &err)

{

if(err) {

fprintf(stderr, "Execution stopped %d:'%s'", err.getErrorCode(), err.getErrorText());

exit(1);

}

}

static void usage()

{

wchar_t *s = wcsrchr(argv0, L'/');

if (!s)

s = wcsrchr(argv0, L'
');

if (s)

*s = '\0';

printf("Syntax: %s [-h] | [<connect_options>]\n"

"\tconnect_options :: \n"

"\t\t[ <username> \n"

"\t\t[ <password>\n"

"\t\t[ <database_name>\n"

"\t\t[ <database_server> ]]]]\n"

"\t\t[ <request> ]]]]\n"

"\tCalling %s without any arguments will use user '%s','%s'\n"

"\t\ton database '%s' at server '%s'\n",

argv0, argv0,

connectArgs.username, connectArgs.password,

connectArgs.dbname, connectArgs.host);

exit(1);

}

[Former Member]

Hello,

Here is code that explain my issue.

I create a table like this :

create table "ESKDBADM"."DBM350_AUTOTESTXML"( "MSN" INTEGER not null, "DIST_FILE" BLOB, constraint SYSPRIMARYKEY primary key ("MSN"))

And then I try to delete row with the following program. I do not understand why the request SELECT Msn, DIST_FILE FROM DBM350_AUTOTESTXML set a share lock on the tableid : 000080000004691D.

I do not find this tableid in the "SYSINFO"."FILES" table.

SESSION;TRANSCOUNT;PROCESS;USERNAME;DATE;TIME;TERMID;LASTWRITE;LOCKMODE;LOCKSTATE;APPLPROCESS;APPLNODE;SCHEMANAME;OWNER;TABLENAME;TABLEID;ROWIDLENGTH;ROWIDHEX;ROWID;

295697; 5220;172;ESKDBADM;2010-06-21;11:01:40;ly-delorme-u..1CDC;?;tab_share;?;7388;LY-DELORME.esker.corp;?;?;?;000080000004691D;0;?;?

// SQLDBC.cpp : Defines the entry point for the console application.

//

#include "stdafx.h"

#include <stdio.h>

#include <tchar.h>

#include <comdef.h>

#include <iostream>

#include "SQLDBC.h"

using namespace SQLDBC;

static void parseArgs(int argc, wchar_t **argv);

static SQLDBC_Connection *connectDB(SQLDBC_Environment * env = NULL);

static char* convertToUCS2(char *src);

static void usage();

SQLDBC_Environment *g_env;

typedef struct ConnectArgsT {

bstrt username;

bstrt password;

bstrt dbname;

bstrt host;

bstrt request;

} ConnectArgsT;

ConnectArgsT connectArgs;

void exitOnError(SQLDBC_ErrorHndl &err);

int _tmain(int argc, _TCHAR* argv[])

{

parseArgs(argc, argv);

SQLDBC_Connection *conn = connectDB();

//* Create a new statment object and execute it.

SQLDBC_PreparedStatement *stmt = conn->createPreparedStatement();

// FREEZE

if (connectArgs.request.length() == 0)

connectArgs.request = "SELECT Msn, DIST_FILE FROM DBM350_AUTOTESTXML";

/* NO FREEZE

if (connectArgs.request.length() == 0)

connectArgs.request = "SELECT Msn FROM DBM350_AUTOTESTXML";

*/

char command = (char)((LPCWSTR)(connectArgs.request));

//* Executes the UNICODE encoded SQL command.

SQLDBC_Retcode ret = stmt->prepare((const char*)command, SQLDBC_StringEncodingType::UCS2Swapped);

exitOnError(stmt->error());

stmt->execute();

exitOnError(stmt->error());

// * Get the resultset.

SQLDBC_ResultSet *result;

result = stmt->getResultSet();

exitOnError(stmt->error());

// * Fetch the row from the resultset.

result->next();

SQLDBC::SQLDBC_RowSet* pRowset = result->getRowSet();

ret = pRowset->fetch();

exitOnError(result->error());

wchar_t szString[30];

SQLDBC_Length ind;

/*

• Get a string value from the column.

*/

result->getObject(1, SQLDBC_HOSTTYPE_UCS2_SWAPPED, szString, &ind, sizeof(szString)); // Retreive MSN.

// Delete de line.

// Create a new connection for delete

SQLDBC_Connection *conn2 = connectDB(g_env);

//* Create a new statment object and execute it.

SQLDBC_PreparedStatement *stmt2 = conn2->createPreparedStatement();

std::wstring st = L"DELETE FROM DBM350_AUTOTESTXML WHERE MSN=";

st += szString;

stmt2->prepare((char*)st.c_str(), SQLDBC_StringEncodingType::UCS2Swapped);

//* Executes the UNICODE encoded SQL command.

ret = stmt2->execute();

exitOnError(stmt->error());

printf("%s\n", szString);

return 0;

}

static char *convertToUCS2(char *src)

{

int len = strlen(src);

wchar_t w=(wchar_t)calloc(len, sizeof(wchar_t));

int nc = mbstowcs(w, src, len);

if ((size_t)-1 == nc) {

fprintf(stderr, "Conversion to UCS2 failed. Execution stopped.\n");

exit(1);

}

short s=(SQLDBC_Int2)calloc(nc+1, sizeof(SQLDBC_Int2));

int i;

for(i=0; i<nc; i++) {

s<i> = w<i>;

}

free(w);

return (char*)s;

}

static char* strupper(char *str)

{

char *p=str;

while(*p) {

p=toupper(p);

++p;

}

return str;

}

static wchar_t *argv0;

#ifdef WIN32

#define STRICMP _stricmp

#else

#define STRICMP strcasecmp

#endif

[lbreddemann]

> create table "ESKDBADM"."DBM350_AUTOTESTXML"( "MSN" INTEGER not null, "DIST_FILE" BLOB, constraint SYSPRIMARYKEY primary key ("MSN"))

Ok, I did the same, and you'll see that you indeed got two database internal files for this:

sqlcli -d db770 -u lars,***

Welcome to the SAP MaxDB interactive terminal.

Type:  \h for help with commands
       \q to quit

sqlcli db770=>
sqlcli db770=> \mu
Multiline mode switched ON
sqlcli db770=> select fileid, root, f.type, primaryfileid from files f
> join tables t
>       on  (t.tableid=f.fileid
>               or t.tableid=f.primaryfileid)
> where
> t.tablename='DBM350_AUTOTESTXML'
> ;
| FILEID             | ROOT          | TYPE                 | PRIMARYFILEID                |
| ------------------ | ------------- | -------------------- | ---------------------------- |
| 0x0000000000024BBC |          4662 | SHORT COLUMN FILE    | 0x0000000000024BBB           |
| 0x0000000000024BBB |        111175 | TABLE                | ?                            |

2 rows selected (330.374 msec)

So, maybe you're seing a lock on the short column file that had been created for the LOB column.

> And then I try to delete row with the following program. I do not understand why the request SELECT Msn, DIST_FILE FROM DBM350_AUTOTESTXML set a share lock on the tableid : 000080000004691D.

> I do not find this tableid in the "SYSINFO"."FILES" table.

>SESSION;TRANSCOUNT;PROCESS;USERNAME;DATE;TIME;TERMID;LASTWRITE;LOCKMODE;LOCKSTATE;APPLPROCESS;APPLNODE;SCHEMANAME;OWNER;TABLENAME;TABLEID;ROWIDLENGTH;ROWIDHEX;ROWID;

> 295697; 5220;172;ESKDBADM;2010-06-21;11:01:40;ly-delorme-u..1CDC;?;tab_share;?;7388;LY-DELORME.esker.corp;?;?;?;000080000004691D;0;?;?

Hmm... you wrote that you didn't find the file...

Please reproduce this and use the folliwing statement:

select fileid, root, type, primaryfileid from files where fileid='000080000004691D'

You should get at least a root for this.

With this root, run the following query:

select tableid, schemaname, owner, tablename, indexname, type, root from sysdd.roots where root = < put root in here>

Also, please provide some information about the sessions isolation modes:

select sessionid, applicationprocess, isolationlevel  from sessions

> // Create a new connection for delete

Ok, there you go.

Two connections - one reading data (and thus requiring a share lock) and one trying to exclusively access the data (perform the delete).

All that from within one application process.

That's not a DB deadlock, but a home-grown application deadlock.

You may want to review the internals documentation on locking available at the [MaxDB Homepage|http://maxdb.sap.com/training/internals_7.6/locking_EN_76.pdf].

regards,

Lars

[Former Member]

Hello,

Here is code that explain my issue.

I create a table like this :

create table "ESKDBADM"."DBM350_AUTOTESTXML"( "MSN" INTEGER not null, "DIST_FILE" BLOB, constraint SYSPRIMARYKEY primary key ("MSN"))

And then I try to delete row with the following program. I do not understand why the request SELECT Msn, DIST_FILE FROM DBM350_AUTOTESTXML set a share lock on the tableid : 000080000004691D.

I do not find this tableid in the "SYSINFO"."FILES" table.

SESSION;TRANSCOUNT;PROCESS;USERNAME;DATE;TIME;TERMID;LASTWRITE;LOCKMODE;LOCKSTATE;APPLPROCESS;APPLNODE;SCHEMANAME;OWNER;TABLENAME;TABLEID;ROWIDLENGTH;ROWIDHEX;ROWID;

295697; 5220;172;ESKDBADM;2010-06-21;11:01:40;ly-delorme-u..1CDC;?;tab_share;?;7388;LY-DELORME.esker.corp;?;?;?;000080000004691D;0;?;?

// SQLDBC.cpp : Defines the entry point for the console application.

//

#include "stdafx.h"

#include <stdio.h>

#include <tchar.h>

#include <comdef.h>

#include <iostream>

#include "SQLDBC.h"

using namespace SQLDBC;

static void parseArgs(int argc, wchar_t **argv);

static SQLDBC_Connection *connectDB(SQLDBC_Environment * env = NULL);

static char* convertToUCS2(char *src);

static void usage();

SQLDBC_Environment *g_env;

typedef struct ConnectArgsT {

bstrt username;

bstrt password;

bstrt dbname;

bstrt host;

bstrt request;

} ConnectArgsT;

ConnectArgsT connectArgs;

void exitOnError(SQLDBC_ErrorHndl &err);

int _tmain(int argc, _TCHAR* argv[])

{

parseArgs(argc, argv);

SQLDBC_Connection *conn = connectDB();

//* Create a new statment object and execute it.

SQLDBC_PreparedStatement *stmt = conn->createPreparedStatement();

// FREEZE

if (connectArgs.request.length() == 0)

connectArgs.request = "SELECT Msn, DIST_FILE FROM DBM350_AUTOTESTXML";

/* NO FREEZE

if (connectArgs.request.length() == 0)

connectArgs.request = "SELECT Msn FROM DBM350_AUTOTESTXML";

*/

char command = (char)((LPCWSTR)(connectArgs.request));

//* Executes the UNICODE encoded SQL command.

SQLDBC_Retcode ret = stmt->prepare((const char*)command, SQLDBC_StringEncodingType::UCS2Swapped);

exitOnError(stmt->error());

stmt->execute();

exitOnError(stmt->error());

// * Get the resultset.

SQLDBC_ResultSet *result;

result = stmt->getResultSet();

exitOnError(stmt->error());

// * Fetch the row from the resultset.

result->next();

SQLDBC::SQLDBC_RowSet* pRowset = result->getRowSet();

ret = pRowset->fetch();

exitOnError(result->error());

wchar_t szString[30];

SQLDBC_Length ind;

/*

• Get a string value from the column.

*/

result->getObject(1, SQLDBC_HOSTTYPE_UCS2_SWAPPED, szString, &ind, sizeof(szString)); // Retreive MSN.

// Delete de line.

// Create a new connection for delete

SQLDBC_Connection *conn2 = connectDB(g_env);

//* Create a new statment object and execute it.

SQLDBC_PreparedStatement *stmt2 = conn2->createPreparedStatement();

std::wstring st = L"DELETE FROM DBM350_AUTOTESTXML WHERE MSN=";

st += szString;

stmt2->prepare((char*)st.c_str(), SQLDBC_StringEncodingType::UCS2Swapped);

//* Executes the UNICODE encoded SQL command.

ret = stmt2->execute();

exitOnError(stmt->error());

printf("%s\n", szString);

return 0;

}

static char *convertToUCS2(char *src)

{

int len = strlen(src);

wchar_t w=(wchar_t)calloc(len, sizeof(wchar_t));

int nc = mbstowcs(w, src, len);

if ((size_t)-1 == nc) {

fprintf(stderr, "Conversion to UCS2 failed. Execution stopped.\n");

exit(1);

}

short s=(SQLDBC_Int2)calloc(nc+1, sizeof(SQLDBC_Int2));

int i;

for(i=0; i<nc; i++) {

s<i> = w<i>;

}

free(w);

return (char*)s;

}

static char* strupper(char *str)

{

char *p=str;

while(*p) {

p=toupper(p);

++p;

}

return str;

}

static wchar_t *argv0;

#ifdef WIN32

#define STRICMP _stricmp

#else

#define STRICMP strcasecmp

#endif

SQLDBC_Connection *connectDB(SQLDBC_Environment * env)

{

char errorText[200];

SQLDBC_Retcode rc;

if (g_env == NULL)

{

SQLDBC_IRuntime *runtime;

runtime = SQLDBC::GetClientRuntime(errorText, sizeof(errorText));

if (!runtime) {

fprintf(stderr, "Getting instance of the ClientRuntime failed %s\n", errorText);

usage();

}

env = new SQLDBC_Environment(runtime);

g_env = env;

}

SQLDBC_Connection *conn = env->createConnection();

printf("Connecting to '%s' on '%s' as user '%s'\n",

(char)connectArgs.dbname, (char)connectArgs.host, (char*)connectArgs.username);

rc = conn->connect(connectArgs.host, connectArgs.dbname,

connectArgs.username, connectArgs.password);

if(SQLDBC_OK != rc) {

fprintf(stderr, "Can't connect to '%s'.\nERROR: %d:'%s'\n",

connectArgs.dbname, conn->error().getErrorCode(), conn->error().getErrorText());

exit(1);

}

return conn;

}

[Former Member]

this is the same reply as previous reply, this carriage return. I hope.

Hello,

Here is code that explain my issue.

I create a table like this :

create table "ESKDBADM"."DBM350_AUTOTESTXML"( "MSN" INTEGER not null, "DIST_FILE" BLOB, constraint SYSPRIMARYKEY primary key ("MSN"))

And then I try to delete row with the following program. I do not understand why the request SELECT Msn, DIST_FILE FROM DBM350_AUTOTESTXML set a share lock on the tableid : 000080000004691D.

I do not find this tableid in the "SYSINFO"."FILES" table.

SESSION;TRANSCOUNT;PROCESS;USERNAME;DATE;TIME;TERMID;LASTWRITE;LOCKMODE;LOCKSTATE;APPLPROCESS;APPLNODE;SCHEMANAME;OWNER;TABLENAME;TABLEID;ROWIDLENGTH;ROWIDHEX;ROWID;

295697; 5220;172;ESKDBADM;2010-06-21;11:01:40;ly-delorme-u..1CDC;?;tab_share;?;7388;LY-DELORME.esker.corp;?;?;?;000080000004691D;0;?;?

// SQLDBC.cpp : Defines the entry point for the console application.

// YOU MUST CHANGE CONNECTION SETTINGS.

#include "stdafx.h"

#include <stdio.h>

#include <tchar.h>

#include <comdef.h>

#include <iostream>

#include "SQLDBC.h"

using namespace SQLDBC;

static void parseArgs(int argc, wchar_t **argv);

static SQLDBC_Connection *connectDB(SQLDBC_Environment * env = NULL);

static char* convertToUCS2(char *src);

static void usage();

SQLDBC_Environment *g_env;

typedef struct ConnectArgsT {

bstrt username;

bstrt password;

bstrt dbname;

bstrt host;

bstrt request;

} ConnectArgsT;

ConnectArgsT connectArgs;

void exitOnError(SQLDBC_ErrorHndl &err);

int _tmain(int argc, _TCHAR* argv[])

{

parseArgs(argc, argv);

SQLDBC_Connection *conn = connectDB();

//* Create a new statment object and execute it.

SQLDBC_PreparedStatement *stmt = conn->createPreparedStatement();

// FREEZE

if (connectArgs.request.length() == 0)

connectArgs.request = "SELECT Msn, DIST_FILE FROM DBM350_AUTOTESTXML";

/* NO FREEZE

if (connectArgs.request.length() == 0)

connectArgs.request = "SELECT Msn FROM DBM350_AUTOTESTXML";

*/

char command = (char)((LPCWSTR)(connectArgs.request));

//* Executes the UNICODE encoded SQL command.

SQLDBC_Retcode ret = stmt->prepare((const char*)command, SQLDBC_StringEncodingType::UCS2Swapped);

exitOnError(stmt->error());

stmt->execute();

exitOnError(stmt->error());

// * Get the resultset.

SQLDBC_ResultSet *result;

result = stmt->getResultSet();

exitOnError(stmt->error());

// * Fetch the row from the resultset.

result->next();

SQLDBC::SQLDBC_RowSet* pRowset = result->getRowSet();

ret = pRowset->fetch();

exitOnError(result->error());

wchar_t szString[30];

SQLDBC_Length ind;

/*

• Get a string value from the column.

*/

result->getObject(1, SQLDBC_HOSTTYPE_UCS2_SWAPPED, szString, &ind, sizeof(szString)); // Retreive MSN.

// Delete de line.

// Create a new connection for delete

SQLDBC_Connection *conn2 = connectDB(g_env);

//* Create a new statment object and execute it.

SQLDBC_PreparedStatement *stmt2 = conn2->createPreparedStatement();

std::wstring st = L"DELETE FROM DBM350_AUTOTESTXML WHERE MSN=";

st += szString;

stmt2->prepare((char*)st.c_str(), SQLDBC_StringEncodingType::UCS2Swapped);

//* Executes the UNICODE encoded SQL command.

ret = stmt2->execute();

exitOnError(stmt->error());

printf("%s\n", szString);

return 0;

}

static char *convertToUCS2(char *src)

{

int len = strlen(src);

wchar_t w=(wchar_t)calloc(len, sizeof(wchar_t));

int nc = mbstowcs(w, src, len);

if ((size_t)-1 == nc) {

fprintf(stderr, "Conversion to UCS2 failed. Execution stopped.\n");

exit(1);

}

short s=(SQLDBC_Int2)calloc(nc+1, sizeof(SQLDBC_Int2));

int i;

for(i=0; i<nc; i++) {

s<i> = w<i>;

}

free(w);

return (char*)s;

}

static char* strupper(char *str)

{

char *p=str;

while(*p) {

p=toupper(p);

++p;

}

return str;

}

static wchar_t *argv0;

#ifdef WIN32

#define STRICMP _stricmp

#else

#define STRICMP strcasecmp

#endif

SQLDBC_Connection *connectDB(SQLDBC_Environment * env)

{

char errorText[200];

SQLDBC_Retcode rc;

if (g_env == NULL)

{

/*

• Every application has to initialize the SQLDBC library by getting a

• reference to the ClientRuntime and calling the SQLDBC_Environment constructor.

*/

SQLDBC_IRuntime *runtime;

runtime = SQLDBC::GetClientRuntime(errorText, sizeof(errorText));

if (!runtime) {

fprintf(stderr, "Getting instance of the ClientRuntime failed %s\n", errorText);

usage();

}

env = new SQLDBC_Environment(runtime);

g_env = env;

}

/*

• Create a new connection object and open a session to the database.

*/

SQLDBC_Connection *conn = env->createConnection();

printf("Connecting to '%s' on '%s' as user '%s'\n",

(char)connectArgs.dbname, (char)connectArgs.host, (char*)connectArgs.username);

rc = conn->connect(connectArgs.host, connectArgs.dbname,

connectArgs.username, connectArgs.password);

if(SQLDBC_OK != rc) {

fprintf(stderr, "Can't connect to '%s'.\nERROR: %d:'%s'\n",

connectArgs.dbname, conn->error().getErrorCode(), conn->error().getErrorText());

exit(1);

}

return conn;

}

static void parseArgs (int argc, wchar_t **argv)

{

argv0 = wcsdup(argv[0]);

/*

• setting defaults for demo database

*/

connectArgs.username = "ESKDBADM";

connectArgs.password = "DELORME";

connectArgs.dbname = "EDP350";

connectArgs.host = "ly-delorme";

/*

• use values from command line

*/

if (argc > 5) {

connectArgs.request = argv [5];

}

if (argc > 4) {

connectArgs.host = argv [4];

}

if (argc > 3) {

connectArgs.dbname = argv [3];

}

if (argc > 2) {

connectArgs.password = argv [2];

}

if (argc > 1) {

if (!wcsicmp(argv [1], L"-h"))

usage();

else {

connectArgs.username = argv [1];

}

}

strupper(connectArgs.username);

strupper(connectArgs.password);

}

void exitOnError(SQLDBC_ErrorHndl &err)

{

if(err) {

fprintf(stderr, "Execution stopped %d:'%s'", err.getErrorCode(), err.getErrorText());

exit(1);

}

}

static void usage()

{

wchar_t *s = wcsrchr(argv0, L'/');

if (!s)

s = wcsrchr(argv0, L'
');

if (s)

*s = '\0';

printf("Syntax: %s [-h] | [<connect_options>]\n"

"\tconnect_options :: \n"

"\t\t[ <username> \n"

"\t\t[ <password>\n"

"\t\t[ <database_name>\n"

"\t\t[ <database_server> ]]]]\n"

"\t\t[ <request> ]]]]\n"

"\tCalling %s without any arguments will use user '%s','%s'\n"

"\t\ton database '%s' at server '%s'\n",

argv0, argv0,

connectArgs.username, connectArgs.password,

connectArgs.dbname, connectArgs.host);

exit(1);

}

[Former Member]

Hello,

Here is code that explain my issue.

I create a table like this :

create table "ESKDBADM"."DBM350_AUTOTESTXML"(

"MSN" INTEGER not null,

"DIST_FILE" BLOB,

constraint SYSPRIMARYKEY primary key ("MSN"))

And then I try to delete row with the following program.

I do not understand why the request SELECT Msn, DIST_FILE FROM DBM350_AUTOTESTXML set a share lock on the tableid : 000080000004691D. I do not find this tableid in the "SYSINFO"."FILES" table.

SESSION;TRANSCOUNT;PROCESS;USERNAME;DATE;TIME;TERMID;LASTWRITE;LOCKMODE;LOCKSTATE;APPLPROCESS;APPLNODE;SCHEMANAME;OWNER;TABLENAME;TABLEID;ROWIDLENGTH;ROWIDHEX;ROWID;

295697; 5220;172;ESKDBADM;2010-06-21;11:01:40;ly-delorme-u..1CDC;?;tab_share;?;7388;LY-DELORME.esker.corp;?;?;?;000080000004691D;0;?;?

// SQLDBC.cpp : Defines the entry point for the console application.

//

#include "stdafx.h"

#include "SQLDBC.h"

using namespace SQLDBC;

static void parseArgs(int argc, wchar_t **argv);

static SQLDBC_Connection *connectDB(SQLDBC_Environment * env = NULL);

static char* convertToUCS2(char *src);

static void usage();

SQLDBC_Environment *g_env;

typedef struct ConnectArgsT {

bstrt username;

bstrt password;

bstrt dbname;

bstrt host;

bstrt request;

} ConnectArgsT;

ConnectArgsT connectArgs;

void exitOnError(SQLDBC_ErrorHndl &err);

int _tmain(int argc, _TCHAR* argv[])

{

parseArgs(argc, argv);

SQLDBC_Connection *conn = connectDB();

//* Create a new statment object and execute it.

SQLDBC_PreparedStatement *stmt = conn->createPreparedStatement();

// FREEZE

if (connectArgs.request.length() == 0)

connectArgs.request = "SELECT Msn, DIST_FILE FROM DBM350_AUTOTESTXML";

/* NO FREEZE

if (connectArgs.request.length() == 0)

connectArgs.request = "SELECT Msn FROM DBM350_AUTOTESTXML";

*/

char command = (char)((LPCWSTR)(connectArgs.request));

//* Executes the UNICODE encoded SQL command.

SQLDBC_Retcode ret = stmt->prepare((const char*)command, SQLDBC_StringEncodingType::UCS2Swapped);

exitOnError(stmt->error());

stmt->execute();

exitOnError(stmt->error());

// * Get the resultset.

SQLDBC_ResultSet *result;

result = stmt->getResultSet();

exitOnError(stmt->error());

// * Fetch the row from the resultset.

result->next();

SQLDBC::SQLDBC_RowSet* pRowset = result->getRowSet();

ret = pRowset->fetch();

exitOnError(result->error());

wchar_t szString[30];

SQLDBC_Length ind;

/*

• Get a string value from the column.

*/

result->getObject(1, SQLDBC_HOSTTYPE_UCS2_SWAPPED, szString, &ind, sizeof(szString)); // Retreive MSN.

// Delete de line.

// Create a new connection for delete

SQLDBC_Connection *conn2 = connectDB(g_env);

//* Create a new statment object and execute it.

SQLDBC_PreparedStatement *stmt2 = conn2->createPreparedStatement();

std::wstring st = L"DELETE FROM DBM350_AUTOTESTXML WHERE MSN=";

st += szString;

stmt2->prepare((char*)st.c_str(), SQLDBC_StringEncodingType::UCS2Swapped);

//* Executes the UNICODE encoded SQL command.

ret = stmt2->execute();

exitOnError(stmt->error());

printf("%s\n", szString);

return 0;

}

static char *convertToUCS2(char *src)

{

int len = strlen(src);

wchar_t w=(wchar_t)calloc(len, sizeof(wchar_t));

int nc = mbstowcs(w, src, len);

if ((size_t)-1 == nc) {

fprintf(stderr, "Conversion to UCS2 failed. Execution stopped.\n");

exit(1);

}

short s=(SQLDBC_Int2)calloc(nc+1, sizeof(SQLDBC_Int2));

int i;

for(i=0; i<nc; i++) {

s<i> = w<i>;

}

free(w);

return (char*)s;

}

static char* strupper(char *str)

{

char *p=str;

while(*p) {

p=toupper(p);

++p;

}

return str;

}

static wchar_t *argv0;

#ifdef WIN32

#define STRICMP _stricmp

#else

#define STRICMP strcasecmp

#endif

SQLDBC_Connection *connectDB(SQLDBC_Environment * env)

{

char errorText[200];

SQLDBC_Retcode rc;

if (g_env == NULL)

{

/*

• Every application has to initialize the SQLDBC library by getting a

• reference to the ClientRuntime and calling the SQLDBC_Environment constructor.

*/

SQLDBC_IRuntime *runtime;

runtime = SQLDBC::GetClientRuntime(errorText, sizeof(errorText));

if (!runtime) {

fprintf(stderr, "Getting instance of the ClientRuntime failed %s\n", errorText);

usage();

}

env = new SQLDBC_Environment(runtime);

g_env = env;

}

/*

• Create a new connection object and open a session to the database.

*/

SQLDBC_Connection *conn = env->createConnection();

printf("Connecting to '%s' on '%s' as user '%s'\n",

(char)connectArgs.dbname, (char)connectArgs.host, (char*)connectArgs.username);

rc = conn->connect(connectArgs.host, connectArgs.dbname,

connectArgs.username, connectArgs.password);

if(SQLDBC_OK != rc) {

fprintf(stderr, "Can't connect to '%s'.\nERROR: %d:'%s'\n",

connectArgs.dbname, conn->error().getErrorCode(), conn->error().getErrorText());

exit(1);

}

return conn;

}

static void parseArgs (int argc, wchar_t **argv)

{

argv0 = wcsdup(argv[0]);

/*

• setting defaults for demo database

*/

connectArgs.username = "ESKDBADM";

connectArgs.password = "DELORME";

connectArgs.dbname = "EDP350";

connectArgs.host = "ly-delorme";

/*

• use values from command line

*/

if (argc > 5) {

connectArgs.request = argv [5];

}

if (argc > 4) {

connectArgs.host = argv [4];

}

if (argc > 3) {

connectArgs.dbname = argv [3];

}

if (argc > 2) {

connectArgs.password = argv [2];

}

if (argc > 1) {

if (!wcsicmp(argv [1], L"-h"))

usage();

else {

connectArgs.username = argv [1];

}

}

strupper(connectArgs.username);

strupper(connectArgs.password);

}

void exitOnError(SQLDBC_ErrorHndl &err)

{

if(err) {

fprintf(stderr, "Execution stopped %d:'%s'", err.getErrorCode(), err.getErrorText());

exit(1);

}

}

static void usage()

{

wchar_t *s = wcsrchr(argv0, L'/');

if (!s)

s = wcsrchr(argv0, L'
');

if (s)

*s = '\0';

printf("Syntax: %s [-h] | [<connect_options>]\n"

"\tconnect_options :: \n"

"\t\t[ <username> \n"

"\t\t[ <password>\n"

"\t\t[ <database_name>\n"

"\t\t[ <database_server> ]]]]\n"

"\t\t[ <request> ]]]]\n"

"\tCalling %s without any arguments will use user '%s','%s'\n"

"\t\ton database '%s' at server '%s'\n",

argv0, argv0,

connectArgs.username, connectArgs.password,

connectArgs.dbname, connectArgs.host);

exit(1);

}

[lbreddemann]

> In fact when I made select with LOB, there is the follwing line in DOMAIN.LOCKS

> SESSION;TRANSCOUNT;PROCESS;USERNAME;DATE;TIME;TERMID;LASTWRITE;LOCKMODE;LOCKSTATE;APPLPROCESS;APPLNODE;SCHEMANAME;OWNER;TABLENAME;TABLEID;ROWIDLENGTH;ROWIDHEX;ROWID;

> 295513; 4290;438;ESKDBADM;2010-06-17;12:05:46;ly-delorme-u..1674;?;tab_share;?;5748;LY-DELORME.esker.corp;?;?;?;0000800000046919;0;?;?

>

> when i send the delete request the following lock appears in DOMAIN.LOCKS. A exclusif lock on the column I want to delete. It's ok

>

> SESSION;TRANSCOUNT;PROCESS;USERNAME;DATE;TIME;TERMID;LASTWRITE;LOCKMODE;LOCKSTATE;APPLPROCESS;APPLNODE;SCHEMANAME;OWNER;TABLENAME;TABLEID;ROWIDLENGTH;ROWIDHEX;ROWID;

> 295521; 4287;167;ESKDBADM;2010-06-17;12:05:49;ly-delorme-u..1674; 5;row_exclusive;write;5748;LY-DELORME.esker.corp;ESKDBADM;ESKDBADM;DBM350_AUTOTESTXML;0000000000000934;5;

It seems that you use different sessions for the SELECT (295513) and the DELETE (295521) statements.

So this is a rather normal locking behavior.

> BUT there is a dead lock , I think because of the following entry in DOMAIN.LOCK_WAITS

> The database request a tab_exclusive lock on tableid 0000800000046918 but there is already a tab_share lock on this.

>

> Do you why there is a lock on the table : tableid 0000800000046918 ?

Why don't you check the FILES tables to figure out what's behind this tableid?

I would assume that it's not a table but a "short column file" (that's where the blobs are stored).

> How to avoid this lock using SQLDBC ?

Up to now, I cannot see your deadlock here.

One session locks a row, the other wants to delete it and waits.

The solution for that is easy: end the first transaction via rollback or commit to release the lock.

A deadlock would imply that the two sessions are waiting for each other.

This is not the case here!

Maybe if you present some coding (SQL statements, C++ code , whatever) that reproduces hte behavior.

Don't forget to include the table DDL and some test data.

regards,

Lars