Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jpegu
Starting Member
1 Post |
 Posted - 2013-10-17 : 17:05:58
|
| Hi,The table:TAB1(ID, TARGET, STATE, NEXT). Column ID is PRIMARY KEY.The Query is that is showing deadlock:SELECT * FROM TAB1 WHERE NEXT = (SELECT MIN(NEXT) FROM TAB1 WHERE TARGET=? AND STATE=?) FOR UPDATEI did an explain plan I see something like this:---------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 8095 | 6 (0)| 00:00:01 || 1 | FOR UPDATE | | | | | || 2 | BUFFER SORT | | | | | ||* 3 | TABLE ACCESS FULL | TAB1 | 1 | 8095 | 3 (0)| 00:00:01 || 4 | SORT AGGREGATE | | 1 | 2083 | | ||* 5 | TABLE ACCESS FULL| TAB1 | 1 | 2083 | 3 (0)| 00:00:01 |---------------------------------------------------------------------------------------Since the query is doing TABLE ACCESS FULL twice, so I'm suspecting 2 session executing the same query will access the rows in different orders. Can indexing of columns will help in preventing the deadlock? Say creating an index on NEXT??? Note: Normally, the table will have max 1000 rows. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-10-17 : 18:18:21
|
| Why are you using FOR UPDATE? You can remove that unless you are trying to block other processes from modifying that data while you select it, but I'm not sure if that is your goal. |
 |
|
|
ScottPletcher
Aged Yak Warrior
550 Posts |
Posted - 2013-10-17 : 18:36:19
|
| Doesn't look like SQL Server, unless perhaps it is a cursor declaration.What DBMS are you working in? |
|
|
|
|
|
|
Deadlock in SELECT FOR UPDATE query