You should take a look at RCSI (read committed snapshot isolation) - if that is not possible then consider enabling snapshot isolation and using that for the select statements.

I also looks like you have set table hits to force updlock and rowlock on the select statements. Why are you using these lock hints? This will force each select statement to issue those locks which will block all other statements.

Thanks for response.
updlock and rowlock are used for "select for update" scenario. Row is locked to prevent other transactions to edit it and in the same transaction, the row is updated.
Sorry for not mentioning this in my post.
Could RCSI help in this case where locking is intentional?

Also the table is attacked with many queries of type SELECT * FROM datatable WHERE id = "foo"
without explicit transaction or any locking hint. So I think this does not affect the cause of deadlock in any way as it always happens between the UPDATE and SELECT with rowlocking. However I can't prove it. Do you have the opposite opinion?

No we don't use any stored procedures.
Also there is no reporting on this table. There are some other queries where we use the same table. These are something like what you mentioned - if you meant queries on non-primary key column (SELECT * FROM datatable WHERE os = "foo" and deviceid = "bar" and deactivated_date = "baz"), but they run very rarely, like once a week maybe even less often.

Thanks for response.
updlock and rowlock are used for "select for update" scenario. Row is locked to prevent other transactions to edit it and in the same transaction, the row is updated.
Sorry for not mentioning this in my post.
Could RCSI help in this case where locking is intentional?

Also the table is attacked with many queries of type SELECT * FROM datatable WHERE id = "foo"
without explicit transaction or any locking hint. So I think this does not affect the cause of deadlock in any way as it always happens between the UPDATE and SELECT with rowlocking. However I can't prove it. Do you have the opposite opinion?

Are these select statements part of an update - or are they separate statements? Based on what you have shown - these select statements are not part of the update which is why you are getting deadlocks.

This 'select for update' - what exactly are you doing here? Are you selecting a row - and then in a separate statement updating the row?

SELECT ... FROM datatable WHERE ...;
UPDATE datatable SET ...

Or - are they part of the update?

UPDATE datatable
SET column = (SELECT ... FROM datatable WHERE ...);

RCSI could help - since your select statements would then be reading from the version store the updates won't be blocked. You wouldn't have to worry about 'select for update' - because any other in-flight transactions would not be available at that time.

However - if you are expecting the select statements to wait until another processes update has completed then you have a whole different problem.

This 'select for update' - what exactly are you doing here? Are you selecting a row - and then in a separate statement updating the row?

SELECT ... FROM datatable WHERE ...;
UPDATE datatable SET ...

Yes, this is our case.
Those multiple selects - They are not part of an update, they are executed from another application server before actual select for update scenario happens.
It is always something like:
from first server: select, select
from second server: start of explicit transaction select with rowlock, update, end of transaction
from first server: select

However - if you are expecting the select statements to wait until another processes update has completed then you have a whole different problem.

No, we dont expect concurrent reading/modification on the same row. That is why we are so confused about this whole thing.

They just access the same database and in one of them, just a subset of columns/properties is read.
When user requests the first one, it executes mentioned select queries and calls the other one, which should access the same row and possibly update it, while on the first one there are no explicit transactions, so it should not be blocking the second one. Its all synchronous, no parallelism.

This 'select for update' - what exactly are you doing here? Are you selecting a row - and then in a separate statement updating the row?

SELECT ... FROM datatable WHERE ...;
UPDATE datatable SET ...

Yes, this is our case.
Those multiple selects - They are not part of an update, they are executed from another application server before actual select for update scenario happens.
It is always something like:
from first server: select, select
from second server: start of explicit transaction select with rowlock, update, end of transaction
from first server: select

However - if you are expecting the select statements to wait until another processes update has completed then you have a whole different problem.

No, we dont expect concurrent reading/modification on the same row. That is why we are so confused about this whole thing.

Very confusing...

There is no reason to issue a SELECT with an update and row lock...why are you doing this? The SELECT with an update and row lock will block all other processes until it is completed - especially the UPDATEs.

It is not a select with update and rowlock in the same query.
It is:
select with rowlock (to prevent anything from changing the row)#server application computing data to save
update of locked row (with computed data)
I think it is very common scenario.

It is not a select with update and rowlock in the same query.
It is:
select with rowlock (to prevent anything from changing the row)#server application computing data to save
update of locked row (with computed data)
I think it is very common scenario

This is not how locking works in SQL Server. The SELECT does not lock the row and hold that lock until after you issue the UPDATE. As soon as the SELECT statement is completed - the lock is released unless you are performing this process in an explicit transaction...

This sounds like application locks - which is a completely different process.