Deadlocks & BEGIN/END TRANSACTION

[Leaf]

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, Leaf

[Erland Sommarskog]

Leaf (ra********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Leaf]

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

[Erland Sommarskog]

Leaf (ra********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Leaf]

Thanks for your kind reply. VERY helpful.