I'm currently having troubles with frequent deadlocks with a specific user table in MS SQL 2008. Here are some facts about this particular table:

1. Has a large amount of rows (1 to 2 million)
2. All the indexes used on this table only has "use row lock" ticked on its option

3. rows are frequently updated by multiple transactions but are unique (e.g. probably a thousand or more update statements are executed to different unique rows every hour)

4. the table does not use partitions.

Upon checking the table on sys.tables, I found that the lock_escalation is set to TABLE

I'm very tempted to turn the lock_escalation for this table to DISABLE but I'm not really sure what side effect this would incur. From What I understand, using DISABLE will minimize escalating locks to TABLE level which if combined with the row lock settings of the indexes should theoretically minimize the deadlocks I am encountering..

From what I have read in Determining threshold for lock escalation it seems that locking automatically escalates when a single transaction fetches 5000 rows..

What does a single transaction mean in this sense? A single session/connection getting 5000 rows thru individual update/select statements?

Or is it a single sql update/select statement that fetches 5000 or more rows?

Any insight is appreciated, btw, n00b DBA here

Thanks