How to avoid deadlocks when selecting from joined tables?

Question

I have two very simple tables, let's call them [UserData1] and [UserData2]. They both have [UserId] column as a primary key. I am running two types of queries against these two tables. One is a SELECT statement that returns combined data for a particular user:

SELECT <a subset of columns from both tables>
FROM [UserData1] ud1
    FULL OUTER JOIN [UserData2] ud2 ON ud1.[UserId] = ud2.[UserId]
WHERE
    ud1.[UserId] = @UserId OR ud2.[UserId] = @UserId

The other is a transaction that updates user data in both tables for a particular user:

BEGIN TRANSACTION

UPDATE [UserData1]
SET <new values>
WHERE [UserId] = @UserId

UPDATE [UserData2]
SET <new values>
WHERE [UserId] = @UserId

COMMIT TRANSACTION

The problem here is the order of acquiring shared table locks in the SELECT statement is undetermined, which may (and actually does) lead to a classical deadlock situation if SQL Server decides to lock [UserData2] before [UserData1]. What would be the best way to avoid deadlocks in this case?

Merge these tables into one table, right? I wish it was that easy. Suppose there is a reason to keep them separate.

READ UNCOMMITTED / NOLOCK hint? Suppose dirty reads cannot be tolerated.

SNAPSHOT isolation level? This would solve the problem, but I am not sure about the overhead involved.

So the question boils down to: is there a way to guarantee the order in which locks on joined tables are acquired?

At first I thought this could be achieved with FORCE ORDER query hint, but then I found out by experiment that it doesn't necessarily enforce the order in which the tables are locked. Another solution in this particular case would be to issue separate SELECT queries for each table and then combine two one-row recordsets in the application layer, but in case I ever need to make a query for multiple users, I would still prefer to get all results in one recordset.

UPDATE:

This is the excerpt from deadlock trace:

   Deadlock encountered .... Printing deadlock information
   Wait-for graph

   Node:1
   KEY: 17:72057594039173120 (e21762ccf3dc) CleanCnt:3 Mode:X Flags: 0x1
    Grant List 1:
      Owner:0x00000020F75B0480 Mode: X        Flg:0x40 Ref:0 Life:02000000 SPID:72 ECID:0 XactLockInfo: 0x00000020EB13ED68
      SPID: 72 ECID: 0 Statement Type: UPDATE Line #: 1
      Input Buf: Language Event: (@UserId bigint,@DataColumn2 int)update
   Requested by: 
     ResType:LockOwner Stype:'OR'Xdes:0x00000020FC98DA40 Mode: S SPID:75 BatchID:0 ECID:0 TaskProxy:(0x00000020DAB38608) Value:0xf75abbc0 Cost:(0/0)

   Node:2
   KEY: 17:72057594039107584 (e21762ccf3dc) CleanCnt:9 Mode:S Flags: 0x1
    Grant List 1:
      Owner:0x00000020EEBFE580 Mode: S        Flg:0x40 Ref:1 Life:00000000 SPID:75 ECID:0 XactLockInfo: 0x00000020FC98DA80
      SPID: 75 ECID: 0 Statement Type: SELECT Line #: 1
      Input Buf: Language Event: (@UserId bigint)select [t].[UserId], t.[DataColumn2], t1.[DataColumn1]
   Requested by: 
     ResType:LockOwner Stype:'OR'Xdes:0x00000020EB13ED28 Mode: X SPID:72 BatchID:0 ECID:0 TaskProxy:(0x0000001F671C6608) Value:0xf75b5400 Cost:(0/456)

   Victim Resource Owner:
    ResType:LockOwner Stype:'OR'Xdes:0x00000020FC98DA40 Mode: S SPID:75 BatchID:0 ECID:0 TaskProxy:(0x00000020DAB38608) Value:0xf75abbc0 Cost:(0/0)
  deadlock-list
   deadlock victim=process20fda2ccf8
    process-list
     process id=process20fda2ccf8 taskpriority=0 logused=0 waitresource=KEY: 17:72057594039173120 (e21762ccf3dc) waittime=4526 ownerId=3416711 transactionname=SELECT lasttranstarted=2013-07-11T18:42:20.943 XDES=0x20fc98da40 lockMode=S schedulerid=20 kpid=2800 status=suspended spid=75 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2013-07-11T18:42:20.950 lastbatchcompleted=2013-07-11T18:42:20.950 lastattention=1900-01-01T00:00:00.950 clientapp=.Net SqlClient Data Provider hostname=hostname hostpid=27716 loginname=loginname isolationlevel=read committed (2) xactid=3416711 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
      executionStack
       frame procname=adhoc line=1 stmtstart=36 sqlhandle=0x020000001fcbbe1423a0c65cc8411344c6040e879195af3a0000000000000000000000000000000000000000
  select [t].[UserId], t.[DataColumn2], t1.[DataColumn1] from [UserData1] t1 full outer join [UserData2] t on t1.[UserId]=t.[UserId] where t.[UserId]=@UserId or t1.[UserId]=@UserId option (force order)     
       frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  unknown     
      inputbuf
  (@UserId bigint)select [t].[UserId], t.[DataColumn2], t1.[DataColumn1] from [UserData1] t1 full outer join [UserData2] t on t1.[UserId]=t.[UserId] where t.[UserId]=@UserId or t1.[UserId]=@UserId option (force order)    
     process id=process20fd055498 taskpriority=0 logused=456 waitresource=KEY: 17:72057594039107584 (e21762ccf3dc) waittime=4525 ownerId=3416764 transactionname=user_transaction lasttranstarted=2013-07-11T18:42:20.960 XDES=0x20eb13ed28 lockMode=X schedulerid=9 kpid=6024 status=suspended spid=72 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2013-07-11T18:42:20.970 lastbatchcompleted=2013-07-11T18:42:20.970 lastattention=1900-01-01T00:00:00.970 clientapp=.Net SqlClient Data Provider hostname=hostname hostpid=27716 loginname=loginname isolationlevel=read committed (2) xactid=3416764 currentdb=17 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
      executionStack
       frame procname=adhoc line=1 stmtstart=508 sqlhandle=0x02000000c0d74a32597ec460559a2d5dbdc92f7746cdce270000000000000000000000000000000000000000
  update UserData2 set [LastModified]=getutcdate(), [DataColumn2]=[DataColumn2]+@DataColumn2Increment where [UserId]=@UserId     
       frame procname=unknown line=1 sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
  unknown     
      inputbuf
  (@UserId bigint,@DataColumn2Increment int)update UserData2 set [LastModified]=getutcdate(), [DataColumn2]=[DataColumn2]+@DataColumn2Increment where [UserId]=@UserId    
    resource-list
     keylock hobtid=72057594039173120 dbid=17 objectname=database_name.dbo.UserData1 indexname=1 id=lock20ec75b380 mode=X associatedObjectId=72057594039173120
      owner-list
       owner id=process20fd055498 mode=X
      waiter-list
       waiter id=process20fda2ccf8 mode=S requestType=wait
     keylock hobtid=72057594039107584 dbid=17 objectname=database_name.dbo.UserData2 indexname=1 id=lock20ec07f600 mode=S associatedObjectId=72057594039107584
      owner-list
       owner id=process20fda2ccf8 mode=S
      waiter-list
       waiter id=process20fd055498 mode=X requestType=wait

Apparently the process running SELECT statement acquired a lock on [UserData2] table before [UserData1], despite FORCE ORDER hint.

Answer 1

With READ COMMITTED the select should not take part in the deadlock as it should only ever acquire one lock at a time. The lock can be released immediately after reading the locked row.

I dearly recommend you turn on snapshot isolation. It will solve the issue. Familiarize yourself with the 3 overheads involved: increased row size, tempdb writes and tiny read overhead. Most of the time they are not meaningful.