TT6002 deadlock errors between SELECT FOR UPDATES and UPDATES or INSERTS (Doc ID 1919731.1)

Last updated on JUNE 15, 2016

Applies to:

Oracle TimesTen In-Memory Database - Version 11.2.2.6.6 and later
Information in this document applies to any platform.

Symptoms

It was observed that many TT6002 deadlock errors were being reported by applications.  This happened between two types of SQL statement because they are following different sequence while acquiring a lock.  

Example deadlock  error messages:

TT6002: Lock request denied because of deadlock

Details: Tran 923.12487 (pid 15493) wants Un lock on rowid BMUFVUAAAC7qAAAPjj, table ME.MYVIEW. But tran 51.56546 (pid 3251) has it in S (request was S). Holder SQL (update xvalue set xvalue= nvl(xvalue, 0) + ?,       xvalue= n...) -- file "tindex.c", lineno 4849, procedure "sbTixNext()"

or 

TT6002: Lock request denied because of deadlock

Details: Tran 277.5432 (pid 7878) wants X lock on hash value 36099506, table ME.MYVIEW. But tran 342.8246 (pid 7840) has it in SF
(request was SF). Holder SQL (insert into BASE (a, b, c, d, e...)
-- file "hindex.c", lineno 2026, procedure "sbHixGetCursorFirstFetchLocks()"

 

Basic description:

Connection 1: SELECT FOR UPDATE
acquires row lock then hash lock


Connection 2: UPDATE or INSERT
acquires hash lock then row lock


In this case deadlock occurred because two connections are trying to update the same row but executing different set of queries that caused the lock applied in different order. One connection executed select for update first that caused the row lock to be acquired first. The second update directly and that caused the hash lock to be acquired before the row lock. Deadlock occurred when the first connection tried to acquire hash lock and the second one tried to acquire row lock.



Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms