title: Deadlock in SELECT FOR UPDATE query
body: <p>I have table say </p>
  
  <p><strong>TAB1</strong></p>
  
  <pre><code>ID, TARGET, STATE, NEXT
  </code></pre>
  
  <p>Column <code>ID</code> is the primary key.</p>
  
  <p>The query is that is showing deadlock is similar to this</p>
  
  <pre><code>SELECT * 
  FROM TAB1 
  WHERE NEXT = (SELECT MIN(NEXT) FROM TAB1 WHERE TARGET=? AND STATE=?) FOR UPDATE
  </code></pre>
  
  <p>I did an explain plan I see something like this:</p>
  
  <pre><code>| 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 |
  </code></pre>
  
  <p>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. </p>
  
  <p>Can indexing of columns will help in preventing the deadlock? Say creating an index on NEXT??? Or by changing the PRIMARY to NON CLUSTERED KEY?? Note: Normally, the table will have max 1000 rows.</p>