Updating Blob columns and getting deadlocks

harby ariza

Updating Blob columns and getting deadlocks
_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________




Hi Guys,

I just wondering if there is any special consideration to take into account when performing updates against a BLOB columns. I have a developer who is basically getting deadlocks while trying to update the BLOB column and even the update statement is very straight forward. Just in case the update looks like :

UPDATE TB_TRANS_INBOUNDMSG SET DTE_UPDATED = ?, CDE_STATUS = ?, BIN_JAVAOBJ = ? WHERE NUM_RECORDID = ?

And the NUM_RECORDID is part of the primary key so it has an unique index and the blob column is BIN_JAVAOBJ. Any comments will be highly appreciated. Thanks.



_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in reliance
upon this information, by persons or entities other than the intended recipient is
prohibited.

If you have received this in error, please contact the sender and delete this e-mail
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute
the information contained in this e-mail and any attached files, with the permission
of the sender.

This message has been scanned for viruses with Symantec Scan Engine and cleared by
MailMarshal.
_______________________________________________________________________________________

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Myron Miller

Re: Updating Blob columns and getting deadlocks
(in response to harby ariza)
Remember that BLOB locking is totally different from regular locking. It's extremely easy to get into full blob tablespace locks and conflicts with BLOBs. There's complete doc in this in the V8 and V9 manuals as well as the DB2 V9 Perfomance redbook.

I suspect that you're running into issues with blob lock escalation.




________________________________
From: Harbry Ariza <[login to unmask email]>
To: [login to unmask email]
Sent: Wednesday, December 17, 2008 8:47:42 PM
Subject: [DB2-L] Updating Blob columns and getting deadlocks

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________




Hi Guys,

I just wondering if there is any special consideration to take into account when performing updates against a BLOB columns. I have a developer who is basically getting deadlocks while trying to update the BLOB column and even the update statement is very straight forward. Just in case the update looks like :

UPDATE TB_TRANS_INBOUNDMSG SET DTE_UPDATED = ?, CDE_STATUS = ?, BIN_JAVAOBJ = ? WHERE NUM_RECORDID = ?

And the NUM_RECORDID is part of the primary key so it has an unique index and the blob column is BIN_JAVAOBJ. Any comments will be highly appreciated. Thanks.



_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in reliance
upon this information, by persons or entities other than the intended recipient is
prohibited.

If you have received this in error, please contact the sender and delete this e-mail
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute
the information contained in this e-mail and any attached files, with the permission
of the sender.

This message has been scanned for viruses with Symantec Scan Engine and cleared by
MailMarshal.
_______________________________________________________________________________________

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms