DB2 SELECT causing Deadlock!!!!

[Sushil Singh]

Hi,

I was wondering how a SELECT query can cause a deadlock. Here is the
error which I am getting sometime:
QL0911N The current transaction has been rolled back because of a
deadlock
or timeout. Reason code "2". SQLSTATE=40001

And the SQL is:
userQuery="export to $outputFileName of del messages $errorFileName
select b.first_name, b.last_name, g.migrated, e.address_line_1,
e.address_line_2, e.city, e.state_abbrev, f.state_name, e.postal_code,
a.email_address, b.RECEIVE_NEWSLETTER from user_profile b, user a LEFT
OUTER JOIN user_contact c on a.user_id = c.user_id and
c.contact_seq_nbr <= 1 LEFT OUTER JOIN contact d ON c.contact_id =
d.contact_id LEFT OUTER JOIN address e ON d.address_id = e.address_id
LEFT OUTER JOIN state f ON e.state_abbrev = f.state_abbrev LEFT OUTER
JOIN user_migration_master g on a.email_address = g.email and
g.migrated = 'Y' where a.user_id = b.user_id and a.email_address is
not null and a.created >= timestamp('$currDate7Days 00:00:00.000') and
a.created < timestamp('$currDateGMT 00:00:00.000') and
b.receive_newsletter = 'Y' order by b.first_name"

Thanks in advance!

Sushil Singh

[Miro Flasza]

If you're using read stability or repeatable read isolation level,
then the select statement won't release the locks on the previously
read rows until commit (see the admin guide for the explanation of
different isolation level semantics). If you have another transaction
performing concurrent updates on the same table, then this can lead
to a deadlock. Example:

app 1 (select) locks row #1 for read
app 2 (update) locks row #2 for update
app 1 (select) attempts to lock row #2 for read - waits
app 2 (update) attempts to lock row #1 for update - waits
-> deadlock detector detects a deadlock and terminates app 1

Note that if app 1 was using isolation level cursor stability then
it would have released the lock on row #1 before attempting to get
the lock on row #2 and would avoid the deadlock in the example above.
You should read up on the meaning of the different isolation levels
and decide for yourself whether cursor stability is sufficient in
your case. If you want to determine the exact resource the deadlock
is occurring on and the applications involved in it, use the db2 event
monitor for deadlocks.

Regards,
Miro

[Sushil Singh]

Hi Miro:

Thanks for your reply.

Now I understand the various isolation level. But I am not sure how
to determine which isolation level I am using.
- I executed my query using DB2 command line.
Is there any way to determine the isolation level and how can i
specify a isolation level.

Thanks in advance.

Sushil Singh

Miro Flasza <miro....@sympatico.ca> wrote in message news:<3C16A981...@sympatico.ca>...

[Fan Ruo Xin]

db2 terminate
db2 change isolation to ur (or cs, rs, rr)
db2 connect to sample

[bea...@gmail.com]

HI, I have the same error message when rename and create table, "QL0911N The current transaction has been rolled back because of a deadlock or timeout. Reason code "2". SQLSTATE=40001". , we use event monitor, and get error message, below:

Deadlock statement history ...
Deadlock ID : 0
Deadlock node : 0
Participant No : 0
Application id : *N0.biyjinst.181019080901
Stmt history ID : 2
Type : Dynamic
Section No : 384
Package cache id : 17983093080064
Package creator : NULLID
Package name : SYSLH202
Package version :
Lock timeout value : 5
Nesting level of stmt : 1
Invocation ID : 1
Query ID : 281474976710657
Source ID : 0
UOW Sequence number : 0003
Isolation level : Cursor Stability
Stmt first use time : 10/19/2018 16:08:27.006461
Stmt last use time : 10/19/2018 16:08:27.006461
Statement text : SELECT TABNAME FROM SYSCAT.TABLES WHERE TABNAME='HMON_ATM_INFO' AND TABSCHEMA='SYSTOOLS'.

seems that is question about system view?

Thanks!

在 2001年12月12日星期三 UTC+8上午3:23:01，Sushil Singh写道：