Some general questions

Question

Some general questions

andy 21711

Valued Member

Points: 65
More actions
January 15, 2010 at 6:39 am

#137495

I wonder if anybody can can provide answers to these questions:
1) Is there any point in using NOLOCK on queries in a database which doesn't use transactions?
2) How can deadlocks appear in a database which doesn't use transactions?

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

All queries use transactions. If you don't explicitly start a transaction, one is automatically started at the beginning of a statement and automatically committed if the query finishes without error.

As for nolock, make sure you understand what it does before using it. It's not a way to make queries faster, it's a way to get data without caring if it's accurate or not. See - http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx

As for deadlocks, yes there are cases where you can get deadlocks without explicit transactions. If you're experiencing deadlocks, switch traceflag 1222 on. That will result in a deadlock graph been written to the error log every time a deadlock occurs. Post the result of that graph here.

DBCC TRACEON(1222,-1)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

You say your database isn't using transactions. Are you running INSERT, UPDATE, and DELETE statements in the database? If so, you are using transactions. You may not be using EXPLICIT transactions, but instead using IMPLICIT transactions.

You may want to run a google search on ssc, you'll find quite a lot of discussions regarding using NOLOCK and the problems that may be encountered because of using it.

Mark Cowne One Orange Chip Points: 26984 More actions · Answer 3

andy 21711 (1/15/2010)
2) How can deadlocks appear in a database which doesn't use transactions?

Triggers can cause this

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

andy 21711 Valued Member Points: 65 More actions · Answer 4

Hi Everybody,

Thanks for your replies. I realise there are autocommit transactions, but since they are each one statement long, how can there be deadlocks unless one statement manages to access two or more resources and conflicts with another statement that accesses the same resources in a different order?

Here are two queries we have run in different SQL Management Studio windows:

declare @count int

set @count = 1

while (@count < 10000000)

begin

select * from dealmaster where serial_no = 9100093

set @count = @count + 1

end

... and ...

declare @count int

set @count = 1

while (@count < 10000000)

begin

update dealmaster set stoplock = stoplock where serial_no = 9100093

set @count = @count + 1

end

This gives rise to a deadlock. WHY? There are no triggers. We have tried it with different combinations: adding extra columns, using different numbers of rows (between 1 and 500K), etc.

The table's definition is

CREATE TABLE [dbo].[dealmaster](

[ID] [int] IDENTITY(1,1) NOT NULL,

[serial_no] [int] NOT NULL,

[stoplock] [int] NULL,

CONSTRAINT [PK_dealmaster] PRIMARY KEY CLUSTERED

(

[serial_no] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

andy 21711 Valued Member Points: 65 More actions · Answer 5

I forgot to send the deadlock graph, so here it is:

<?xml version="1.0" encoding="utf-16" ?>

- <TraceData xmlns="http://tempuri.org/TracePersistence.xsd">

- <Header>

- <ProfilerUI>

- <OrderedColumns>

</OrderedColumns>

- <TracedEvents>

- <Event id="148">

</Event>

- <Event id="25">

</Event>

- <Event id="59">

</Event>

- <Event id="60">

</Event>

- <Event id="27">

</Event>

- <Event id="189">

</Event>

</TracedEvents>

</ProfilerUI>

</Header>

- <Events>

- <Event id="65534" name="Trace Start">

</Event>

- <Event id="27" name="Lock:Timeout">

<Column id="7" name="NTDomainName">ARCDOMAIN1</Column>

<Column id="8" name="HostName">ANANTVISTA</Column>

<Column id="64" name="SessionLoginName">ARCDOMAIN1\Anant</Column>

<Column id="6" name="NTUserName">Anant</Column>

<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio - Query</Column>

<Column id="26" name="ServerName">ANANTVISTA\SQLSVR2008</Column>

<Column id="11" name="LoginName">ARCDOMAIN1\Anant</Column>

<Column id="35" name="DatabaseName">TRADLIVE</Column>

</Event>

- <Event id="27" name="Lock:Timeout">

<Column id="7" name="NTDomainName">ARCDOMAIN1</Column>

<Column id="8" name="HostName">ANANTVISTA</Column>

<Column id="64" name="SessionLoginName">ARCDOMAIN1\Anant</Column>

<Column id="6" name="NTUserName">Anant</Column>

<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio - Query</Column>

<Column id="26" name="ServerName">ANANTVISTA\SQLSVR2008</Column>

<Column id="11" name="LoginName">ARCDOMAIN1\Anant</Column>

<Column id="35" name="DatabaseName">TRADLIVE</Column>

</Event>

- <Event id="27" name="Lock:Timeout">

<Column id="7" name="NTDomainName">ARCDOMAIN1</Column>

<Column id="8" name="HostName">ANANTVISTA</Column>

<Column id="64" name="SessionLoginName">ARCDOMAIN1\Anant</Column>

<Column id="6" name="NTUserName">Anant</Column>

<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio - Query</Column>

<Column id="26" name="ServerName">ANANTVISTA\SQLSVR2008</Column>

<Column id="11" name="LoginName">ARCDOMAIN1\Anant</Column>

<Column id="35" name="DatabaseName">TRADLIVE</Column>

</Event>

- <Event id="59" name="Lock:Deadlock Chain">

<Column id="1" name="TextData">Deadlock Chain SPID = 54</Column>

<Column id="26" name="ServerName">ANANTVISTA\SQLSVR2008</Column>

<Column id="35" name="DatabaseName">TRADLIVE</Column>

</Event>

- <Event id="59" name="Lock:Deadlock Chain">

<Column id="1" name="TextData">Deadlock Chain SPID = 53</Column>

<Column id="26" name="ServerName">ANANTVISTA\SQLSVR2008</Column>

<Column id="35" name="DatabaseName">TRADLIVE</Column>

</Event>

- <Event id="25" name="Lock:Deadlock">

<Column id="7" name="NTDomainName">ARCDOMAIN1</Column>

<Column id="8" name="HostName">ANANTVISTA</Column>

<Column id="64" name="SessionLoginName">ARCDOMAIN1\Anant</Column>

<Column id="6" name="NTUserName">Anant</Column>

<Column id="10" name="ApplicationName">Microsoft SQL Server Management Studio - Query</Column>

<Column id="26" name="ServerName">ANANTVISTA\SQLSVR2008</Column>

<Column id="11" name="LoginName">ARCDOMAIN1\Anant</Column>

<Column id="35" name="DatabaseName">TRADLIVE</Column>

</Event>

- <Event id="148" name="Deadlock graph">

<Column id="1" name="TextData"><deadlock-list> <deadlock victim="process362f000"> <process-list> <process id="process362f000" taskpriority="0" logused="0" waitresource="OBJECT: 7:330848641:0 " waittime="3399" ownerId="509336" transactionname="UPDATE" lasttranstarted="2010-01-15T16:52:59.990" XDES="0x2552b5a0" lockMode="X" schedulerid="2" kpid="7592" status="suspended" spid="54" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2010-01-15T16:52:59.990" lastbatchcompleted="2010-01-15T16:50:35.883" lastattention="2010-01-15T16:46:54.407" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ANANTVISTA" hostpid="7448" loginname="ARCDOMAIN1\Anant" isolationlevel="read committed (2)" xactid="509336" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="adhoc" line="8" stmtstart="16" sqlhandle="0x0200000043e0790decd5554f4f4b18bf65aa4a631c8f597c"> UPDATE [deadlocktest] set [stoplock] = [stoplock] WHERE [serial_no]=@1 </frame> <frame procname="adhoc" line="8" stmtstart="314" stmtend="598" sqlhandle="0x02000000446d052579ffab4f407d1807981e9599e6c409d4"> update deadlocktest set stoplock = stoplock where serial_no = 671874 --update dealmaster set stoplock = stoplock where serial_no = 9100093 </frame> </executionStack> <inputbuf> --set implicit_transactions ON declare @count int set @count = 1 while (@count < 10000) begin --select * from dealmaster where serial_no = 9100093 update deadlocktest set stoplock = stoplock where serial_no = 671874 --update dealmaster set stoplock = stoplock where serial_no = 9100093 set @count = @count + 1 --commit end --set ANSI_DEFAULTS off --DBCC USEROPTIONS --dbcc traceon(1205, -1) --dbcc traceon(1204, -1) --dbcc traceon(1222, -1) </inputbuf> </process> <process id="process362fc78" taskpriority="0" logused="0" waitresource="OBJECT: 7:330848641:0 " waittime="3337" ownerId="509335" transactionname="SELECT" lasttranstarted="2010-01-15T16:52:59.890" XDES="0x5933de8" lockMode="S" schedulerid="2" kpid="4440" status="suspended" spid="53" sbid="0" ecid="0" priority="0" trancount="0" lastbatchstarted="2010-01-15T16:52:56.907" lastbatchcompleted="2010-01-15T16:51:01.590" lastattention="2010-01-15T16:51:01.647" clientapp="Microsoft SQL Server Management Studio - Query" hostname="ANANTVISTA" hostpid="7448" loginname="ARCDOMAIN1\Anant" isolationlevel="read committed (2)" xactid="509335" currentdb="7" lockTimeout="4294967295" clientoption1="671090784" clientoption2="390200"> <executionStack> <frame procname="adhoc" line="7" stmtstart="16" sqlhandle="0x02000000a2919a1eddf87f0e2f36bb473ab3d1313f7cb2cb"> SELECT * FROM [deadlocktest] WHERE [SERIAL_NO]=@1 </frame> <frame procname="adhoc" line="7" stmtstart="206" stmtend="566" sqlhandle="0x020000003d486f1c77b69516dff0938529b4b883a002d9e4"> select * from deadlocktest where SERIAL_NO = 671874 --select * from dealmaster where serial_no = 9100093 --update dealmaster set stoplock = stoplock where serial_no = 9100093 </frame> </executionStack> <inputbuf> --set implicit_transactions OFF declare @count int set @count = 1 while (@count < 10000) begin select * from deadlocktest where SERIAL_NO = 671874 --select * from dealmaster where serial_no = 9100093 --update dealmaster set stoplock = stoplock where serial_no = 9100093 set @count = @count + 1 end --set ANSI_DEFAULTS off --DBCC USEROPTIONS ---DBCC DATABASEOPTIONS </inputbuf> </process> </process-list> <resource-list> <objectlock lockPartition="0" objid="330848641" subresource="FULL" dbid="7" objectname="TRADLIVE.dbo.Deadlocktest" id="lock6ed9100" mode="IX" associatedObjectId="330848641"> <owner-list> <owner id="process362fc78" mode="IS"/> </owner-list> <waiter-list> <waiter id="process362f000" mode="X" requestType="convert"/> </waiter-list> </objectlock> <objectlock lockPartition="0" objid="330848641" subresource="FULL" dbid="7" objectname="TRADLIVE.dbo.Deadlocktest" id="lock6ed9100" mode="IX" associatedObjectId="330848641"> <owner-list> <owner id="process362f000" mode="IX"/> </owner-list> <waiter-list> <waiter id="process362fc78" mode="S" requestType="convert"/> </waiter-list> </objectlock> </resource-list> </deadlock> </deadlock-list></Column>

<Column id="26" name="ServerName">ANANTVISTA\SQLSVR2008</Column>

</Event>

- <Event id="65533" name="Trace Stop">

</Event>

</Events>

</TraceData>

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 6

andy 21711 (1/15/2010)
Thanks for your replies. I realise there are autocommit transactions, but since they are each one statement long, how can there be deadlocks unless one statement manages to access two or more resources and conflicts with another statement that accesses the same resources in a different order?

Easy, it's a key-lookup deadlock.

Session 1 runs a query that seeks on a noncovering nonclustered index, then has to do lookup to cluster to get rest of values.

Session 2 runs an update, first updates the cluster, then goes to update the nonclustered index

Hence you can get into the situation where session 1 has a shared lock on a key in a nonclustered index and requests a shared lock on a key in the clustered index while session 2 has an exclusive lock on the key in the clustered index and requests an exclusive lock on the key in the nonclustered index.

Almost a textbook deadlock.

declare @count int
set @count = 1
while (@count < 10000000)
begin
select * from dealmaster where serial_no = 9100093
set @count = @count + 1
end
... and ...
declare @count int
set @count = 1
while (@count < 10000000)
begin
update dealmaster set stoplock = stoplock where serial_no = 9100093
set @count = @count + 1
end

What is this supposed to do? Run the same select and update millions of times? Just testing for deadlocks?

Please post all indexes that table has. Usual fix for this is to widen the nonclustered index so that it's covering, though not feasible when you're doing a select *.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andy 21711 Valued Member Points: 65 More actions · Answer 7

Hi Gail,

Thanks for your reply, which was very illuminating: it certainly hadn't occurred to us that indexes could be involved in deadlocks. However, the scenario you describe doesn't apply in this case: the table doesn't have a non-clustered index, only a clustered one. Is it possible to deadlock in in that case?

You asked what the script is supposed to do: yes, it was just to test for deadlocks.

Andy

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 8

Can you repost that deadlock graph so that it's readable please. Either attach the xml file that profiler produces or use traceflag 1222 and post the output of that.

The one you posted is going to take just too much work to get into a readable form

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

andy 21711 Valued Member Points: 65 More actions · Answer 9

Hi Gail,

Here's the XML file (if I've managed to attach it correctly).

Andy

pravin.narhe-789089 Newbie Points: 1 More actions · Answer 10

pravin.narhe-789089

Newbie

Points: 1

January 22, 2010 at 1:25 am

#1107140

🙂