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

Deadlock updating different rows

Here is the condensed version of my question: why do I get a deadlock
when multiple threads are updating different rows in the same table?
Details: I have a table with a clustered index spread across multiple
columns. I run multiple threads, each of which accesses a separate row
in the table. Nevertheless, I see deadlocks.
SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
is
requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
is
requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
It is my understanding that the KEY locks are essentially row-level
locks because with clustered indexes the data are leaf nodes of the
index. As you can see, each thread is requesting a lock held by the
other. The locks are on the same index but different rows (the long
hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
I can't understand why different threads updating distinct rows would
ever want to lock the same rows. Granted, the rows may be adjacent,
but should that cause the acquisition of locks on rows other than the
one being updated? I could understand a broader locking if INSERTs
were happening, but that is not the case.
Thanks for any help you can offer.Is the index unique? Please post the complete table DDL and UPDATE
statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135189033.769214.220820@.f14g2000cwb.googlegroups.com...
> Here is the condensed version of my question: why do I get a deadlock
> when multiple threads are updating different rows in the same table?
> Details: I have a table with a clustered index spread across multiple
> columns. I run multiple threads, each of which accesses a separate row
> in the table. Nevertheless, I see deadlocks.
> SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
> is
> requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
> SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
> is
> requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
> It is my understanding that the KEY locks are essentially row-level
> locks because with clustered indexes the data are leaf nodes of the
> index. As you can see, each thread is requesting a lock held by the
> other. The locks are on the same index but different rows (the long
> hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
> I can't understand why different threads updating distinct rows would
> ever want to lock the same rows. Granted, the rows may be adjacent,
> but should that cause the acquisition of locks on rows other than the
> one being updated? I could understand a broader locking if INSERTs
> were happening, but that is not the case.
> Thanks for any help you can offer.
>|||Dan,
Thanks for your reply. Index is unique. Here is the table definition:
create TABLE [Sum_Item_Revenue] (
[tendered_business_period_dim_id] [int] NOT NULL ,
[posted_business_period_dim_id] [int] NOT NULL ,
[event_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___event__5B78929E] DEFAULT (0),
[profit_center_dim_id] [int] NOT NULL ,
[misc_period_dim_id] [int] NOT NULL ,
[pay_type_dim_id] [int] NOT NULL ,
[emp_dim_id] [int] NOT NULL ,
[item_dim_id] [int] NOT NULL ,
[total_sales_gross_amount] [decimal](18, 4) NULL ,
[total_discount_amount] [decimal](18, 4) NULL ,
[ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___order__45FE52CB] DEFAULT (0),
CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
(
[tendered_business_period_dim_id],
[posted_business_period_dim_id],
[event_dim_id],
[profit_center_dim_id],
[misc_period_dim_id],
[pay_type_dim_id],
[emp_dim_id],
[item_dim_id],
[ordered_profit_center_dim_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
It turns out it's not just a straight UPDATE but a stored procedure. I
realize the stored procedure has the potential to do INSERTs but for
the testing I've been doing, it's all been updates, because the records
already exist. Here is the stored proc:
create procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
Declare @.count int
SELECT @.count = count(*)
FROM Sum_Item_Revenue
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
IF @.count = 0
INSERT INTO Sum_Item_Revenue
( tendered_business_period_dim_id ,
posted_business_period_dim_id ,
profit_center_dim_id ,
ordered_profit_center_dim_id ,
misc_period_dim_id ,
pay_type_dim_id ,
emp_dim_id ,
item_dim_id ,
total_sales_gross_amount ,
total_discount_amount
)
VALUES
( @.tendered_business_period_dim_id ,
@.posted_business_period_dim_id ,
@.profit_center_dim_id ,
@.ordered_profit_center_dim_id ,
@.misc_period_dim_id ,
@.pay_type_dim_id ,
@.emp_dim_id ,
@.item_dim_id ,
@.total_sales_gross_amount ,
@.total_discount_amount
)
ELSE
UPDATE Sum_Item_Revenue SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount ,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
I've removed some non-essential columns from the table for the purposes
of this posting, to remove clutter.
I call this routine from multiple threads, where each thread passes in
a unique profit_center_dim_id. Other values of the key are similar.
So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
SELECT is manifested by one of my SPIDs above attempting to obtain a
shared lock.
Thanks, rand|||Try this:
BEGIN TRAN
IF EXISTS(SELECT 1 FROM...WITH(UPDLOCK, HOLDLOCK) WHERE...)
BEGIN
UPDATE...
--error handling here
END
ELSE
BEGIN
INSERT...
--error handling here
END
COMMIT TRAN
WITH(UPDLOCK,HOLDLOCK) places an update range-lock on the table that is
about to be modified. This does not affect select concurrency, because
other transactions can obtain shared locks on rows that already have an
update lock. It only affects insert/update concurrency and not by much. It
will eliminate the deadlock that you're encountering.
The construct below doesn't take into account the fact that another
transaction can obtain a lock on the row to be updated between the SELECT
and the UPDATE or INSERT.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Brian, Thanks I'll give it a try. I should add that the lack of
transaction semantics within my stored procedure is because this
procedure is invoked from .NET code within BeginTransaction() and
Commit() using the default isolation level of Read Committed. Slightly
bigger picture: I'm multi-threading code that has heretofore been
single-threaded. The thing that has me baffled is why there is any
lock contention at all, given that different threads should be
accessing different rows. Unless my assumption is incorrect and the
locks I see are really not row-level, but are table- or page-level.|||First, I prefer to handle transaction processing within the stored
procedure. This makes it a lot easier to troubleshoot deadlocks and to
change code--for example, to implement optimistic concurrency.
Second, READ COMMITTED is good for reporting; for modifications, it is a
disaster waiting to happen. You should use REPEATABLE READ or preferably
SERIALIZABLE if the information you're reading will be used in a subsequent
modification within the same transaction. As a rule, Rows selected that may
be updated should have an update lock applied and held until the transaction
commits; rows selected that will not be updated but whose value will be used
either directly or indirectly as values that will be inserted or updated
should have a shared lock applied and held. This is extremely important to
keep garbage out of your database. Any change to the source data between
the SELECT and the UPDATE/INSERT renders the results you've just read out
stale, which can introduce incorrect information into the database. If the
update involves inserting or updating summary information, then you should
use SERIALIZABLE because an INSERT will cause the results to become stale.
READ COMMITTED doesn't prevent changes from occuring between the SELECT and
the UPDATE/INSERT, and REPEATABLE READ doesn't prevent new rows that meet
the criteria used for summarization from being inserted.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135217308.843942.88810@.z14g2000cwz.googlegroups.com...
> Brian, Thanks I'll give it a try. I should add that the lack of
> transaction semantics within my stored procedure is because this
> procedure is invoked from .NET code within BeginTransaction() and
> Commit() using the default isolation level of Read Committed. Slightly
> bigger picture: I'm multi-threading code that has heretofore been
> single-threaded. The thing that has me baffled is why there is any
> lock contention at all, given that different threads should be
> accessing different rows. Unless my assumption is incorrect and the
> locks I see are really not row-level, but are table- or page-level.
>|||I see that the event_dim_id column is part of the primary key but is not
included in the where clause of the SELECT or UPDATE. This could increase
the likelihood of your deadlocks.
Brian pointed out that you are vulnerable to changes between the SELECT and
INSERT/UPDATE. Since you run the proc is run as part of a transaction,
below is another 'UPSERT' technique that I like to use. I hard-coded a zero
value for event_dim_id in this example.
alter procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
SET NOCOUNT ON
INSERT INTO Sum_Item_Revenue
(
tendered_business_period_dim_id,
posted_business_period_dim_id,
profit_center_dim_id,
ordered_profit_center_dim_id,
misc_period_dim_id,
pay_type_dim_id,
emp_dim_id,
item_dim_id,
total_sales_gross_amount,
total_discount_amount
)
SELECT
@.tendered_business_period_dim_id,
@.posted_business_period_dim_id,
@.profit_center_dim_id,
@.ordered_profit_center_dim_id,
@.misc_period_dim_id,
@.pay_type_dim_id,
@.emp_dim_id,
@.item_dim_id,
@.total_sales_gross_amount,
@.total_discount_amount
WHERE NOT EXISTS
(
SELECT *
FROM Sum_Item_Revenue WITH (UPDLOCK, HOLDLOCK)
WHERE
tendered_business_period_dim_id =@.tendered_business_period_dim_id
AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
)
IF @.@.ROWCOUNT = 0
BEGIN
UPDATE Sum_Item_Revenue
SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id
=@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id
AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
END
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Thanks Dan & Brian. I will experiment. Any idea why different threads
accessing different rows should even be contending for resources at
all? Dan, event_dim_id is not significant since it is not used and
always has a default value of 0.|||Look at the lock information in your original post. It's not enough that
you're only modifying one row at a time. Your procedure also reads rows:
that's why you get deadlocks. Both SPIDs have exclusive locks on one row,
but before the transactions commit, they also are trying to obtain shared or
update locks on the other transaction's row. What's strange is that the
locks represented in the original post don't match what you would get from
your procedure. I suspect that there is another procedure involved. You
have an update lock, but the procedure you posted doesn't have
WITH(UPDLOCK). It is my understanding that update locks are only obtained
when an explicit locking hint is specified.
Is it possible that other statements are issued by the application. There
are many other things that could be causing the deadlocks. That's why I
prefer to encapsulate database updates in procedures, and whenever possible,
to handle any transaction processing within those procedures. It makes
troubleshooting much, MUCH easier.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>|||> Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
The event_dim_id column might not be significant from your perspective but
SQL Server can't make the assumption that only one row will be returned
unless you include it in your WHERE clause. Also, the column is badly
needed to use the primary key index effectively. Check out the details of
the SEEK operator in the query plan without and with event_dim_id:
--without event_dim_id: scans all values with specified
tendered_business_period_dim_id
--and posted_business_period_dim_id
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]),
WHERE:((((([Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id]
AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d]) AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id]) AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id]) AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id]) AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
--without event_dim_id: single row retrieved via s
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]
AND
[Sum_Item_Revenue].[event_dim_id]=0 AND
[Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id] AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d] AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id] AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id] AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id] AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
Not only will the inefficient plan hurt performance, it can contribute to
the likelihood of deadlocks.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>

Deadlock problem when using SQL server

I have a deadlock problem when using SQL server.
It seems that the deadlock occurs when several threads udpate and then query
different rows of the same table. I was able to reduce it to a simple
example:
A table containing named counters:
The table has 2 columns:
name varchar( 100 )
value int( 4 )
The table has a clustered primary key on the column 'name'.
To get a unique counter value the following SQL statement (in a transaction)
are executed:
UPDATE counters SET value = value + 1 WHERE name = ?;
SELECT value FROM counters WHERE name = ?;
The counters table contains about 100 rows.
The transaction isolation level is set to read committed.
When more than one thread at a time executes the SQL statements on different
rows, deadlocks occur.
The following observations were made during tests:
- When the threads all access the same row, everything works fine.
- The deadlocks do not occur when adding a 'with ( nolock )' hint to the
select.
My question now is: Is there a way to avoid the deadlocks without using hint
s?
I do not want to use hints, because we have an EJB application, in which we
get deadlocks in several similar cases, some of them beeing CMP Entity Beans
.
It would be possible to add hints to some statements, but I cannot control
the SQL statements generated for CMP Entity Beans.
Most of our customers are using Oracle as a database, and with Oracle no
deadlocks are occurring. Adding hints to SQL statements makes them database
dependant and I would like to avoid this.
Thanks in advance for any help.Hi
http://www.sql-server-performance.com/deadlocks.asp
"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
> I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
> The counters table contains about 100 rows.
> The transaction isolation level is set to read committed.
> When more than one thread at a time executes the SQL statements on
different
> rows, deadlocks occur.
> The following observations were made during tests:
> - When the threads all access the same row, everything works fine.
> - The deadlocks do not occur when adding a 'with ( nolock )' hint to the
> select.
> My question now is: Is there a way to avoid the deadlocks without using
hints?
> I do not want to use hints, because we have an EJB application, in which
we
> get deadlocks in several similar cases, some of them beeing CMP Entity
Beans.
> It would be possible to add hints to some statements, but I cannot
control
> the SQL statements generated for CMP Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle no
> deadlocks are occurring. Adding hints to SQL statements makes them
database
> dependant and I would like to avoid this.
> Thanks in advance for any help.|||"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
>I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
> query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
>
. . .
: Is there a way to avoid the deadlocks without using hints?
> I do not want to use hints, because we have an EJB application, in which
> we
> get deadlocks in several similar cases, some of them beeing CMP Entity
> Beans.
> It would be possible to add hints to some statements, but I cannot control
> the SQL statements generated for CMP Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle no
> deadlocks are occurring. Adding hints to SQL statements makes them
> database
> dependant and I would like to avoid this.
>
Are these two statements being run in a transaction?
If, in Oracle, you are running
BEGIN
UPDATE counters SET value = value + 1 WHERE name = ?;
SELECT value FROM counters WHERE name = ?;
END;
The equivilent in Sql Server is not|||"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
>I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
> query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
>
Are you wrapping these statements in an explicit transaction?
What is the index structure for the table?
Oracle and Sql Server are just different. In Oracle the statement
SELECT value FROM counters WHERE name = ?;
Generates no locks whatsoever, so I'm not sure you're going to get this to
work without some customization for the different databases.
David|||giespte wrote:
> I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and
> then query different rows of the same table. I was able to reduce it
> to a simple example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction) are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
> The counters table contains about 100 rows.
> The transaction isolation level is set to read committed.
> When more than one thread at a time executes the SQL statements on
> different rows, deadlocks occur.
> The following observations were made during tests:
> - When the threads all access the same row, everything works fine.
> - The deadlocks do not occur when adding a 'with ( nolock )' hint to
> the select.
> My question now is: Is there a way to avoid the deadlocks without
> using hints?
> I do not want to use hints, because we have an EJB application, in
> which we get deadlocks in several similar cases, some of them beeing
> CMP Entity Beans. It would be possible to add hints to some
> statements, but I cannot control the SQL statements generated for CMP
> Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle
> no deadlocks are occurring. Adding hints to SQL statements makes them
> database dependant and I would like to avoid this.
> Thanks in advance for any help.
Wrap them in a transaction.
David Gugick
Imceda Software
www.imceda.com|||"David Browne" wrote:

> "giespte" <giespte@.discussions.microsoft.com> wrote in message
> news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
> Are you wrapping these statements in an explicit transaction?
Yes.
> What is the index structure for the table?
The table has a clustered primary key on 'name', created with no additional
parameter. I suppose SQL Server uses a BTree as Default.
I remember that years ago, i had a performance problem with OpenIngres. A
hash Index improved the situation then. Is it possible to use hash indexes
with SQL Server ? Would they help in this situation ?
The Profiler logs a udpate key lock acquired and released for every row when
executing the update and a shared key lock acquired and released for every
row for the select. This looks like an index page scan to me.
Other locks ( Intended Update and Intended Share ) were set at table and
page level. The profiler skipped some log messages, so i have not been able
to analyze it in full detail, but it was lots of data to go through.
> Oracle and Sql Server are just different. In Oracle the statement
> SELECT value FROM counters WHERE name = ?;
> Generates no locks whatsoever, so I'm not sure you're going to get this to
> work without some customization for the different databases.
>
I know. That′s why i tried the nolock hint.
The thing that really surprised me was, that the deadlock occurred even
though the threads work on a separate data rows. There has to be some lockin
g
at page or table level.
Thanks for the prompt reply.
Peter

> David
>
>

Deadlock problem when using SQL server

I have a deadlock problem when using SQL server.
It seems that the deadlock occurs when several threads udpate and then query
different rows of the same table. I was able to reduce it to a simple
example:
A table containing named counters:
The table has 2 columns:
name varchar( 100 )
value int( 4 )
The table has a clustered primary key on the column 'name'.
To get a unique counter value the following SQL statement (in a transaction)
are executed:
UPDATE counters SET value = value + 1 WHERE name = ?;
SELECT value FROM counters WHERE name = ?;
The counters table contains about 100 rows.
The transaction isolation level is set to read committed.
When more than one thread at a time executes the SQL statements on different
rows, deadlocks occur.
The following observations were made during tests:
- When the threads all access the same row, everything works fine.
- The deadlocks do not occur when adding a 'with ( nolock )' hint to the
select.
My question now is: Is there a way to avoid the deadlocks without using hints?
I do not want to use hints, because we have an EJB application, in which we
get deadlocks in several similar cases, some of them beeing CMP Entity Beans.
It would be possible to add hints to some statements, but I cannot control
the SQL statements generated for CMP Entity Beans.
Most of our customers are using Oracle as a database, and with Oracle no
deadlocks are occurring. Adding hints to SQL statements makes them database
dependant and I would like to avoid this.
Thanks in advance for any help.
Hi
http://www.sql-server-performance.com/deadlocks.asp
"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
> I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
> The counters table contains about 100 rows.
> The transaction isolation level is set to read committed.
> When more than one thread at a time executes the SQL statements on
different
> rows, deadlocks occur.
> The following observations were made during tests:
> - When the threads all access the same row, everything works fine.
> - The deadlocks do not occur when adding a 'with ( nolock )' hint to the
> select.
> My question now is: Is there a way to avoid the deadlocks without using
hints?
> I do not want to use hints, because we have an EJB application, in which
we
> get deadlocks in several similar cases, some of them beeing CMP Entity
Beans.
> It would be possible to add hints to some statements, but I cannot
control
> the SQL statements generated for CMP Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle no
> deadlocks are occurring. Adding hints to SQL statements makes them
database
> dependant and I would like to avoid this.
> Thanks in advance for any help.
|||"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
>I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
> query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
>
. . .
: Is there a way to avoid the deadlocks without using hints?
> I do not want to use hints, because we have an EJB application, in which
> we
> get deadlocks in several similar cases, some of them beeing CMP Entity
> Beans.
> It would be possible to add hints to some statements, but I cannot control
> the SQL statements generated for CMP Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle no
> deadlocks are occurring. Adding hints to SQL statements makes them
> database
> dependant and I would like to avoid this.
>
Are these two statements being run in a transaction?
If, in Oracle, you are running
BEGIN
UPDATE counters SET value = value + 1 WHERE name = ?;
SELECT value FROM counters WHERE name = ?;
END;
The equivilent in Sql Server is not
|||"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
>I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
> query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
>
Are you wrapping these statements in an explicit transaction?
What is the index structure for the table?
Oracle and Sql Server are just different. In Oracle the statement
SELECT value FROM counters WHERE name = ?;
Generates no locks whatsoever, so I'm not sure you're going to get this to
work without some customization for the different databases.
David
|||giespte wrote:
> I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and
> then query different rows of the same table. I was able to reduce it
> to a simple example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction) are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
> The counters table contains about 100 rows.
> The transaction isolation level is set to read committed.
> When more than one thread at a time executes the SQL statements on
> different rows, deadlocks occur.
> The following observations were made during tests:
> - When the threads all access the same row, everything works fine.
> - The deadlocks do not occur when adding a 'with ( nolock )' hint to
> the select.
> My question now is: Is there a way to avoid the deadlocks without
> using hints?
> I do not want to use hints, because we have an EJB application, in
> which we get deadlocks in several similar cases, some of them beeing
> CMP Entity Beans. It would be possible to add hints to some
> statements, but I cannot control the SQL statements generated for CMP
> Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle
> no deadlocks are occurring. Adding hints to SQL statements makes them
> database dependant and I would like to avoid this.
> Thanks in advance for any help.
Wrap them in a transaction.
David Gugick
Imceda Software
www.imceda.com
|||"David Browne" wrote:

> "giespte" <giespte@.discussions.microsoft.com> wrote in message
> news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
> Are you wrapping these statements in an explicit transaction?
Yes.
> What is the index structure for the table?
The table has a clustered primary key on 'name', created with no additional
parameter. I suppose SQL Server uses a BTree as Default.
I remember that years ago, i had a performance problem with OpenIngres. A
hash Index improved the situation then. Is it possible to use hash indexes
with SQL Server ? Would they help in this situation ?
The Profiler logs a udpate key lock acquired and released for every row when
executing the update and a shared key lock acquired and released for every
row for the select. This looks like an index page scan to me.
Other locks ( Intended Update and Intended Share ) were set at table and
page level. The profiler skipped some log messages, so i have not been able
to analyze it in full detail, but it was lots of data to go through.
> Oracle and Sql Server are just different. In Oracle the statement
> SELECT value FROM counters WHERE name = ?;
> Generates no locks whatsoever, so I'm not sure you're going to get this to
> work without some customization for the different databases.
>
I know. That′s why i tried the nolock hint.
The thing that really surprised me was, that the deadlock occurred even
though the threads work on a separate data rows. There has to be some locking
at page or table level.
Thanks for the prompt reply.
Peter

> David
>
>

Deadlock problem when using SQL server

I have a deadlock problem when using SQL server.
It seems that the deadlock occurs when several threads udpate and then query
different rows of the same table. I was able to reduce it to a simple
example:
A table containing named counters:
The table has 2 columns:
name varchar( 100 )
value int( 4 )
The table has a clustered primary key on the column 'name'.
To get a unique counter value the following SQL statement (in a transaction)
are executed:
UPDATE counters SET value = value + 1 WHERE name = ?;
SELECT value FROM counters WHERE name = ?;
The counters table contains about 100 rows.
The transaction isolation level is set to read committed.
When more than one thread at a time executes the SQL statements on different
rows, deadlocks occur.
The following observations were made during tests:
- When the threads all access the same row, everything works fine.
- The deadlocks do not occur when adding a 'with ( nolock )' hint to the
select.
My question now is: Is there a way to avoid the deadlocks without using hints?
I do not want to use hints, because we have an EJB application, in which we
get deadlocks in several similar cases, some of them beeing CMP Entity Beans.
It would be possible to add hints to some statements, but I cannot control
the SQL statements generated for CMP Entity Beans.
Most of our customers are using Oracle as a database, and with Oracle no
deadlocks are occurring. Adding hints to SQL statements makes them database
dependant and I would like to avoid this.
Thanks in advance for any help.Hi
http://www.sql-server-performance.com/deadlocks.asp
"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
> I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
> The counters table contains about 100 rows.
> The transaction isolation level is set to read committed.
> When more than one thread at a time executes the SQL statements on
different
> rows, deadlocks occur.
> The following observations were made during tests:
> - When the threads all access the same row, everything works fine.
> - The deadlocks do not occur when adding a 'with ( nolock )' hint to the
> select.
> My question now is: Is there a way to avoid the deadlocks without using
hints?
> I do not want to use hints, because we have an EJB application, in which
we
> get deadlocks in several similar cases, some of them beeing CMP Entity
Beans.
> It would be possible to add hints to some statements, but I cannot
control
> the SQL statements generated for CMP Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle no
> deadlocks are occurring. Adding hints to SQL statements makes them
database
> dependant and I would like to avoid this.
> Thanks in advance for any help.|||"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
>I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
> query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
>
. . .
: Is there a way to avoid the deadlocks without using hints?
> I do not want to use hints, because we have an EJB application, in which
> we
> get deadlocks in several similar cases, some of them beeing CMP Entity
> Beans.
> It would be possible to add hints to some statements, but I cannot control
> the SQL statements generated for CMP Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle no
> deadlocks are occurring. Adding hints to SQL statements makes them
> database
> dependant and I would like to avoid this.
>
Are these two statements being run in a transaction?
If, in Oracle, you are running
BEGIN
UPDATE counters SET value = value + 1 WHERE name = ?;
SELECT value FROM counters WHERE name = ?;
END;
The equivilent in Sql Server is not|||"giespte" <giespte@.discussions.microsoft.com> wrote in message
news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
>I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and then
> query
> different rows of the same table. I was able to reduce it to a simple
> example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction)
> are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
>
Are you wrapping these statements in an explicit transaction?
What is the index structure for the table?
Oracle and Sql Server are just different. In Oracle the statement
SELECT value FROM counters WHERE name = ?;
Generates no locks whatsoever, so I'm not sure you're going to get this to
work without some customization for the different databases.
David|||giespte wrote:
> I have a deadlock problem when using SQL server.
> It seems that the deadlock occurs when several threads udpate and
> then query different rows of the same table. I was able to reduce it
> to a simple example:
> A table containing named counters:
> The table has 2 columns:
> name varchar( 100 )
> value int( 4 )
> The table has a clustered primary key on the column 'name'.
> To get a unique counter value the following SQL statement (in a
> transaction) are executed:
> UPDATE counters SET value = value + 1 WHERE name = ?;
> SELECT value FROM counters WHERE name = ?;
> The counters table contains about 100 rows.
> The transaction isolation level is set to read committed.
> When more than one thread at a time executes the SQL statements on
> different rows, deadlocks occur.
> The following observations were made during tests:
> - When the threads all access the same row, everything works fine.
> - The deadlocks do not occur when adding a 'with ( nolock )' hint to
> the select.
> My question now is: Is there a way to avoid the deadlocks without
> using hints?
> I do not want to use hints, because we have an EJB application, in
> which we get deadlocks in several similar cases, some of them beeing
> CMP Entity Beans. It would be possible to add hints to some
> statements, but I cannot control the SQL statements generated for CMP
> Entity Beans.
> Most of our customers are using Oracle as a database, and with Oracle
> no deadlocks are occurring. Adding hints to SQL statements makes them
> database dependant and I would like to avoid this.
> Thanks in advance for any help.
Wrap them in a transaction.
--
David Gugick
Imceda Software
www.imceda.com|||"David Browne" wrote:
> "giespte" <giespte@.discussions.microsoft.com> wrote in message
> news:8F3D976F-A89A-46F3-8D1A-873948405241@.microsoft.com...
> >I have a deadlock problem when using SQL server.
> >
> > It seems that the deadlock occurs when several threads udpate and then
> > query
> > different rows of the same table. I was able to reduce it to a simple
> > example:
> >
> > A table containing named counters:
> > The table has 2 columns:
> > name varchar( 100 )
> > value int( 4 )
> >
> > The table has a clustered primary key on the column 'name'.
> >
> > To get a unique counter value the following SQL statement (in a
> > transaction)
> > are executed:
> >
> > UPDATE counters SET value = value + 1 WHERE name = ?;
> > SELECT value FROM counters WHERE name = ?;
> >
> Are you wrapping these statements in an explicit transaction?
Yes.
> What is the index structure for the table?
The table has a clustered primary key on 'name', created with no additional
parameter. I suppose SQL Server uses a BTree as Default.
I remember that years ago, i had a performance problem with OpenIngres. A
hash Index improved the situation then. Is it possible to use hash indexes
with SQL Server ? Would they help in this situation ?
The Profiler logs a udpate key lock acquired and released for every row when
executing the update and a shared key lock acquired and released for every
row for the select. This looks like an index page scan to me.
Other locks ( Intended Update and Intended Share ) were set at table and
page level. The profiler skipped some log messages, so i have not been able
to analyze it in full detail, but it was lots of data to go through.
> Oracle and Sql Server are just different. In Oracle the statement
> SELECT value FROM counters WHERE name = ?;
> Generates no locks whatsoever, so I'm not sure you're going to get this to
> work without some customization for the different databases.
>
I know. That´s why i tried the nolock hint.
The thing that really surprised me was, that the deadlock occurred even
though the threads work on a separate data rows. There has to be some locking
at page or table level.
Thanks for the prompt reply.
Peter
> David
>
>

Deadlock on simple update related to clustered index

I am encountering a deadlock situation, with two threads
running two updates to the same table, if that table has a
clustered index on it that is NOT the primary key, and the
where clause of the UPDATE statement is on the primary
key. Very strange... Here is the test case:
create table x
(id int not null,
lastModified datetime not null,
altkey varchar(3) not null)
GO
CREATE UNIQUE CLUSTERED INDEX IAK_X ON x (altKey)
GO
alter table x add constraint pk_x primary key(id)
GO
insert into x
values (1, getdate(), 'XXX');
Then, from 2 ISQL threads, ran the following code...
begin tran
waitfor time '15:06' -- ensure they start at same time
update x
set lastModified = getdate()
where id = 1;
waitfor delay '000:00:10' -- wait to ensure lock occurs
update x
set lastModified = getdate()
where id = 1;
commit tran;
When I monitor the locks, the following happens:
Thread A successfully performs Update 1
Thread B is blocked as A has Exclusive lock on the row.
Thread B, however, successfully gets a KEY UPDATE lock on
PK_X
Thread A, when it tries to perform Update 2, is blocked
trying to get a KEY UPDATE lock on PK_X, causing the
deadlock.
Even if I then drop the clustered index with
DROP INDEX x.iak_x;
the problem remains.
However, if I NEVER create the clustered index. The
threads both work fine and never encounter a deadlock.
Neither transaction attempts to get a KEY UPDATE lock on
the primary key index PK_X.
We try to use clustered indexes on fields other than the
ID # (one-up number, but NOT an IDENTITY column in this
case), because we encountered deadlocks on Inserts in the
past.
Any help would be greatly appreciated.On Thu, 24 Jul 2003 14:02:58 -0700, "Corey" <chorton1@.austin.rr.com>
wrote:
>Any help would be greatly appreciated.
That's pretty humorous.
I've never gotten into the detailed guts of SQLServer like some around
here, but it sounds to me like you've hit a SQLServer bug. It looks
like SQLServer locks the data record in thread A, but allows thread B
to begin and get a lock on an index entry while waiting for the data,
and voila, deadlock. If that is the case, I'd call it a bug.
I assume it works correctly if you have the PK on id?
J.sql