Deadlocks on SELECT statements?

Can anyone explain to me, even hypothetically, how 2 SELECT statements on th
e
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apfWhat isolation level are you using?
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||The default - Read Committed|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed|||Can you post the deadlock trace?
"apf" wrote:

> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > o
n the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.|||If you can also post the two actual select statements causing the deadlocks
.
Are there any indexes supporting the where clause and if so how selective ar
e
they?
"FredG" wrote:
[vbcol=seagreen]
> Can you post the deadlock trace?
>
> "apf" wrote:
>

Deadlocks on SELECT statements?

Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apf
What isolation level are you using?
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||The default - Read Committed
|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed
|||Can you post the deadlock trace?
"apf" wrote:

> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf
|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.
|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
[vbcol=seagreen]
> Can you post the deadlock trace?
>
> "apf" wrote:

Deadlocks on SELECT statements?

Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
same table can cause a deadlock?
The output to trace flag 1204 indicates that deadlocking in occuring on 2
statments that look like this:
select count(*) from table_1 where ...
The where criteria is different for the 2 SELECTS.
Thanks in advance for any ideas or guesses!!
apfWhat isolation level are you using?
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:3E1F69F9-C6C7-4F61-A767-4BED27D3B26E@.microsoft.com...
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on
> the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||The default - Read Committed|||Are you on the latest service pack? Any chance this is the cause:
http://support.microsoft.com/kb/293232/EN-US/
--
Andrew J. Kelly SQL MVP
"apf" <apf@.discussions.microsoft.com> wrote in message
news:C9FC427B-D02E-4E08-AE5A-39DDD64166BB@.microsoft.com...
> The default - Read Committed|||Can you post the deadlock trace?
"apf" wrote:
> Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> same table can cause a deadlock?
> The output to trace flag 1204 indicates that deadlocking in occuring on 2
> statments that look like this:
> select count(*) from table_1 where ...
> The where criteria is different for the 2 SELECTS.
> Thanks in advance for any ideas or guesses!!
> apf|||> Can anyone explain to me, even hypothetically, how 2 SELECT statements > on the same table can cause a deadlock?
depends on the isolation level
here you go, in QA run this:
create table a(m int, n int)
create unique clustered index au on a(m)
insert into a
select 1,2
union all
select 2,2
union all
select 3,1
go
begin transaction
select * from a with(updlock) where m=1
open another QA window and run
begin transaction
select * from a with(updlock) where m=3
return to window 1 and run
select * from a with(updlock) where m=3
return to window 3 and run
select * from a with(updlock) where m=1
wait a little bit and here you go
Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 58) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.|||If you can also post the two actual select statements causing the deadlocks.
Are there any indexes supporting the where clause and if so how selective are
they?
"FredG" wrote:
> Can you post the deadlock trace?
>
> "apf" wrote:
> > Can anyone explain to me, even hypothetically, how 2 SELECT statements on the
> > same table can cause a deadlock?
> >
> > The output to trace flag 1204 indicates that deadlocking in occuring on 2
> > statments that look like this:
> >
> > select count(*) from table_1 where ...
> >
> > The where criteria is different for the 2 SELECTS.
> >
> > Thanks in advance for any ideas or guesses!!
> >
> > apf

Deadlock on Update Statement (NOLOCK)

I have the following updates statements in my stored procedure which caused
a deadlock. Should I take the (NOLOCK) statement out of the update
statements?
Is there some else I can to help resolve this deadlock?
Thanks,
Update SRA_FlowMaster
Set Status = 'I'
From SRA_FlowMaster New
Inner Join SRA_FlowMaster (NoLock)
On New. FlowMasterNo = SRA_FlowMaster. FlowMasterNo
And New.TypeCode = SRA_FlowMaster. TypeCode
WhereNew. FlowMasterID = @.i_FlowMasterID
And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
And SRA_FlowMaster. Status In ('K', 'M')
Update SRA_FlowMaster
Set Status = 'I'
From SRA_FlowMaster New
Inner Join SRA_FlowMaster (NoLock)
On New. FlowMasterNo = SRA_FlowMaster. FlowMaster
And New.TypeCode = SRA_FlowMaster. TypeCode
Where New. FlowMasterID = @.i_FlowMasterID
And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
And SRA_FlowMaster. Status = 'A'
And New. Status In ('A', 'D')Joe,
A deadlock involved two processes requesting a resource being locked by the
other. You have to identify the processes and the statements causing the
deadlock. The table hint you are using is not a hint to prevent deadlocks.
See "Minimizing Deadlocks" and "Troubleshooting Deadlocks" in BOL for more
information.
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Joe K." wrote:

> I have the following updates statements in my stored procedure which cause
d
> a deadlock. Should I take the (NOLOCK) statement out of the update
> statements?
> Is there some else I can to help resolve this deadlock?
> Thanks,
>
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMasterNo
> And New.TypeCode = SRA_FlowMaster. TypeCode
> WhereNew. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status In ('K', 'M')
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMaster
> And New.TypeCode = SRA_FlowMaster. TypeCode
> Where New. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status = 'A'
> And New. Status In ('A', 'D')
>|||Hmm. Let me guess.
'Status' has only a handful of values.
You have an index on 'Status'.
Almost all of the values of 'Status' are the same.
This is a pretty common issue. Status fields are really a bad way to
represent and control status of an object, even thought they seem intuitive
at first. The key range locking required on an update makes it almost
impossible to scale to any reasonable level.
You can drop the index on Status but you will probably time out on some
other queries. (NOLOCK) hints won't change the inherent locking required to
do an update. Your problem is architectural and will require adjusting the
schema to fix. I would represent Status as a work queue using another
table. The presence of a pointer to the Primary Key indicates the status.
If there is no entries, then the status is whatever the most common status
(I.E. 'Closed', 'C', 'Paid', depending on the context) actually is.
Geoff N. Hiten
Microsoft SQL Server MVP
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:BED3489D-5AC1-48A7-B785-C9EF6128573B@.microsoft.com...
> I have the following updates statements in my stored procedure which
> caused
> a deadlock. Should I take the (NOLOCK) statement out of the update
> statements?
> Is there some else I can to help resolve this deadlock?
> Thanks,
>
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMasterNo
> And New.TypeCode = SRA_FlowMaster. TypeCode
> WhereNew. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status In ('K', 'M')
> Update SRA_FlowMaster
> Set Status = 'I'
> From SRA_FlowMaster New
> Inner Join SRA_FlowMaster (NoLock)
> On New. FlowMasterNo = SRA_FlowMaster. FlowMaster
> And New.TypeCode = SRA_FlowMaster. TypeCode
> Where New. FlowMasterID = @.i_FlowMasterID
> And SRA_FlowMaster. FlowMasterID <> @.i_FlowMasterID
> And SRA_FlowMaster. Status = 'A'
> And New. Status In ('A', 'D')
>

Deadlock isn't logging SQL statements

Hi,
I'm running SQL 2005 SP1 and we're getting some deadlocks. There is nothing
written to the event log. I've turned on 1204 and the log is showing the
deadlock however again, no SQL statements. I see this in the SQL Server log:
Log Viewer could not read information for this log entry. Cause: Data is
Null. This method or property cannot be called on Null values.. Content:.
I've also tried profiling deadlock and deadlock chain events and I can't get
the SQL still. Anyone tell me what I'm doing wrong?
ThanksHi
http://blogs.msdn.com/bartd/archive/2006/09/09/747119.aspx
http://blogs.msdn.com/bartd/archive/2006/09/25/770928.aspx
"sqlboy2000" <sqlboy2000@.discussions.microsoft.com> wrote in message
news:03FDEDA3-935F-458F-81AA-9489BAB1C2B5@.microsoft.com...
> Hi,
> I'm running SQL 2005 SP1 and we're getting some deadlocks. There is
> nothing
> written to the event log. I've turned on 1204 and the log is showing the
> deadlock however again, no SQL statements. I see this in the SQL Server
> log:
> Log Viewer could not read information for this log entry. Cause: Data is
> Null. This method or property cannot be called on Null values.. Content:.
> I've also tried profiling deadlock and deadlock chain events and I can't
> get
> the SQL still. Anyone tell me what I'm doing wrong?
> Thanks

DDL in Transactions

Hello,
I know that it is possible to put DDL statements (i.e.
CREATE TABLE, DROP TABLE etc...) in transactions but I
have found a peculiarity that I am trying to get around.
I issued the following:
BEGIN TRANSACTION
CREATE VIEW TempView AS select * from tempTable
COMMIT TRANSACTION
It gave the following error message:
Server: Msg111, level 15, State 1, Line 2
'CREATE VIEW' must be the first statement in a query batch
Can anyone find a way around this using the simple T-SQL
code above?
Thanks in advance
Jamie
P.S. Why is there no microsoft.public.sqlserver.tsql
newsgroup?Hello Jamie !
Sorry but this aint the way it goes. DDL Statements such as
alter,create,drop fires an Implicit commit to send the changes directly to
the database.
HTH, Jens Süßmeyer.|||Jens,
Thats what I always thought too. But if I try this:
BEGIN TRANSACTION
create table temptable (col1 int)
ROLLBACK TRANSACTION
the rollback works (i.e. the table isn't created). Try it! There is even a server level setting that indicates whether you can allow DDL in transactions or not (see sp_server_info, number 110).
So, I can have DDL in a transaction but not CREATE VIEW it seems. Why not?
Regards
Jamie
>--Original Message--
>Hello Jamie !
>Sorry but this aint the way it goes. DDL Statements such as
>alter,create,drop fires an Implicit commit to send the changes directly to
>the database.
>HTH, Jens S=FC=DFmeyer.
>
>.
>|||Hi Jens,
That is not true, DDL does not do an implicit commit and can be included in
a multi statement transaction. The only issue there is, is the error Jamie
got: CREATE VIEW/PROCEDURE and a few others have to be the first statement
in a batch. There is an easy way around that, as a transaction can span
multiple batches:
BEGIN TRAN
GO
CREATE VIEW....
COMMIT TRAN
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> Hello Jamie !
> Sorry but this aint the way it goes. DDL Statements such as
> alter,create,drop fires an Implicit commit to send the changes directly to
> the database.
> HTH, Jens Süßmeyer.
>|||DDL does not issue an implicit commit in SQL Server, although this may
be the case with some other RDBMS vendors.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jens Süßmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
> Hello Jamie !
> Sorry but this aint the way it goes. DDL Statements such as
> alter,create,drop fires an Implicit commit to send the changes
directly to
> the database.
> HTH, Jens Süßmeyer.
>|||DDL for textual objects (views, procedures, etc.) must be in a separate
batch so that SQL Server can determine where the CREATE statement ends.
Multiple batches may be executed in a single transaction. Try:
BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"Jamie Thomson" <jamie.thomson@.int21.com> wrote in message
news:008301c367f9$f337e4b0$a301280a@.phx.gbl...
> Hello,
> I know that it is possible to put DDL statements (i.e.
> CREATE TABLE, DROP TABLE etc...) in transactions but I
> have found a peculiarity that I am trying to get around.
> I issued the following:
> BEGIN TRANSACTION
> CREATE VIEW TempView AS select * from tempTable
> COMMIT TRANSACTION
> It gave the following error message:
> Server: Msg111, level 15, State 1, Line 2
> 'CREATE VIEW' must be the first statement in a query batch
> Can anyone find a way around this using the simple T-SQL
> code above?
> Thanks in advance
> Jamie
>
> P.S. Why is there no microsoft.public.sqlserver.tsql
> newsgroup?|||Thanks Gents,
Dan's suggestion works perfectly.
i.e. :
BEGIN TRANSACTION
GO
CREATE VIEW TempView AS select * from tempTable
GO
COMMIT TRANSACTION
GO
Thanks for the advice.
Regards
Jamie
>--Original Message--
>DDL does not issue an implicit commit in SQL Server, although this may
>be the case with some other RDBMS vendors.
>-- >Hope this helps.
>Dan Guzman
>SQL Server MVP
>--
>SQL FAQ links (courtesy Neil Pike):
>http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=3D800
>http://www.sqlserverfaq.com
>http://www.mssqlserver.com/faq
>--
>"Jens S=FC=DFmeyer" <jsuessmeyer@.(Remove_ME]web.de> wrote in message
>news:OEoreq$ZDHA.3768@.tk2msftngp13.phx.gbl...
>> Hello Jamie !
>> Sorry but this aint the way it goes. DDL Statements such as
>> alter,create,drop fires an Implicit commit to send the changes
>directly to
>> the database.
>> HTH, Jens S=FC=DFmeyer.
>>
>
>.
>|||> BEGIN TRANSACTION
> GO
> CREATE VIEW TempView AS select * from tempTable
> GO
> COMMIT TRANSACTION
> GO
As an aside, you couldn't do this inside the definition of a stored
procedure; you'd have to use dynamic SQL, I suppose. But that doesn't seem
to be an issue for the OP.

DDL Extraction

Does anyone know how to pull DDL create statements out of a SQL Server 2005 EE database for existing objects? I'm mainly concerned with indexes and constraints. If possible I can create the statements myself if I can get all of the information out of the databse.

Thanks in advance

Hi,

you can either use the SMO class libraries to use the Script() method on the objects or the GUI of SQL Server Managment Studio which does the same things behind the scenes.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Thanks I'll check it out.

I was looking for something that is not using .NET code and is just SQL.

|||You can look in the system views and get all the information you need. INFORMATION_SCHEMA prefixed views are a more denormalized version of the sys.XXX views and will contain most everything you would need to script create statements.

DDL Conversion

Hi,

How do you convert DDL statements of SQL Server, which are
generated by DTS into other database vendors' syntax (IBM
DB2 or Oracle)?
Any utility tool?

Thank you,

--jaquesjaquesbosch2@.yahoo.de (Jaques) wrote in message news:<569b197f.0311021228.1602722d@.posting.google.com>...
> Hi,
> How do you convert DDL statements of SQL Server, which are
> generated by DTS into other database vendors' syntax (IBM
> DB2 or Oracle)?
> Any utility tool?
> Thank you,
> --jaques

There are a number of third-party products which can do this - I've
used Embarcadero products for similar tasks, which generally work
well, although they can be expensive. The disadvantage of these tools
is that there will always be some platform-specific data types or
syntax which may not be cleanly scripted because there is no direct
equivalent. So there will usually be some amount of manual
checking/modification required.

Simon|||Hi

If you are using a modelling tool, then this may produce the scripts for the
different database systems.

John

"Jaques" <jaquesbosch2@.yahoo.de> wrote in message
news:569b197f.0311021228.1602722d@.posting.google.c om...
> Hi,
> How do you convert DDL statements of SQL Server, which are
> generated by DTS into other database vendors' syntax (IBM
> DB2 or Oracle)?
> Any utility tool?
> Thank you,
> --jaques