Smith Deadlock

declare @CustomerID int
set @CustomerID = 342

--Original statement
select	aut.[Token]
	, aut.[UserName]
	, aut.[LastAuthenticationTime]
	, aut.[LoginSourceType]
	, aut.[AuthenticationRetries]
	, aut.[MarketName]
	, aut.[SerializedEncryptedValues]
	, ai.AuthInfoID as [Id]
	, ai.[AuthInfoID], ai.[Password]
	, ai.[RegistrationTime]
	, ai.[MustChangePassword]
	, ai.[LastPasswordChangeTime]
	, ai.[PasswordEncrypterType]
	, ai.[SerializedEncryptedValues]
from Authentication.Authentication aut
inner join Authentication.AuthToInfo ati on ati.AuthTokenID = aut.Token
inner join Authentication.AuthInfo ai ON ai.AuthInfoID = ati.AuthInfoID
inner join Customer.AuthToken tok ON tok.AuthTokenID = aut.Token --DEADLOCKED RESOURCE
inner join Customer.Profile pro ON pro.CustomerID = tok.CustomerID
where pro.CustomerID = @CustomerID

--Alternative #1: Two statements
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

--1: to get token based on customer id
declare @token uniqueidentifier
select @token = tok.AuthTokenId
from Customer.Profile pro with (nolock)
inner join Customer.AuthToken tok with (nolock)
	on tok.CustomerID = pro.CustomerID
where pro.CustomerID = @CustomerID

--2: to get token details
select	aut.[Token]
	, aut.[UserName]
	, aut.[LastAuthenticationTime]
	, aut.[LoginSourceType]
	, aut.[AuthenticationRetries]
	, aut.[MarketName]
	, aut.[SerializedEncryptedValues]
	, ai.AuthInfoID as [Id]
	, ai.[AuthInfoID], ai.[Password]
	, ai.[RegistrationTime]
	, ai.[MustChangePassword]
	, ai.[LastPasswordChangeTime]
	, ai.[PasswordEncrypterType]
	, ai.[SerializedEncryptedValues]
from Authentication.Authentication aut with (nolock)
inner join Authentication.AuthToInfo ati with (nolock)
	on ati.AuthTokenID = aut.Token
inner join Authentication.AuthInfo ai with (nolock)
	on ai.AuthInfoID = ati.AuthInfoID
where aut.Token = @token

--Alternative #2: Sub-query
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
select	aut.[Token]
	, aut.[UserName]
	, aut.[LastAuthenticationTime]
	, aut.[LoginSourceType]
	, aut.[AuthenticationRetries]
	, aut.[MarketName]
	, aut.[SerializedEncryptedValues]
	, ai.AuthInfoID as [Id]
	, ai.[AuthInfoID], ai.[Password]
	, ai.[RegistrationTime]
	, ai.[MustChangePassword]
	, ai.[LastPasswordChangeTime]
	, ai.[PasswordEncrypterType]
	, ai.[SerializedEncryptedValues]
from Authentication.Authentication aut with (nolock)
inner join Authentication.AuthToInfo ati with (nolock)
	on ati.AuthTokenID = aut.Token
inner join Authentication.AuthInfo ai with (nolock)
	on ai.AuthInfoID = ati.AuthInfoID
where exists (
	select 1
	from Customer.Profile pro with (nolock)
	inner join Customer.AuthToken tok with (nolock)
		on tok.CustomerID = pro.CustomerID
	where pro.CustomerID = @CustomerID
	and aut.Token = tok.AuthTokenId
)

Authentication.