42761

Oracle deadlock keeps repeating on the same record

Question:

I have a problem with the following query:

SELECT o.OID, o.DOSSIER_KEY, o.DOSSIER_TYPE FROM TVM04_VMAX_TEMP_RIGHTS WHERE o.DOSSIER_KEY = ? AND o.DOSSIER_TYPE = ? FOR UPDATE

it keeps causing a deadlock even though it is repeated more times at different intervals. If I have understood how a deadlock happens, I would expect that one of the two colliding sessions is rollbacked and the other goes forward (<a href="https://stackoverflow.com/questions/20610916/need-help-understanding-the-behaviour-of-select-for-update-causing-a-deadloc" rel="nofollow">here</a>)

I get indeed a <em>Internal Exception: java.sql.SQLException: ORA-00060: deadlock detected</em> while waiting for resource, but the probability that on our system another tx is again accessing the same record, every time I run the Query manually too, seems to me near to 0.

This is my dump Oracle file:

Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds waits TX-00400007-008c00ec 304 786 X 300 757 X TX-00070016-0002dae6 300 757 X 304 786 X session 786: DID 0001-0130-011DAD37 session 757: DID 0001-012C-00B1E41F session 757: DID 0001-012C-00B1E41F session 786: DID 0001-0130-011DAD37 Rows waited on: Session 786: obj - rowid = 0003921D - AAA5IdAAMAAHjdyAAG (dictionary objn - 234013, file - 12, block - 1980274, slot - 6) Session 757: obj - rowid = 0003921D - AAA5IdAARAACfC6AAz (dictionary objn - 234013, file - 17, block - 651450, slot - 51) ----- Information for the OTHER waiting sessions ----- Session 757: sid: 757 ser: 387 audsid: 18983600 user: 64/WLSP01 flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/- flags2: (0x40009) -/-/INC pid: 300 O/S info: user: oracle, term: UNKNOWN, ospid: 42730046 image: oracle@H50A450 client details: O/S info: user: weblogic, term: unknown, ospid: 1234 machine: H53AD20 program: JDBC Thin Client application name: JDBC Thin Client, hash value=2546894660 current SQL: SELECT o.OID, o.DOSSIER_KEY, o.DOSSIER_TYPE FROM TVM04_VMAX_TEMP_RIGHTS o WHERE o.DOSSIER_KEY = :1 AND o.DOSSIER_TY PE = :2 FOR UPDATE ----- End of information for the OTHER waiting sessions ----- Information for THIS session: ----- Current SQL Statement for this session (sql_id=489bnqugb9wsz) ----- SELECT o.OID, o.DOSSIER_KEY, o.DOSSIER_TYPE FROM TVM04_VMAX_TEMP_RIGHTS o WHERE o.DOSSIER_KEY = :1 AND o.DOSSIER_TYPE = :2 FOR UPDATE ===================================================

Have you any hint why and how it could happen?

Thank you very much in advance!

Fabio

Answer1:

I would suggest to use the <strong>SKIP LOCKED</strong> clause to avoid other sessions to fetch the rows for update which are already locked.

It will only lock the rows which it could select for update, the rest which are skipped are already locked by other session.

For example,

<strong>Session 1:</strong>

SQL> SELECT empno, deptno 2 FROM emp WHERE 3 deptno = 10 4 FOR UPDATE NOWAIT; EMPNO DEPTNO ---------- ---------- 7782 10 7839 10 7934 10 SQL>

<strong>Session 2:</strong>

SQL> SELECT empno, deptno 2 FROM emp WHERE 3 deptno in (10, 20) 4 FOR UPDATE NOWAIT; FROM emp WHERE * ERROR at line 2: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

Now let's <strong>skip the rows</strong> which are locked by session 1.

SQL> SELECT empno, deptno 2 FROM emp WHERE 3 deptno IN (10, 20) 4 FOR UPDATE SKIP LOCKED; EMPNO DEPTNO ---------- ---------- 7369 20 7566 20 7788 20 7876 20 7902 20 SQL>

So, department = 10 were locked by <strong>session 1</strong> and then department = 20 are locked by <strong>session 2</strong>.

Also, have a look at my answer <a href="https://stackoverflow.com/questions/28455231/deadlocks-in-oracle/28455397#28455397" rel="nofollow">here</a> for better understanding of deadlocks. Please read <a href="http://lalitkumarb.wordpress.com/2014/02/25/understanding-oracle-deadlock/" rel="nofollow">Understanding Oracle Deadlock</a>.

Recommend

  • What is the difference between running in VS 2010 and running a builded EXE?
  • How do I read and write repeatedly from a process in vim?
  • Problem with sockets in C#
  • mysql table locked after php crashes
  • NSMutableArray Access Issue
  • Timing loops with asynchronous functions
  • In C what exactly happens if i use () to initialize a double dimension array instead of the {}?
  • How can I extract results of aggregate queries in slick?
  • Xcode 4 NSLog Macro link in Xcode 3
  • How to match http request and response using Jersey ContainerRequestFilter and ContainerResponseFilt
  • How to render a blob on a canvas element?
  • azure media services - The request body is too large and exceeds the maximum permissible limit
  • How to set ini file attributes during an Inno install
  • Play WS (2.2.1): post/put large request
  • Setting up SourceTree to merge unity3d scenes with UnityYAMLMerge
  • Why does access(2) check for real and not effective UID?
  • How to create a file in java without a extension
  • Typescript - Unable to get 'import' statement to function
  • Spark fat jar to run multiple versions on YARN
  • Extracting HTML between tags
  • Python CGI os.system causing malformed header
  • Ajax Loaded meta Tags
  • Debugging ASP.NET on a built-in web server suddenly stops
  • Exchange data b/w iOS devices using Bluetooth 4.0
  • Master page gives error
  • Uncaught Error: Could not find module `ember-load-initializers`
  • Apache 2.4 and php-fpm does not trigger apache http basic auth for php pages
  • How to set/get protobuf's extension field in Go?
  • DirectX11 ClearRenderTargetViewback with transparent buffer?
  • Sending data from AppleScript to FileMaker records
  • Running a C# exe file
  • Symfony2: How to get request parameter
  • Google cloud sdk not working when python points python3
  • Why winpcap requires both .lib and .dll to run?
  • Run Powershell script from inside other Powershell script with dynamic redirection to file
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • Load html files in TinyMce
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • coudnt use logback because of log4j
  • How to get NHibernate ISession to cache entity not retrieved by primary key