This mornings fiasco was interesting.
For no obvious reason, the number of deadlocks on the server increased, soon it was every other statement was throwing up a deadlock, and knocking off the victim. All the update and delete statements specified one or two rows only, but for some reason SQL Server was escalating what should have been a ROWLOCK into a PAGE or TABLE lock. I imagine this was decided upon by the optimizer because the the rows it was going to update existed in the same page and so it was more efficient to obtain a PAGE LOCK, though in a multiuser environment it seem this was fatal for the least expensive query, as it ended up the vitim and was killed. To overcome this the table hints WITH(ROWLOCK) ensured SQL Server chose an rowlock for each statement. Its not often a 2 users are going to want to update the same record, though with data ordered by date its reasonable to expect it to exist in the same page as another users data. So taking a page lock out on that prevents all users doing their own stuff to any record also located on that page. The same would apply for extents aswell.
So..
UPDATE card_deck
SET spenthand = 6
WHERE dealer_id = @dealerid
Became..
UPDATE card_deck WITH (ROWLOCK)
SET spenthand = 6
WHERE dealer_id = @dealerid
But if you want to have more control over what SQL Server decides is the deadlock victim, you can add the following to your sotred procedures for the lower priority tasks
SET DEADLOCK_PRIORITY LOW
This tells the optimzer that the current session is the preferred deadlock victim.
As a side note trace flag 1204 and 1205 will help identify which tables are causing deadlocks, but turn these flags off when you are done as they are heavy