mysql子查询更新死锁_使用子查询选择更新导致死锁

I have query when executed from different session is resulting in deadlock.

TAB1 (ID, TARGET, STATE, NEXT) AND ID is primary key

Column ID is the primary key.

SELECT *

FROM

TAB1 WHERE NEXT = (SELECT MIN(NEXT) FROM TAB1 WHERE TARGET=? AND STATE=?)

AND TARGET=? AND STATE=? FOR UPDATE

In the Oracle trace file, I see the statement:

DEADLOCK DETECTED

Current SQL statement for this session:

SELECT ID, TARGET, NEXT, STATE FROM TAB1

WHERE NEXT=(SELECT MIN(NEXT) FROM TAB1 WHERE (TARGET='$any') AND ( STATE = 0))

AND (TARGET='$any')

AND (STATE = 0) FOR UPDATE

The following deadlock is not an ORACLE error. It is a

deadlock due to user error in the design of an application

or from issuing incorrect ad-hoc SQL. The following

information may aid in determining the deadlock:

Deadlock graph:

---------Blocker(s)-------- ---------Waiter(s)---------

Resource Name process session holds waits process session holds waits

TX-00010012-0102905b 54 474 X 52 256 X

TX-000a0005-00a30961 52 256 X 54 474 X

session 474: DID 0001-0036-00000002 session 256: DID 0001-0034-00000002

session 256: DID 0001-0034-00000002 session 474: DID 0001-0036-00000002

Rows waited on:

Session 256: obj - rowid = 00013181 - AAATGBAAzAABtPTAAI

(dictionary objn - 78209, file - 51, block - 447443, slot - 8)

Session 474: obj - rowid = 00013181 - AAATGBAAzAABtPUAAJ

(dictionary objn - 78209, file - 51, block - 447444, slot - 9)

Information on the OTHER waiting sessions:

Session 256:

pid=52 serial=58842 audsid=43375302 user: 106/B2B_ISINTERNAL

O/S info: user: admwmt, term: spl099wmt04.compucom.local, ospid: , machine: spl099wmt04.compucom.local/10.16.0.41

program: JDBC Connect Client

Current SQL Statement:

SELECT ID, TARGET, NEXT, STATE FROM TAB1

WHERE NEXT=(SELECT MIN(NEXT) FROM TAB1 WHERE (TARGET='$any') AND ( STATE = 0))

AND (TARGET='$any')

AND (STATE = 0) FOR UPDATE

End of information on OTHER waiting sessions.

===================================================

Is there any way to avoid this? Rewriting the Query or Indexing?

解决方案

Update

Instead of trying to guess exactly how Oracle retrieves rows and force a plan, it may be easier to use one of the available UPDATE FOR locking features.

NOWAIT or SKIP LOCKED should be able to fix the problem. Although with NOWAIT you would probably need to add some application logic to retry after an error.

Since there are bind variables there may be multiple execution plans for the same SQL statement. This is normally a good thing, for example think about a query like this: select * from tab where status = ?. A full table scan would work best for a popular status, and an index scan would work better for a rare status. But if one plan uses an index and one uses a table, the same statement will retrieve resources in a different order, potentially causing a deadlock.

Forcing the statement to always use the same plan will prevent the deadlocks.

First, you will want to confirm my theory about multiple execution plans is correct. Look for multiple rows in this query, specifically look for different plan_hash_values for the same SQL_ID.

select child_number, plan_hash_value, gv$sql.*

from gv$sql

where sql_text like '%NEXT=(SELECT%';

Then it's a matter of forcing the statements to always use the same plan. One simple way is to find the outline that fixes a specific plan, and use the same set of hints for both statements. Hopefully the forced plan will still run well for all sets of bind variables.

select *

from table(dbms_xplan.display_cursor(

sql_id => '',

cursor_child_no => ,

format => '+outline')

);