Deadlocks & BEGIN/END TRANSACTION

Greetings,

I've been reading with interest the threads here on deadlocking, as I'm
finding my formerly happy app in a production environment suddenly
deadlocking left and right. It started around the time I decided to
wrap a series of UPDATE commands with BEGIN/END.

The gist of it is I have a .NET app that can do some heavy reading (no
writing) from tblWOS. It can take a minute or so to read all the data
into the app, along with data from other tables.

I also have a web app out on the floor where people can enter
transactions which updates perhaps 5-20 records in tblWOS at a time.
The issue comes when someone is loading data with the app, and someone
else tries an update through the web app: deadlocks-ville on the
application and/or the web app.

Again, I believe it began around the time I wrapped those 5-20 record
updates to tblWOS on the web app with BEGIN/END. The funny thing is
that the records involved are not the same ones, so I'm thinking some
kind of table-level lock is going on.

I've played with UPDLOCK in examples, but don't quite understand what
it's attempting to do. Since the web update is discrete and short, and
it is NOT updating records that are getting loaded, I'd like the
BEGIN/UPDATE/END web transaction to happen and not deadlock the loading
application.

Any suggestions? I'd be most grateful.

thanks, LeafLeaf (rangerleaf@.hotmail.com) writes:
> I've been reading with interest the threads here on deadlocking, as I'm
> finding my formerly happy app in a production environment suddenly
> deadlocking left and right. It started around the time I decided to
> wrap a series of UPDATE commands with BEGIN/END.
> The gist of it is I have a .NET app that can do some heavy reading (no
> writing) from tblWOS. It can take a minute or so to read all the data
> into the app, along with data from other tables.
> I also have a web app out on the floor where people can enter
> transactions which updates perhaps 5-20 records in tblWOS at a time.
> The issue comes when someone is loading data with the app, and someone
> else tries an update through the web app: deadlocks-ville on the
> application and/or the web app.
> Again, I believe it began around the time I wrapped those 5-20 record
> updates to tblWOS on the web app with BEGIN/END. The funny thing is
> that the records involved are not the same ones, so I'm thinking some
> kind of table-level lock is going on.
> I've played with UPDLOCK in examples, but don't quite understand what
> it's attempting to do. Since the web update is discrete and short, and
> it is NOT updating records that are getting loaded, I'd like the
> BEGIN/UPDATE/END web transaction to happen and not deadlock the loading
> application.

Of course, if you want those 5-20 updates to be performed all or none
of them, but not only half of them, user-defined transactions is the way
to go. But since you then will hold locks for a longer period, you will
be more prone to deadlocking.

Deadlock situations can be fairly straight-forward to understand, but can
also be very complex. Therefore it's difficult to give precise advice from
any distance.

I can give some general advice though:

Indexing is important. Make sure that all involved queries uses Index Seek
or Clustered Index Seek, so the queries do not require table locks. You
can study the query plans by running the queries from Query Analyzer, but
you can also use Profiler to trace the application, and include the
Show Execution Plan event.

Important is also the order of access. Say that process A updates rows
1, 5, 9, 11, 17 in that order and process B updates rows 24, 27, 11, 1, 2
in that order. They will deadlock, because when A comes to row 11, B already
has updated that one, but not committed it. B then gets stuck on row 1,
because A has a lock on that row.

I don't if this happening in your application, but it's very important to
not have transactions in progress while waiting for user input. You could
be waiting all day in such case.

Finally, UPDLOCK, is a locking hint which is good when you read a row,
with the intention to update it in the same transaction. UPDLOCK itself
is a shared locks, and thus readers are not block. But the lock remains
to the end of the transaction, and no other process can have an UPDLOCK
on the same resource.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland,

Thanks so much for your quick and verbose response.

> Indexing is important. Make sure that all involved queries uses Index
Seek
> or Clustered Index Seek, so the queries do not require table locks.

I'm not using queries, but rather ADO.NET to pull data from tables by
filtering on IDs. Can I take that to mean I should make sure that any
IDs in my WHERE statements should be indexed? All records in my DB have
a primary key, clustered index. But in one-to-many tables, I filter
heavily on that related table to the primary key in another table. For
example, tblWOS.FactoryOrderID is the reference in a child table to PK
in tblFactoryOrders.FactoryOrderID:

SELECT * FROM tblWOS WHERE FactoryOrderID=10

Are you implying that I should make sure tblWOS.FactoryOrderID should
be indexed, too?

I'll be a bit more explicit. The web app passes a series of discrete
SQL commands via an ADO.NET connection object:

BEGIN TRANS
-- Update primary table (one record)
UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
-- Update related child table (many records)
UPDATE tblWOS SET ... WHERE FactoryOrderID=10
COMMIT TRANS

tblFactoryOrders has one record in it (primary record), and tblWOS
could have 5-20 related to tblFactoryOrders.

These transactions can happen for any primary record at any time from
the web by 100 users, but generally each primary record gets hit just a
couple times a day.

Meanwhile, there's a .NET app which periodically (2-10 times daily)
loads a set of data from tblFactoryOrders and tblWOS, about 900 records
from the first and 5,000 records from the second. It loads this way:

SELECT FactorOrderID, * FROM tblFactoryOrders WHERE ...

and then a series of for each record in tblFactoryOrders

SELECT * FROM tblWOS WHERE FactoryOrderID=...

The issue is that while this load is happening, someone on the web
doing an UPDATE on records unrelated to the loaded values cause a
deadlock on the app's SELECT. Is this an ISOLATION LEVEL issue?

It's OK if someone on the web updates while this load is happening.

I'd like to resolve:

- if two folks on the web hit the same FactoryOrderID object, I'd like
them not to deadlock, but one to wait on the other.

- if someone is loading the application, I'd like it not to deadlock
when someone on the web does a transaction.

- the app can also write back to tblWOS (again, records not available
by the web app), and not deadlock the web app or the save process.

Would it be wise to stick a:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANS
UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
UPDATE tblWOS SET ... WHERE FactoryOrderID=10
COMMIT TRANS

thank you, Leaf|||Leaf (rangerleaf@.hotmail.com) writes:
> I'm not using queries, but rather ADO.NET to pull data from tables by
> filtering on IDs. Can I take that to mean I should make sure that any
> IDs in my WHERE statements should be indexed? All records in my DB have
> a primary key, clustered index. But in one-to-many tables, I filter
> heavily on that related table to the primary key in another table. For
> example, tblWOS.FactoryOrderID is the reference in a child table to PK
> in tblFactoryOrders.FactoryOrderID:
> SELECT * FROM tblWOS WHERE FactoryOrderID=10
> Are you implying that I should make sure tblWOS.FactoryOrderID should
> be indexed, too?

Assuming tblWOS is of any size, you should definitely have an index
on that column. Now, I don't know much about this table, but I like
to point out from what you said here, it could very well be that this
is the column you should have your clustered index on.

> Meanwhile, there's a .NET app which periodically (2-10 times daily)
> loads a set of data from tblFactoryOrders and tblWOS, about 900 records
> from the first and 5,000 records from the second. It loads this way:
> SELECT FactorOrderID, * FROM tblFactoryOrders WHERE ...
> and then a series of for each record in tblFactoryOrders
> SELECT * FROM tblWOS WHERE FactoryOrderID=...

It would certainly be a good idea, to write a stored procedure that
produces rwo result sets: one that contains the rows from tblFactoryOrders,
one that contain all rows from tblFactoryOrders. In any case, sending
a query for each FactoryOrderId means a lot of network roundtrips.
Basically, if you get 100 ids pact, the load takes 100 times of what
it could take.

> The issue is that while this load is happening, someone on the web
> doing an UPDATE on records unrelated to the loaded values cause a
> deadlock on the app's SELECT. Is this an ISOLATION LEVEL issue?

Not really. The table scans are the real issue here.

> - if two folks on the web hit the same FactoryOrderID object, I'd like
> them not to deadlock, but one to wait on the other.

And then the guy that is number #2 overwrites the updates of #1? The
common strategy is to use optimistic locking. This can be implemented
in several ways, but the easiest is to add a timestamp column.
Timestamp columns are automatically updated by SQL Server each time
you update a row. (And they have nothing to do with date and time.)
So you add a timestamp condition to the UPDATE, and if they don't
match, the user is informed of an update conflict?

> - if someone is loading the application, I'd like it not to deadlock
> when someone on the web does a transaction.

I know too little about the scenario to tell whether just adding the
index will help. I'm also a little concerned of the consistency the
data that is being loaded. What happens if there is an update to
tblWOS when a load is in progress? What is the desired result?

By wrapping the load in a transaction, with the isolation level of
REPEATABLE READ or SERIALIZABLE, you could ensure consistency, as no
update of orders being loaded could be performed while the load is going
on.

This would even more require you to make sure tblFactoryOrders is
read only once, and not once for each ID.

> Would it be wise to stick a:
> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
> BEGIN TRANS
> UPDATE tblFactoryOrders SET ... WHERE FactoryOrderID=10
> UPDATE tblWOS SET ... WHERE FactoryOrderID=10
> COMMIT TRANS

Actually, as hinted above, it's more the SELECT transaction that
can benefit for a higher isolation level. The UPDATE transaction
will not change that much, if at all.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks for your kind reply. VERY helpful.sql

Deadlocking

I'm looking for any tuning information that you may have
on deadlocking.
I have an APP(not written by me) that I support and it is
generating deadlocks at some of customer sites. In one
case the client is seeing upwards of 40 deadlocks in a
day. Now I know the app needs to be dealt with, but that
is a long process as we have about 100 clients live and
are working on upgrades and patches. What I want to know
is, is there anything on the server side I can do to
mitigate the deadlocks? Can I allow the operation to re-
try more and thus hope one of the processes completes
before killing a process is needed? Basically, does
anyone have any thoughts? Could I pad rows in the table
to cause one row per page, thus hopefully a page lock is
actually a row lock and thus causing fewer deadlocks, if
they are table based most often?
I'm a bit desperate.
Thanks.Matt
Here are a couple of articles that might help
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;224453
http://support.microsoft.com/default.aspx?scid=kb;EN-
US;271509
Regards
John

Deadlock problem with insert trigger

Hello,
I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
have a windows app that polls a web-service every few seconds, and the
web service logs that communication in a table. Since there's no reason
to keep really old log entries, and in order to keep the table size
down, I wrote a trigger to delete all records older than x days (x=5 in
this case).
Shortly after adding the trigger, I started getting frequent deadlock
errors. The table (when pruned by the trigger) is about 100k records or
so. Also, there is more than one copy of the windows app polling the
web-service, so two polling events could occur at the same time.
I've included the source for both the stored proc and the trigger below.
At first I thought the problem had to do with the "select" stmt at the
end of the stored proc, but adding the (NOLOCK) hint did not solve the
problem.
Any idea what might be causing the deadlock?
TIA,
Gabe
-- 8< --
CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
@.StoreKey uniqueidentifier,
@.dateAdded datetime,
@.Source nvarchar(100)
AS
INSERT INTO dbo.[StoreCommunicationLog](
[StoreKey],
[dateAdded],
[Source]
) VALUES (
@.StoreKey,
@.dateAdded,
@.Source
)
SELECT
[StoreCommunicationLogID],
[StoreKey],
[dateAdded],
[Source]
FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
WHERE
[StoreCommunicationLogID] = @.@.IDENTITY
GO
-- 8< --
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DECLARE @.StoreKey UNIQUEIDENTIFIER
SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
DECLARE simpleCursor CURSOR
LOCAL
KEYSET
FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey) AND
(ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
DECLARE @.id int
OPEN simpleCursor
FETCH LAST FROM simpleCursor
INTO @.id
CLOSE simpleCursor
DEALLOCATE simpleCursor
DELETE FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey)
AND (StoreCommunicationLogID < @.id)
GO
Hi Gabe
Set up a trace to capture deadlock events, deadlock chains, batches and
statements, so you can see what processes are involved, and what statements
they executed leading up to the deadlock.
Also, why in the world is the trigger using a cursor?
There is no guarantee that the last row returned by the cursor has any
special significance.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Gabe Moothart" <gabe@.imaginesystems.net> wrote in message
news:%23XizNMFSGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the web
> service logs that communication in a table. Since there's no reason to
> keep really old log entries, and in order to keep the table size down, I
> wrote a trigger to delete all records older than x days (x=5 in this
> case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the end
> of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
>
|||Kalen,
Thanks, I will do that. The trigger was actually not written by me, so I
don't know why a cursor was used. I'll take a look at cleaning it up.
Gabe

> Hi Gabe
> Set up a trace to capture deadlock events, deadlock chains, batches and
> statements, so you can see what processes are involved, and what statements
> they executed leading up to the deadlock.
> Also, why in the world is the trigger using a cursor?
> There is no guarantee that the last row returned by the cursor has any
> special significance.
>
|||Hi
first : using a cursur in a trigger is a very bad idea.
Remember while you are inside the trigger code you are IN the transaction.
second : trigger act once only even if the SQL statement that fired it
take one million rows. So the code posted wont work in this case !
The trigger code must not have variable inside and must be only write
with "sets" code (SQL statements).
You can code it this way :
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DELETE FROM StoreCommunicationLog
FROM StoreCommunicationLog S
INNER JOIN inserted i
ON S.StoreKey = i.StoreKey
WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
GO
Third : if StoreKey is the primary key of your table, having an
uniqueidentifier type as the primary key col type is not a good choice
to have some performances. GUID is a 32 byte (256 bits) data so the CPU
must load this data with 8 cycles... So the choice of your key is about
8 to 16 times less quick than a simple integer wich is exactly the max
CPU word (32 bits) to be treated in one cycle...
So the transaction will cost a lot !
To avoid deadlock you must have transactions that are the quickest as
possible. This is not the way you are engaged...
A +
Gabe Moothart a crit :
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no reason
> to keep really old log entries, and in order to keep the table size
> down, I wrote a trigger to delete all records older than x days (x=5 in
> this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the
> end of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************
|||SQLpro,
I haven't tested it yet, so I'm not sure it will solve the deadlock, but
your modified trigger code is much more elegant than what I had been
using. Thanks!
Gabe

> Hi
> first : using a cursur in a trigger is a very bad idea.
> Remember while you are inside the trigger code you are IN the transaction.
> second : trigger act once only even if the SQL statement that fired it
> take one million rows. So the code posted wont work in this case !
> The trigger code must not have variable inside and must be only write
> with "sets" code (SQL statements).
> You can code it this way :
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DELETE FROM StoreCommunicationLog
> FROM StoreCommunicationLog S
> INNER JOIN inserted i
> ON S.StoreKey = i.StoreKey
> WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
> GO
> Third : if StoreKey is the primary key of your table, having an
> uniqueidentifier type as the primary key col type is not a good choice
> to have some performances. GUID is a 32 byte (256 bits) data so the CPU
> must load this data with 8 cycles... So the choice of your key is about
> 8 to 16 times less quick than a simple integer wich is exactly the max
> CPU word (32 bits) to be treated in one cycle...
> So the transaction will cost a lot !
> To avoid deadlock you must have transactions that are the quickest as
> possible. This is not the way you are engaged...
> A +
>
> Gabe Moothart a crit :
>
|||When I need to delete old rows from a log table, I run a scheduled job
every night which executes a stored procedure. Run more often if you
need to.
Delete table where date < dateAdd(day, 45, getdate() )
just an idea
Tom
Gabe Moothart wrote:

> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no
> reason to keep really old log entries, and in order to keep the table
> size down, I wrote a trigger to delete all records older than x days
> (x=5 in this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records
> or so. Also, there is more than one copy of the windows app polling
> the web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger
> below. At first I thought the problem had to do with the "select" stmt
> at the end of the stored proc, but adding the (NOLOCK) hint did not
> solve the problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.

Deadlock problem with insert trigger

Hello,
I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
have a windows app that polls a web-service every few seconds, and the
web service logs that communication in a table. Since there's no reason
to keep really old log entries, and in order to keep the table size
down, I wrote a trigger to delete all records older than x days (x=5 in
this case).
Shortly after adding the trigger, I started getting frequent deadlock
errors. The table (when pruned by the trigger) is about 100k records or
so. Also, there is more than one copy of the windows app polling the
web-service, so two polling events could occur at the same time.
I've included the source for both the stored proc and the trigger below.
At first I thought the problem had to do with the "select" stmt at the
end of the stored proc, but adding the (NOLOCK) hint did not solve the
problem.
Any idea what might be causing the deadlock?
TIA,
Gabe
-- 8< --
CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
@.StoreKey uniqueidentifier,
@.dateAdded datetime,
@.Source nvarchar(100)
AS
INSERT INTO dbo.[StoreCommunicationLog](
[StoreKey],
[dateAdded],
[Source]
) VALUES (
@.StoreKey,
@.dateAdded,
@.Source
)
SELECT
[StoreCommunicationLogID],
[StoreKey],
[dateAdded],
[Source]
FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
WHERE
[StoreCommunicationLogID] = @.@.IDENTITY
GO
-- 8< --
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DECLARE @.StoreKey UNIQUEIDENTIFIER
SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
DECLARE simpleCursor CURSOR
LOCAL
KEYSET
FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey) AND
(ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
DECLARE @.id int
OPEN simpleCursor
FETCH LAST FROM simpleCursor
INTO @.id
CLOSE simpleCursor
DEALLOCATE simpleCursor
DELETE FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey)
AND (StoreCommunicationLogID < @.id)
GOHi Gabe
Set up a trace to capture deadlock events, deadlock chains, batches and
statements, so you can see what processes are involved, and what statements
they executed leading up to the deadlock.
Also, why in the world is the trigger using a cursor?
There is no guarantee that the last row returned by the cursor has any
special significance.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Gabe Moothart" <gabe@.imaginesystems.net> wrote in message
news:%23XizNMFSGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the web
> service logs that communication in a table. Since there's no reason to
> keep really old log entries, and in order to keep the table size down, I
> wrote a trigger to delete all records older than x days (x=5 in this
> case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the end
> of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
>|||Kalen,
Thanks, I will do that. The trigger was actually not written by me, so I
don't know why a cursor was used. I'll take a look at cleaning it up.
Gabe
> Hi Gabe
> Set up a trace to capture deadlock events, deadlock chains, batches and
> statements, so you can see what processes are involved, and what statements
> they executed leading up to the deadlock.
> Also, why in the world is the trigger using a cursor?
> There is no guarantee that the last row returned by the cursor has any
> special significance.
>|||Hi
first : using a cursur in a trigger is a very bad idea.
Remember while you are inside the trigger code you are IN the transaction.
second : trigger act once only even if the SQL statement that fired it
take one million rows. So the code posted wont work in this case !
The trigger code must not have variable inside and must be only write
with "sets" code (SQL statements).
You can code it this way :
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DELETE FROM StoreCommunicationLog
FROM StoreCommunicationLog S
INNER JOIN inserted i
ON S.StoreKey = i.StoreKey
WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
GO
Third : if StoreKey is the primary key of your table, having an
uniqueidentifier type as the primary key col type is not a good choice
to have some performances. GUID is a 32 byte (256 bits) data so the CPU
must load this data with 8 cycles... So the choice of your key is about
8 to 16 times less quick than a simple integer wich is exactly the max
CPU word (32 bits) to be treated in one cycle...
So the transaction will cost a lot !
To avoid deadlock you must have transactions that are the quickest as
possible. This is not the way you are engaged...
A +
Gabe Moothart a écrit :
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no reason
> to keep really old log entries, and in order to keep the table size
> down, I wrote a trigger to delete all records older than x days (x=5 in
> this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the
> end of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
Frédéric BROUARD, MVP SQL Server, expert bases de données et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modélisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro,
I haven't tested it yet, so I'm not sure it will solve the deadlock, but
your modified trigger code is much more elegant than what I had been
using. Thanks!
Gabe
> Hi
> first : using a cursur in a trigger is a very bad idea.
> Remember while you are inside the trigger code you are IN the transaction.
> second : trigger act once only even if the SQL statement that fired it
> take one million rows. So the code posted wont work in this case !
> The trigger code must not have variable inside and must be only write
> with "sets" code (SQL statements).
> You can code it this way :
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DELETE FROM StoreCommunicationLog
> FROM StoreCommunicationLog S
> INNER JOIN inserted i
> ON S.StoreKey = i.StoreKey
> WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
> GO
> Third : if StoreKey is the primary key of your table, having an
> uniqueidentifier type as the primary key col type is not a good choice
> to have some performances. GUID is a 32 byte (256 bits) data so the CPU
> must load this data with 8 cycles... So the choice of your key is about
> 8 to 16 times less quick than a simple integer wich is exactly the max
> CPU word (32 bits) to be treated in one cycle...
> So the transaction will cost a lot !
> To avoid deadlock you must have transactions that are the quickest as
> possible. This is not the way you are engaged...
> A +
>
> Gabe Moothart a écrit :
>> Hello,
>> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
>> have a windows app that polls a web-service every few seconds, and the
>> web service logs that communication in a table. Since there's no
>> reason to keep really old log entries, and in order to keep the table
>> size down, I wrote a trigger to delete all records older than x days
>> (x=5 in this case).
>> Shortly after adding the trigger, I started getting frequent deadlock
>> errors. The table (when pruned by the trigger) is about 100k records
>> or so. Also, there is more than one copy of the windows app polling
>> the web-service, so two polling events could occur at the same time.
>> I've included the source for both the stored proc and the trigger
>> below. At first I thought the problem had to do with the "select" stmt
>> at the end of the stored proc, but adding the (NOLOCK) hint did not
>> solve the problem.
>> Any idea what might be causing the deadlock?
>> TIA,
>> Gabe
>>
>> -- 8< --
>> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
>> @.StoreKey uniqueidentifier,
>> @.dateAdded datetime,
>> @.Source nvarchar(100)
>> AS
>> INSERT INTO dbo.[StoreCommunicationLog](
>> [StoreKey],
>> [dateAdded],
>> [Source]
>> ) VALUES (
>> @.StoreKey,
>> @.dateAdded,
>> @.Source
>> )
>> SELECT
>> [StoreCommunicationLogID],
>> [StoreKey],
>> [dateAdded],
>> [Source]
>> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
>> WHERE
>> [StoreCommunicationLogID] = @.@.IDENTITY
>> GO
>> -- 8< --
>> CREATE TRIGGER trig_StoreCommunicationLog
>> ON StoreCommunicationLog
>> FOR INSERT
>> AS
>> DECLARE @.StoreKey UNIQUEIDENTIFIER
>> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
>> DECLARE simpleCursor CURSOR
>> LOCAL
>> KEYSET
>> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
>> WHERE (StoreKey = @.StoreKey) AND
>> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
>> DECLARE @.id int
>> OPEN simpleCursor
>> FETCH LAST FROM simpleCursor
>> INTO @.id
>> CLOSE simpleCursor
>> DEALLOCATE simpleCursor
>> DELETE FROM StoreCommunicationLog
>> WHERE (StoreKey = @.StoreKey)
>> AND (StoreCommunicationLogID < @.id)
>> GO
>|||When I need to delete old rows from a log table, I run a scheduled job
every night which executes a stored procedure. Run more often if you
need to.
Delete table where date < dateAdd(day, 45, getdate() )
just an idea
Tom
Gabe Moothart wrote:
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no
> reason to keep really old log entries, and in order to keep the table
> size down, I wrote a trigger to delete all records older than x days
> (x=5 in this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records
> or so. Also, there is more than one copy of the windows app polling
> the web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger
> below. At first I thought the problem had to do with the "select" stmt
> at the end of the stored proc, but adding the (NOLOCK) hint did not
> solve the problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.sql

Deadlock problem with insert trigger

Hello,
I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
have a windows app that polls a web-service every few seconds, and the
web service logs that communication in a table. Since there's no reason
to keep really old log entries, and in order to keep the table size
down, I wrote a trigger to delete all records older than x days (x=5 in
this case).
Shortly after adding the trigger, I started getting frequent deadlock
errors. The table (when pruned by the trigger) is about 100k records or
so. Also, there is more than one copy of the windows app polling the
web-service, so two polling events could occur at the same time.
I've included the source for both the stored proc and the trigger below.
At first I thought the problem had to do with the "select" stmt at the
end of the stored proc, but adding the (NOLOCK) hint did not solve the
problem.
Any idea what might be causing the deadlock?
TIA,
Gabe
-- 8< --
CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
@.StoreKey uniqueidentifier,
@.dateAdded datetime,
@.Source nvarchar(100)
AS
INSERT INTO dbo.[StoreCommunicationLog](
[StoreKey],
[dateAdded],
[Source]
) VALUES (
@.StoreKey,
@.dateAdded,
@.Source
)
SELECT
[StoreCommunicationLogID],
[StoreKey],
[dateAdded],
[Source]
FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
WHERE
[StoreCommunicationLogID] = @.@.IDENTITY
GO
-- 8< --
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DECLARE @.StoreKey UNIQUEIDENTIFIER
SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
DECLARE simpleCursor CURSOR
LOCAL
KEYSET
FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey) AND
(ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
DECLARE @.id int
OPEN simpleCursor
FETCH LAST FROM simpleCursor
INTO @.id
CLOSE simpleCursor
DEALLOCATE simpleCursor
DELETE FROM StoreCommunicationLog
WHERE (StoreKey = @.StoreKey)
AND (StoreCommunicationLogID < @.id)
GOHi Gabe
Set up a trace to capture deadlock events, deadlock chains, batches and
statements, so you can see what processes are involved, and what statements
they executed leading up to the deadlock.
Also, why in the world is the trigger using a cursor?
There is no guarantee that the last row returned by the cursor has any
special significance.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Gabe Moothart" <gabe@.imaginesystems.net> wrote in message
news:%23XizNMFSGHA.5908@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the web
> service logs that communication in a table. Since there's no reason to
> keep really old log entries, and in order to keep the table size down, I
> wrote a trigger to delete all records older than x days (x=5 in this
> case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the end
> of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
>|||Kalen,
Thanks, I will do that. The trigger was actually not written by me, so I
don't know why a cursor was used. I'll take a look at cleaning it up.
Gabe

> Hi Gabe
> Set up a trace to capture deadlock events, deadlock chains, batches and
> statements, so you can see what processes are involved, and what statement
s
> they executed leading up to the deadlock.
> Also, why in the world is the trigger using a cursor?
> There is no guarantee that the last row returned by the cursor has any
> special significance.
>|||Hi
first : using a cursur in a trigger is a very bad idea.
Remember while you are inside the trigger code you are IN the transaction.
second : trigger act once only even if the SQL statement that fired it
take one million rows. So the code posted wont work in this case !
The trigger code must not have variable inside and must be only write
with "sets" code (SQL statements).
You can code it this way :
CREATE TRIGGER trig_StoreCommunicationLog
ON StoreCommunicationLog
FOR INSERT
AS
DELETE FROM StoreCommunicationLog
FROM StoreCommunicationLog S
INNER JOIN inserted i
ON S.StoreKey = i.StoreKey
WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
GO
Third : if StoreKey is the primary key of your table, having an
uniqueidentifier type as the primary key col type is not a good choice
to have some performances. GUID is a 32 byte (256 bits) data so the CPU
must load this data with 8 cycles... So the choice of your key is about
8 to 16 times less quick than a simple integer wich is exactly the max
CPU word (32 bits) to be treated in one cycle...
So the transaction will cost a lot !
To avoid deadlock you must have transactions that are the quickest as
possible. This is not the way you are engaged...
A +
Gabe Moothart a crit :
> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no reason
> to keep really old log entries, and in order to keep the table size
> down, I wrote a trigger to delete all records older than x days (x=5 in
> this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records or
> so. Also, there is more than one copy of the windows app polling the
> web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger below.
> At first I thought the problem had to do with the "select" stmt at the
> end of the stored proc, but adding the (NOLOCK) hint did not solve the
> problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
Frdric BROUARD, MVP SQL Server, expert bases de donnes et langage SQL
Le site sur le langage SQL et les SGBDR : http://sqlpro.developpez.com
Audit, conseil, expertise, formation, modlisation, tuning, optimisation
********************* http://www.datasapiens.com ***********************|||SQLpro,
I haven't tested it yet, so I'm not sure it will solve the deadlock, but
your modified trigger code is much more elegant than what I had been
using. Thanks!
Gabe

> Hi
> first : using a cursur in a trigger is a very bad idea.
> Remember while you are inside the trigger code you are IN the transaction.
> second : trigger act once only even if the SQL statement that fired it
> take one million rows. So the code posted wont work in this case !
> The trigger code must not have variable inside and must be only write
> with "sets" code (SQL statements).
> You can code it this way :
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DELETE FROM StoreCommunicationLog
> FROM StoreCommunicationLog S
> INNER JOIN inserted i
> ON S.StoreKey = i.StoreKey
> WHERE ABS(DATEDIFF('dd', S.dateAdded, CURRENT_TIMESTAMP)) > 5
> GO
> Third : if StoreKey is the primary key of your table, having an
> uniqueidentifier type as the primary key col type is not a good choice
> to have some performances. GUID is a 32 byte (256 bits) data so the CPU
> must load this data with 8 cycles... So the choice of your key is about
> 8 to 16 times less quick than a simple integer wich is exactly the max
> CPU word (32 bits) to be treated in one cycle...
> So the transaction will cost a lot !
> To avoid deadlock you must have transactions that are the quickest as
> possible. This is not the way you are engaged...
> A +
>
> Gabe Moothart a crit :
>|||When I need to delete old rows from a log table, I run a scheduled job
every night which executes a stored procedure. Run more often if you
need to.
Delete table where date < dateAdd(day, 45, getdate() )
just an idea
Tom
Gabe Moothart wrote:

> Hello,
> I'm experiencing a deadlock problem with Sql Server 2000. Basically, I
> have a windows app that polls a web-service every few seconds, and the
> web service logs that communication in a table. Since there's no
> reason to keep really old log entries, and in order to keep the table
> size down, I wrote a trigger to delete all records older than x days
> (x=5 in this case).
> Shortly after adding the trigger, I started getting frequent deadlock
> errors. The table (when pruned by the trigger) is about 100k records
> or so. Also, there is more than one copy of the windows app polling
> the web-service, so two polling events could occur at the same time.
> I've included the source for both the stored proc and the trigger
> below. At first I thought the problem had to do with the "select" stmt
> at the end of the stored proc, but adding the (NOLOCK) hint did not
> solve the problem.
> Any idea what might be causing the deadlock?
> TIA,
> Gabe
>
> -- 8< --
> CREATE PROCEDURE dbo.StoreCommunicationLog_Insert
> @.StoreKey uniqueidentifier,
> @.dateAdded datetime,
> @.Source nvarchar(100)
> AS
> INSERT INTO dbo.[StoreCommunicationLog](
> [StoreKey],
> [dateAdded],
> [Source]
> ) VALUES (
> @.StoreKey,
> @.dateAdded,
> @.Source
> )
> SELECT
> [StoreCommunicationLogID],
> [StoreKey],
> [dateAdded],
> [Source]
> FROM dbo.[StoreCommunicationLog] WITH (NOLOCK)
> WHERE
> [StoreCommunicationLogID] = @.@.IDENTITY
> GO
> -- 8< --
> CREATE TRIGGER trig_StoreCommunicationLog
> ON StoreCommunicationLog
> FOR INSERT
> AS
> DECLARE @.StoreKey UNIQUEIDENTIFIER
> SELECT @.StoreKey = (SELECT StoreKey FROM Inserted)
> DECLARE simpleCursor CURSOR
> LOCAL
> KEYSET
> FOR SELECT StoreCommunicationLogID FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey) AND
> (ABS(DATEDIFF("dd",dateAdded,GETDATE())) > 5)
> DECLARE @.id int
> OPEN simpleCursor
> FETCH LAST FROM simpleCursor
> INTO @.id
> CLOSE simpleCursor
> DEALLOCATE simpleCursor
> DELETE FROM StoreCommunicationLog
> WHERE (StoreKey = @.StoreKey)
> AND (StoreCommunicationLogID < @.id)
> GO
E-mail correspondence to and from this address may be subject to the
North Carolina Public Records Law and may be disclosed to third parties.

Deadlock issue with 1204 results

I am consistently encountering deadlocks while running an app, with
replication running. My transactional replication insert proc always wins,
but my app fails. Below I have included the information from a 1204 trace,
but I don't really know how to read it. I believe that spid 63 has a page
level intent exclusive lock on my table, and a select statement spid 60
Share lock. I know these locks are incompatible, and this is the issue.
How do I find out more information on how to fix this?
What else should I look at?
How do I read the log results below?
2004-01-30 14:04:19.52 spid4 Wait-for graph
2004-01-30 14:04:19.52 spid4
2004-01-30 14:04:19.52 spid4 Node:1
2004-01-30 14:04:19.52 spid4 PAG: 9:3:671699 CleanCnt:1
Mode: IX Flags: 0x2
2004-01-30 14:04:19.52 spid4 Grant List 0::
2004-01-30 14:04:19.52 spid4 Owner:0x2cccf7c0 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:63 ECID:0
2004-01-30 14:04:19.52 spid4 SPID: 63 ECID: 0 Statement Type: INSERT
Line #: 8
2004-01-30 14:04:19.52 spid4 Input Buf: RPC Event:
sp_MSins_transactions;1
2004-01-30 14:04:19.52 spid4 Requested By:
2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:60 ECID:0 Ec:(0x29ECB550) Value:0x560354e0 Cost:(0/0)
2004-01-30 14:04:19.52 spid4
2004-01-30 14:04:19.52 spid4 Node:2
2004-01-30 14:04:19.52 spid4 TAB: 9:983674552 [] CleanCnt:1
Mode: S Flags: 0x0
2004-01-30 14:04:19.52 spid4 Grant List 2::
2004-01-30 14:04:19.52 spid4 Owner:0x56012440 Mode: S Flg:0x0
Ref:1 Life:00000001 SPID:60 ECID:0
2004-01-30 14:04:19.52 spid4 SPID: 60 ECID: 0 Statement Type: SELECT
INTO Line #: 44
2004-01-30 14:04:19.52 spid4 Input Buf: Language Event: goto byacct--
/*
delete from exceptions where account_number = '32022238' and symbol = 'ADCT'
delete from matched_transaction where account_number = '32022238'and symbol
= 'ADCT'
delete from unmatched_transaction where account_number = '32022238'and
2004-01-30 14:04:19.52 spid4 Requested By:
2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:63 ECID:0 Ec:(0x6DE8F590) Value:0x2cccfee0 Cost:(0/514)
2004-01-30 14:04:19.52 spid4 Victim Resource Owner:
2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:60 ECID:0 Ec:(0x29ECB550) Value:0x560354e0 Cost:(0/0)
2004-01-30 14:04:19.52 spid60 Error: 1205, Severity: 13, State: 61
2004-01-30 14:04:19.52 spid60 Transaction (Process ID 60) was deadlocked
on lock resources with another process and has been chosen as the deadlock
victim. Rerun the transaction..Captainkt
I assume you know what is a deadlock.
Do you have error handler in your applications with some delay which does
not give to user to wait until SQL Server would kill the victim?
Here are some tips on how to avoid deadlocking on your SQL Server:
Ensure the database design is properly normalized.
Have the application access server objects in the same order each time.
During transactions, don't allow any user input. Collect it before the
transaction begins.
Avoid cursors.
Keep transactions as short as possible. One way to help accomplish this is
to reduce the number of round trips between your application and SQL Server
by using stored procedures or keeping transactions with a single batch.
Another way of reducing the time a transaction takes to complete is to make
sure you are not performing the same reads over and over again. If you do
need to read the same data more than once, cache it by storing it in a
variable or an array, and then re-reading it from there.
Reduce lock time. Try to develop your application so that it grabs locks at
the latest possible time, and then releases them at the very earliest time.
if appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
Consider using the NOLOCK hint to prevent locking if the data being locked
is not modified often.
If appropriate, use as low of an isolation level as possible for the user
connection running the transaction.
Consider using bound connections.
"captainkt" <nothing@.fake.com> wrote in message
news:uPjQmma6DHA.2720@.TK2MSFTNGP09.phx.gbl...
> I am consistently encountering deadlocks while running an app, with
> replication running. My transactional replication insert proc always
wins,
> but my app fails. Below I have included the information from a 1204
trace,
> but I don't really know how to read it. I believe that spid 63 has a page
> level intent exclusive lock on my table, and a select statement spid 60
> Share lock. I know these locks are incompatible, and this is the issue.
> How do I find out more information on how to fix this?
> What else should I look at?
> How do I read the log results below?
> 2004-01-30 14:04:19.52 spid4 Wait-for graph
> 2004-01-30 14:04:19.52 spid4
> 2004-01-30 14:04:19.52 spid4 Node:1
> 2004-01-30 14:04:19.52 spid4 PAG: 9:3:671699 CleanCnt:1
> Mode: IX Flags: 0x2
> 2004-01-30 14:04:19.52 spid4 Grant List 0::
> 2004-01-30 14:04:19.52 spid4 Owner:0x2cccf7c0 Mode: IX
Flg:0x0
> Ref:1 Life:02000000 SPID:63 ECID:0
> 2004-01-30 14:04:19.52 spid4 SPID: 63 ECID: 0 Statement Type:
INSERT
> Line #: 8
> 2004-01-30 14:04:19.52 spid4 Input Buf: RPC Event:
> sp_MSins_transactions;1
> 2004-01-30 14:04:19.52 spid4 Requested By:
> 2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:60 ECID:0 Ec:(0x29ECB550) Value:0x560354e0 Cost:(0/0)
> 2004-01-30 14:04:19.52 spid4
> 2004-01-30 14:04:19.52 spid4 Node:2
> 2004-01-30 14:04:19.52 spid4 TAB: 9:983674552 [] CleanCnt:1
> Mode: S Flags: 0x0
> 2004-01-30 14:04:19.52 spid4 Grant List 2::
> 2004-01-30 14:04:19.52 spid4 Owner:0x56012440 Mode: S
Flg:0x0
> Ref:1 Life:00000001 SPID:60 ECID:0
> 2004-01-30 14:04:19.52 spid4 SPID: 60 ECID: 0 Statement Type:
SELECT
> INTO Line #: 44
> 2004-01-30 14:04:19.52 spid4 Input Buf: Language Event: goto
byacct--
> /*
> delete from exceptions where account_number = '32022238' and symbol ='ADCT'
> delete from matched_transaction where account_number = '32022238'and
symbol
> = 'ADCT'
> delete from unmatched_transaction where account_number = '32022238'and
> 2004-01-30 14:04:19.52 spid4 Requested By:
> 2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:63 ECID:0 Ec:(0x6DE8F590) Value:0x2cccfee0 Cost:(0/514)
> 2004-01-30 14:04:19.52 spid4 Victim Resource Owner:
> 2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:60 ECID:0 Ec:(0x29ECB550) Value:0x560354e0 Cost:(0/0)
> 2004-01-30 14:04:19.52 spid60 Error: 1205, Severity: 13, State: 61
> 2004-01-30 14:04:19.52 spid60 Transaction (Process ID 60) was
deadlocked
> on lock resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction..
>
>

Deadlock issue with 1204 results

I am consistently encountering deadlocks while running an app, with
replication running. My transactional replication insert proc always wins,
but my app fails. Below I have included the information from a 1204 trace,
but I don't really know how to read it. I believe that spid 63 has a page
level intent exclusive lock on my table, and a select statement spid 60
Share lock. I know these locks are incompatible, and this is the issue.
How do I find out more information on how to fix this?
What else should I look at?
How do I read the log results below?
2004-01-30 14:04:19.52 spid4 Wait-for graph
2004-01-30 14:04:19.52 spid4
2004-01-30 14:04:19.52 spid4 Node:1
2004-01-30 14:04:19.52 spid4 PAG: 9:3:671699 CleanCnt:1
Mode: IX Flags: 0x2
2004-01-30 14:04:19.52 spid4 Grant List 0::
2004-01-30 14:04:19.52 spid4 Owner:0x2cccf7c0 Mode: IX Flg:0x0
Ref:1 Life:02000000 SPID:63 ECID:0
2004-01-30 14:04:19.52 spid4 SPID: 63 ECID: 0 Statement Type: INSERT
Line #: 8
2004-01-30 14:04:19.52 spid4 Input Buf: RPC Event:
sp_MSins_transactions;1
2004-01-30 14:04:19.52 spid4 Requested By:
2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:60 ECID:0 Ec0x29ECB550) Value:0x560354e0 Cost0/0)
2004-01-30 14:04:19.52 spid4
2004-01-30 14:04:19.52 spid4 Node:2
2004-01-30 14:04:19.52 spid4 TAB: 9:983674552 [] CleanCnt:1
Mode: S Flags: 0x0
2004-01-30 14:04:19.52 spid4 Grant List 2::
2004-01-30 14:04:19.52 spid4 Owner:0x56012440 Mode: S Flg:0x0
Ref:1 Life:00000001 SPID:60 ECID:0
2004-01-30 14:04:19.52 spid4 SPID: 60 ECID: 0 Statement Type: SELECT
INTO Line #: 44
2004-01-30 14:04:19.52 spid4 Input Buf: Language Event: goto byacct--
/*
delete from exceptions where account_number = '32022238' and symbol = 'ADCT'
delete from matched_transaction where account_number = '32022238'and symbol
= 'ADCT'
delete from unmatched_transaction where account_number = '32022238'and
2004-01-30 14:04:19.52 spid4 Requested By:
2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: IX
SPID:63 ECID:0 Ec0x6DE8F590) Value:0x2cccfee0 Cost0/514)
2004-01-30 14:04:19.52 spid4 Victim Resource Owner:
2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
SPID:60 ECID:0 Ec0x29ECB550) Value:0x560354e0 Cost0/0)
2004-01-30 14:04:19.52 spid60 Error: 1205, Severity: 13, State: 61
2004-01-30 14:04:19.52 spid60 Transaction (Process ID 60) was deadlocked
on lock resources with another process and has been chosen as the deadlock
victim. Rerun the transaction..Captainkt
I assume you know what is a deadlock.
Do you have error handler in your applications with some delay which does
not give to user to wait until SQL Server would kill the victim?
Here are some tips on how to avoid deadlocking on your SQL Server:
Ensure the database design is properly normalized.
Have the application access server objects in the same order each time.
During transactions, don't allow any user input. Collect it before the
transaction begins.
Avoid cursors.
Keep transactions as short as possible. One way to help accomplish this is
to reduce the number of round trips between your application and SQL Server
by using stored procedures or keeping transactions with a single batch.
Another way of reducing the time a transaction takes to complete is to make
sure you are not performing the same reads over and over again. If you do
need to read the same data more than once, cache it by storing it in a
variable or an array, and then re-reading it from there.
Reduce lock time. Try to develop your application so that it grabs locks at
the latest possible time, and then releases them at the very earliest time.
if appropriate, reduce lock escalation by using the ROWLOCK or PAGLOCK.
Consider using the NOLOCK hint to prevent locking if the data being locked
is not modified often.
If appropriate, use as low of an isolation level as possible for the user
connection running the transaction.
Consider using bound connections.
"captainkt" <nothing@.fake.com> wrote in message
news:uPjQmma6DHA.2720@.TK2MSFTNGP09.phx.gbl...

quote:

---

> I am consistently encountering deadlocks while running an app, with
> replication running. My transactional replication insert proc always

---

wins,

quote:

---

> but my app fails. Below I have included the information from a 1204

---

trace,

quote:

---

> but I don't really know how to read it. I believe that spid 63 has a page
> level intent exclusive lock on my table, and a select statement spid 60
> Share lock. I know these locks are incompatible, and this is the issue.
> How do I find out more information on how to fix this?
> What else should I look at?
> How do I read the log results below?
> 2004-01-30 14:04:19.52 spid4 Wait-for graph
> 2004-01-30 14:04:19.52 spid4
> 2004-01-30 14:04:19.52 spid4 Node:1
> 2004-01-30 14:04:19.52 spid4 PAG: 9:3:671699 CleanCnt:1
> Mode: IX Flags: 0x2
> 2004-01-30 14:04:19.52 spid4 Grant List 0::
> 2004-01-30 14:04:19.52 spid4 Owner:0x2cccf7c0 Mode: IX

---

Flg:0x0

quote:

---

> Ref:1 Life:02000000 SPID:63 ECID:0
> 2004-01-30 14:04:19.52 spid4 SPID: 63 ECID: 0 Statement Type:

---

INSERT

quote:

---

> Line #: 8
> 2004-01-30 14:04:19.52 spid4 Input Buf: RPC Event:
> sp_MSins_transactions;1
> 2004-01-30 14:04:19.52 spid4 Requested By:
> 2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:60 ECID:0 Ec0x29ECB550) Value:0x560354e0 Cost0/0)
> 2004-01-30 14:04:19.52 spid4
> 2004-01-30 14:04:19.52 spid4 Node:2
> 2004-01-30 14:04:19.52 spid4 TAB: 9:983674552 [] CleanCnt:1
> Mode: S Flags: 0x0
> 2004-01-30 14:04:19.52 spid4 Grant List 2::
> 2004-01-30 14:04:19.52 spid4 Owner:0x56012440 Mode: S

---

Flg:0x0

quote:

---

> Ref:1 Life:00000001 SPID:60 ECID:0
> 2004-01-30 14:04:19.52 spid4 SPID: 60 ECID: 0 Statement Type:

---

SELECT

quote:

---

> INTO Line #: 44
> 2004-01-30 14:04:19.52 spid4 Input Buf: Language Event: goto

---

byacct--

quote:

---

> /*
> delete from exceptions where account_number = '32022238' and symbol =

---

'ADCT'

quote:

---

> delete from matched_transaction where account_number = '32022238'and

---

symbol

quote:

---

> = 'ADCT'
> delete from unmatched_transaction where account_number = '32022238'and
> 2004-01-30 14:04:19.52 spid4 Requested By:
> 2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: IX
> SPID:63 ECID:0 Ec0x6DE8F590) Value:0x2cccfee0 Cost0/514)
> 2004-01-30 14:04:19.52 spid4 Victim Resource Owner:
> 2004-01-30 14:04:19.52 spid4 ResType:LockOwner Stype:'OR' Mode: S
> SPID:60 ECID:0 Ec0x29ECB550) Value:0x560354e0 Cost0/0)
> 2004-01-30 14:04:19.52 spid60 Error: 1205, Severity: 13, State: 61
> 2004-01-30 14:04:19.52 spid60 Transaction (Process ID 60) was

---

deadlocked

quote:

---

> on lock resources with another process and has been chosen as the deadlock
> victim. Rerun the transaction..
>
>

---

sql

Deadlock Issue

Hi all,
I am using Visual Foxpro as front end and SQL Server at
backend. This is a Client/Server Database App. I am facing
deadlock problems in this environment. Please provice me
any help in this regard.
Thgank in advance.
That is a very open eneded question. You can read SQL Server 2000 Books
Online to understand, what a dead lock is, and how to avoid and troubleshoot
deadlocks in SQL Server Books Online. Believe me, there's a lot of
information in Books Online.
Additionally, you could try out the following link, to learn how to log
additional information about deadlocks into SQL Server error log:
http://vyaskn.tripod.com/administration_faq.htm#q14
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Muhammad Saleem" <anonymous@.discussions.microsoft.com> wrote in message
news:958501c49711$dbde1760$a601280a@.phx.gbl...
> Hi all,
> I am using Visual Foxpro as front end and SQL Server at
> backend. This is a Client/Server Database App. I am facing
> deadlock problems in this environment. Please provice me
> any help in this regard.
> Thgank in advance.

Deadlock detection

My app uses ADO 2.8 with C++.
Is there any way to retrieve sufficient information when a deadlock is
occurs between two ADO connections to an SQL server? It will be a great help
if I could get the last queries executed and current lock rangies of two
connections soon as a deadlock occurs.
Of course, I know there is no perfect deadlock-free database applcation.
However, my case is serious, because there are too many deadlocks and I
cannot still figure out the cause.
Please reply. Any replies are appreciated.
Thanks in advance.
Regards,
Hyun-jik BaeYou can investigate trace flag 1204. Search for 1204 in Books Online and Kno
wledgebase for more
information.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
news:OjlpkEkRFHA.252@.TK2MSFTNGP12.phx.gbl...
> My app uses ADO 2.8 with C++.
> Is there any way to retrieve sufficient information when a deadlock is occ
urs between two ADO
> connections to an SQL server? It will be a great help if I could get the l
ast queries executed and
> current lock rangies of two connections soon as a deadlock occurs.
> Of course, I know there is no perfect deadlock-free database applcation. H
owever, my case is
> serious, because there are too many deadlocks and I cannot still figure ou
t the cause.
> Please reply. Any replies are appreciated.
> Thanks in advance.
> Regards,
> Hyun-jik Bae
>|||Thanks! I will check it out right now.
Regards,
Hyun-jik Bae
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ua1J1KkRFHA.576@.TK2MSFTNGP15.phx.gbl...
> You can investigate trace flag 1204. Search for 1204 in Books Online and
> Knowledgebase for more information.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
> news:OjlpkEkRFHA.252@.TK2MSFTNGP12.phx.gbl...
>|||If you want the last query executed on a connection (PSID) you can use the
DBCC INPUTBUFFER. Not the most reliable because the buffer can change very
quickly.
You have more several options:
The simplest way to get the last query is to trace and log using the SQL
Profiler. Setup the profiler to capture:
TSQL Event
SQL:BatchComplete
Stored Procedures Event Category
RPC:Complete.
If using distributed transactions you may want to add
Transactions Event Category
Transactions:DTCTransactions
Transactions:TransactionLog
Check the BOL (SQL Books On Line) to make sure the correct data columns are
being logged.
Another way is to set up server side logging. This setup using the family of
sp_trace_XXXX stored procedures. If I remember correctly SQL profiler can
create a these server side scripts. Only the log file paths and NTFS
permissions need to be set.
If you have difficulty reproducing the blocking problem you can follow the
directions in the knowledge base article Q271509 "How to monitor SQL Server
2000 blocking"
http://support.microsoft.com/defaul...kb;en-us;224453 and Q224453
"INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems"
http://support.microsoft.com/defaul...kb;en-us;224453
The majority of reoccurring blocking problems is a symptom of a poor
relation database design. Typically the tables are not following 3NF rules
and are in some abnormal form. This is my recommended place to start.
"Bae,Hyun-jik" wrote:

> My app uses ADO 2.8 with C++.
> Is there any way to retrieve sufficient information when a deadlock is
> occurs between two ADO connections to an SQL server? It will be a great he
lp
> if I could get the last queries executed and current lock rangies of two
> connections soon as a deadlock occurs.
> Of course, I know there is no perfect deadlock-free database applcation.
> However, my case is serious, because there are too many deadlocks and I
> cannot still figure out the cause.
> Please reply. Any replies are appreciated.
> Thanks in advance.
> Regards,
> Hyun-jik Bae
>
>|||See if this helps:
Tracing Deadlocks
http://www.sqlservercentral.com/col...ngdeadlocks.asp
AMB
"Bae,Hyun-jik" wrote:

> Thanks! I will check it out right now.
> Regards,
> Hyun-jik Bae
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:ua1J1KkRFHA.576@.TK2MSFTNGP15.phx.gbl...
>
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?docsql wrote:
> On our dev server the app developers have been granted dbo access to
> their individual databases. They do not have sa rights on the Dev
> SQL Server. The problem is that when the app developers create new
> objects, they are the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects
> are qualified with the owner name in the code. For example
> user1.table.
> But when we rollout the changes in production, since the dba creates
> the objects the owner is the dbo and the application stops working. How
> can i reoslve this problem without giving sa access to the
> developer on the dev db servers?
Yes, by always fully qualifying object names:
Create Table dbo.MyTable(...)
David Gugick
Quest Software

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBO

On our dev server the app developers have been granted dbo access to their
individual databases. They do not have sa rights on the Dev SQL Server.
The problem is that when the app developers create new objects, they are the
owners.
For example, user1.testtable.
Since user1 has dbo access, is there a way that when user1 creates an object
it gets qualified as dbo.testtable?
The problem is that with some of these RAD tools the database objects are
qualified with the owner name in the code. For example user1.table.
But when we rollout the changes in production, since the dba creates the
objects the owner is the dbo and the application stops working. How can i
reoslve this problem without giving sa access to the developer on the dev db
servers?
Hi
I presume that when you say 'granted dbo access' you mean that you put the
users in the db_owner role.
Saying 'granted dbo access' is a misnomer, because dbo is a valid user name,
and unless a user has that name, they actually do not have dbo access.
You can check to see what user name someone is using by running
SELECT user_name()
By default, if you have permission to create an object, any objects you
created will be owned by you, and marked with your user name.
If you are in the db_owner role, your user name may be fred, and then your
objects be referenced as fred.someobject.
However, users in the db_owner role do have the right to give a different
owner to objects they create. So fred, in the db_owner role, can create a
table owned by the dbo user:
CREATE dbo.newtable ( column definitions ...)
A SQL Server database allows multiple objects of the same name, because the
uniqueness only needs to be on the ownername/objectname combination,. So the
fact that the RAD tools qualify with the owner name is essential. dbo.table1
and user1.table1 are two completely different objects. The tool has to make
sure it references the correct object.
If you try to access an object without specifying the owner, SQL Server has
to guess who owns the object. It will first guess that you (the current
user) own the object, then it will guess that the dbo owns the object. If
neither the current user or dbo owns the object you are referencing, you
will get an error about an unknown object.
Even though SQL Server can guess the owner correctly in some cases, I
suggest you get in the habit of always specifying the owner name, even if
the dbo owns the object. This habit will put you in good shape if you are
ever planning to upgrade to SQL Server 2005. There are also some performance
benefits in SQL 7/2000 to be gained when not relying on SQL Server to figure
out the owner.
So I'm not sure if the 'problem' you wanted to solve was that objects
created by your db_owner users were not owned by dbo, or that the RAD tools
always would qualify objects with their owners. Hopefully, the above will
help in either case. If not, please ask for elaboration.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"docsql" <docsql@.noemail.nospam> wrote in message
news:%23ingdRK4FHA.1596@.tk2msftngp13.phx.gbl...
> On our dev server the app developers have been granted dbo access to their
> individual databases. They do not have sa rights on the Dev SQL Server.
> The problem is that when the app developers create new objects, they are
> the owners.
> For example, user1.testtable.
> Since user1 has dbo access, is there a way that when user1 creates an
> object it gets qualified as dbo.testtable?
> The problem is that with some of these RAD tools the database objects are
> qualified with the owner name in the code. For example user1.table.
> But when we rollout the changes in production, since the dba creates the
> objects the owner is the dbo and the application stops working. How can i
> reoslve this problem without giving sa access to the developer on the dev
> db servers?
>

DBLIB with SSL

Hi All,
We have a SQL Server 2000 setup with a Certificate for
Server Side Encryption and this works fine with ADO
connections from a C++ desktop app. Connects fine and we
have verified encryption with MS Network Monitor. Another
C++ desktop app built with DBLIB will not connect and
dbopen() quietly fails. I know MS states that DBLIB is
not being "extended" but I believe that it is still
classified as "supported".
Are thre any fundamental reasons why DBLIB doesn't seem to
work with SSL or am I just doing something wrong?
Thanks in advance,
DougHi Doug,
Unfortunately, that is a bug with dblib. I filed it sometime ago and it
has not been fixed.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Hi Kevin,
Thanks for the reply... albeit bad news :-) I know that
you can't commit, but in light of the "not extending"
status of dblib, got any idea what the chances might be of
the bug being fixed? I really, really, really promise
that I won't try to hold you to it :-))
Thanks again,
Doug

>--Original Message--
>Hi Doug,
> Unfortunately, that is a bug with dblib. I filed it
sometime ago and it
>has not been fixed.
>Thanks,
>Kevin McDonnell
>Microsoft Corporation
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>.
>|||You'd have to open up a case with a SQL Support Engineer to get the ball
rolling. You'll also need a Strong Business case, why you need it fixed.
The bug number is 94129, opened by me. Since it is a bug, you shouldn't be
charged for opening the case.
Hope this helps,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.