That will cause a classic deadlock because session 1 locks 100 and waits for 200 and session 2 will lock 200 and wait for 100. The question is whether the IN statement will lock 100 and 200 at the same time or not. If its not locking them atomic that can cause deadlock.

@DanielBjörk you just countered your comment stating " It will lock the rows as it encounters them when doing seek or scan", which is exactly what my example simulates ... locking the rows in-order/out-of-order as they are encountered

not really. Your queries will garanti that in session 1 will lock 100 and then 200 and in session 2 200 and then 100. But when you are using in statement you dont know which order you will find them. You might find 100 first or 200 first. You simply dont know.

and the NOTE at the end states the tests can be performed in the same order; together the 2 sets of examples simulate the 2 outcomes you've stated ... you don't know which order you will find the rows

Well same thing there if you have them in same order in both transactions no deadlock will occur. Your only stating the obvious. The question is whether it will lock both 100 and 200 at same time or not when using IN statement with both in it.

and now you've countered your own comment, again ... lock at the same time ... or lock at different times (ie, as row is encountered)

Well read my comment. "maybe you are right. But then"

Not surprisingly, the above code deadlocks, because there are two threads that are acquiring two separate resources in the reverse order. The question is, when you execute the single statement SELECT FOR UPDATE..WHERE..IN(list), are you effectively acquiring a single "resource" (a row set) or many resources (multiple rows)? If you are effectively acquiring a single resource (all rows in the set in a single atomic operation), then no deadlock can occur. If you're getting multiple resources (each row separately, in unknown order), deadlock can occur. Seems like the latter is the case.

@Chap: what would you expect to happen if your in(list) construct is looking for 2 rows of a really large table ... one row at the 'beginning' of the table ... one row at the 'end' of the table ... and a lengthy table scan (eg, no index, having to pull table from disk) means row #2 is found 30 minutes after row #1 is found?

@markp maybe it waits until it located all matches and take the lock at the end or it takes the lock directly. So do you know the answer to that? ;) The benefit of waiting until the scan / seek is done is that it wont hold the lock unnecciceryly long time but on the other hand if you take the lock directly you have the lock garantied.

@markp I hadn't really considered it, since the column in my example is indexed. But I see your point - clearly if they can't all be locked "quickly" they'd have to be locked separately. (Which is just my guess)

@chap, but it can wait to take the lock until the scan / seek is done and take it on all rows at one time. ;)

@chap: exactly! obviously (?) this assumes the same locking behavior regardless of table size and/or access plan (eg, with/without index); another example, even for a small table and/or index access ... your query finds row #1, is blocked by someone else updating row #6, then once row #6 block is released, you find row #2 ... 2 separate locks spread a bit (timewise) apart, or single lock?

@markp - I don't know - I'm out of my depth here. I'm surprised it's so hard to get a definitive answer. I guess that, in lieu of any statement asserting that this statement is guaranteed not to deadlock, I have to code for that possibility and retry the transaction if it occurs.

@Chap; it's never a bad idea to code/plan for deadlocks :-)

@markp - that is certainly my preference. Without clear, complete, and unambiguous documentation about what will cause a deadlock, though, it's hard to know how! :-)