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
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!
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.
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.