Deadlock or Timeout when delete/update rows with BLOB

[Ray]

I am having my first experience using BLOB as a row in a table. I am
using it to insert graphics for labels we print. I have no problem
inserting into and select from the table. The graphic is being stored
correctly.

Whenever I attempt to delete a row from my application I get the
following error.
SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "68". SQLSTATE=40001

I know the row is not locked as only I have access to this development
database.

Also when I attempt to update a row within the table (even to jsut
alter the NAME row that is only characters) the update always fails but
my application will return true as if the update was a success. When I
check the row via the CLP the old name is still there.

Am I missing something with working with BLOBs?
Thanks,
Ray

[Mark A]

"Ray" <br****@lsmp.co m> wrote in message
news:11******** *************@g 49g2000cwa.goog legroups.com...

I am having my first experience using BLOB as a row in a table. I am
using it to insert graphics for labels we print. I have no problem
inserting into and select from the table. The graphic is being stored
correctly.

Whenever I attempt to delete a row from my application I get the
following error.
SQL0911N The current transaction has been rolled back because of a
deadlock or timeout. Reason code "68". SQLSTATE=40001

I know the row is not locked as only I have access to this development
database.

Also when I attempt to update a row within the table (even to jsut
alter the NAME row that is only characters) the update always fails but
my application will return true as if the update was a success. When I
check the row via the CLP the old name is still there.

Am I missing something with working with BLOBs?
Thanks,
Ray

Do a:
db2 list applications show detail
to see if there are other connections.
Do a snapshot before locks right before and right after the update. If you
cannot figure it out, post the results of the above on this forum.

[gimme_this_gimme_that]

This is crude and I don't recommend it and I know little about DB2.
That being said ...

You could try updating the record with a an empty BLOB and then
deleting it.

[Ray]

I did the snapshot for the row locking and something strange is
happening. It looks like the row is being locked but after the update
the lock is lost and the commit is doing nothing.
For all my other tables I get the following:
After the lock but before the update I have two locks on the table (U,
IX)
After the update but before the commit I have two locks (X,IX)
After the commit all locks are released. With this table and the BLOB I
am getting distinctly different results. I can post a log of a basic
lock,update, commit for a table I know works if that would help.

Here is a snapshot right after I selected the row for update on the
BLOB table. At this point it is locked and not updated.
---------BEGIN SNAPSHOT
Database Lock Snapshot

Database name = LSMPDEV

Database path = C:\DB2\NODE0000 \SQL00003\

Input database alias = LSMPDEV

Locks held = 18
Applications currently connected = 3
Agents currently waiting on locks = 0
Snapshot timestamp = 05-12-2005 09:43:46.855278

Application handle = 265
Application ID = C0A80178.870B.0 50512140954
Sequence number = 0001
Application name = db2bp.exe
Authorization ID = ADMINISTRATOR
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 0
Total wait time (ms) = Not Collected
Application handle = 63
Application ID = C0A80178.4D0C.0 50512144543
Sequence number = 0001
Application name = omnis.exe
Authorization ID = RAY
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 9
Total wait time (ms) = Not Collected

List Of Locks
Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 4
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = ADMINISTRATOR
Table Name = LABEL_GRAPHIC
Mode = NS
Status = Granted
Lock Escalation = NO

Lock Object Name = 121
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = ADMINISTRATOR
Table Name = LABEL_GRAPHIC
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO
Application handle = 281
Application ID = C0A80178.4F0C.0 50512144545
Sequence number = 0001
Application name = omnis.exe
Authorization ID = RAY
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 9
Total wait time (ms) = Not Collected

List Of Locks
Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 4
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = ADMINISTRATOR
Table Name = LABEL_GRAPHIC
Mode = U
Status = Granted
Lock Escalation = NO

Lock Object Name = 121
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = ADMINISTRATOR
Table Name = LABEL_GRAPHIC
Mode = IX
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO
--------END SNAPSHOT

And here is the snapshot after I have updated but before a commit has
been issued.
As you can see the locks have been lost for the application ID even
though a commit has not been issued. I am lost.
----BEGIN SNAPSHOT

Database Lock Snapshot

Database name = LSMPDEV
Database path = C:\DB2\NODE0000 \SQL00003\
Input database alias = LSMPDEV
Locks held = 9
Applications currently connected = 3
Agents currently waiting on locks = 0
Snapshot timestamp = 05-12-2005 09:43:58.549061

Application handle = 265
Application ID = C0A80178.870B.0 50512140954
Sequence number = 0001
Application name = db2bp.exe
Authorization ID = ADMINISTRATOR
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 0
Total wait time (ms) = Not Collected
Application handle = 63
Application ID = C0A80178.4D0C.0 50512144543
Sequence number = 0001
Application name = omnis.exe
Authorization ID = RAY
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 9
Total wait time (ms) = Not Collected

List Of Locks
Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal L Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 4
Node number lock is held at = 0
Object Type = Row
Tablespace Name = USERSPACE1
Table Schema = ADMINISTRATOR
Table Name = LABEL_GRAPHIC
Mode = NS
Status = Granted
Lock Escalation = NO

Lock Object Name = 121
Node number lock is held at = 0
Object Type = Table
Tablespace Name = USERSPACE1
Table Schema = ADMINISTRATOR
Table Name = LABEL_GRAPHIC
Mode = IS
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal V Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO

Lock Object Name = 0
Node number lock is held at = 0
Object Type = Internal P Lock
Tablespace Name =
Table Schema =
Table Name =
Mode = S
Status = Granted
Lock Escalation = NO
Application handle = 281
Application ID = C0A80178.4F0C.0 50512144545
Sequence number = 0001
Application name = omnis.exe
Authorization ID = RAY
Application status = UOW Waiting
Status change time = Not Collected
Application code page = 1252
Locks held = 0
Total wait time (ms) = Not Collected

---END SNAPSHOT.