Deadlocks problem when database files growing

We are running a very busy SQL Server 2000 Enterprise in the cluster "passive active" environment. Hundreds of transactions are going through and all of them are
logged in to a special database we've created. For an each real transaction we are getting around 10 records inserted in to this database.
I found that whenever the database grows its files, especially the log file, we're getting a lot of deadlocks which we are able to resolve only by failing over to another node.

Any suggestions would be appreciated.

Thanks,

DanHowdy

Is this a recent problem of a long term issue?
Sounds like an application design issue....I doubt he growing logfiles would cause the problem - they would just be a symptom of how busy the system is. Also, if the logs grow really quickly, its possible the app is holding open tables etc too long and then causing the deadlocks. Shorter tansactuions may help. I have used locking hint TABLOCKX to get around a lot of problems, but it MAY NOT be the best solution for you. Sounds very application specific.........

I assume you have plenty of disk space for the TEMPDB and the database files?

More info / background would be useful.

Cheers,

SG

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: could not perform retention-based meta data cleanup

Hi SQL Replication Gurus:

I got some issues in my production environment, so please help me out. The following is the message I got from the replication monitor and I don't what to at this point.

Appreciate you help.

Yong

==========================================================================================

Command attempted:

{call sp_mergemetadataretentioncleanup(?, ?, ?)}

Error messages:

The merge process could not perform retention-based meta data cleanup in database 'TT'. (Source: Merge Replication Provider, Error number: -2147199467)
Get help: http://help/-2147199467

Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. (Source: ply-db-svr1, Error number: 1205)
Get help: http://help/1205

Just restart the agent. This is a transient problem due to the deadlock.|||

Michael ,

Thank you for your response. I have tried restarting the sql server agent, the sql Server Engine, and even restarting the windows server. That did not resolve the problem at all.

Thanks.

Yong

|||

1) How many subcribers connecting to publisher?

2) What is the amount of data loaded?

3) What is your configuration on SQL Merge profiler?

Deadlock on communication buffer, Thread

In our environment, a large number of users are
simultaneously running same select query against database.
The query involves around 7 tables and one view that has
millions of rows. In summary, the query is resource
intensive. Sometimes this query fails with the error
message
"Your transaction has been chosen as a victim of deadlock
on Communication Buffer,Thread" .
My question is why should there be a deadlock involved in
a SELECT statement. Deadlock should happen in
update/insert statements that use transactions.
Any insights on this will be helpful.
Thanks.there is an article at BOL that can helpl with this issue.. TROUBLESHOOTING
DEADLOCKS
One thing is the communication buffer in your error message, the bol sugests
that this can be related to query paralelism
Try to start the DBCC TRACEON(1204) to isolate the deadlock cause.
HTH
--
Wandenkolk T. Neto
MCDBA , MCSE
Fundação Abrinq
www.fundabrinq.org.br
"Vinod" <vinoddua2000@.yahoo.com> escreveu na mensagem
news:01c301c3674e$6b278d10$a501280a@.phx.gbl...
> In our environment, a large number of users are
> simultaneously running same select query against database.
> The query involves around 7 tables and one view that has
> millions of rows. In summary, the query is resource
> intensive. Sometimes this query fails with the error
> message
> "Your transaction has been chosen as a victim of deadlock
> on Communication Buffer,Thread" .
> My question is why should there be a deadlock involved in
> a SELECT statement. Deadlock should happen in
> update/insert statements that use transactions.
> Any insights on this will be helpful.
> Thanks.

Deadlock

In a high traffic environment, deadlocks eventually occur as number of data processes increase. How can deadlocks be avoided, minimized and resolved. Please kindly provide scenario examples and samples of T-SQL code. Thanks much.

Check out this previous thread on deadlocks:http://forums.asp.net/862097/ShowPost.aspx. You might find something in there to help you, in particular this link:Tips for Reducing SQL Server Deadlocks.|||In your previous post, you said:
These 3 suggestions have all but eliminated deadlocking for me:
-- Keep transactions as short as possible
-- Reduce lock time
-- Consider using the NOLOCK hint
Did a full combination of the above eliminate deadlocking or not?
Please advise how to implement the 2nd and third suggestions with sample T-SQL code.
Thanks much.
|||

thuhue wrote:

In your previous post, you said:
These 3 suggestions have all but eliminated deadlocking for me:
-- Keep transactions as short as possible
-- Reduce lock time
-- Consider using the NOLOCK hint
Did a full combination of the above eliminate deadlocking or not?
Please advise how to implement the 2nd and third suggestions with sample T-SQL code.
Thanks much.

For my particular situation, yes, the above eliminated the deadlockproblems we were experiencing. #1 and #2 were for me the samething -- using transactions as late as possible and finishing them asearly as possible reduced the lock time and went a long way to reducingcontention. For the NOLOCK hint, this is how you use it:

SELECT someColumns FROM myTable WITH (NOLOCK) WHERE someCondition

See also:SQL Server Lock Contention Tamed: The Joys Of NOLOCK and ROWLOCK.

DBREINDEX takes database offline?

HI All,
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
Suchi
Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:

> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> No, it isn't offline in that sense.
>
>
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> Yes, except for the table currently being reindexes and having the lock.
>
> No
>
> Wait. Just as any type lf locking/blocking.
>
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
>

DBREINDEX takes database offline?

HI All,
Environment: SQL server2000, SP4, on win2003.
I have few quetions regarding DBCC DBREINDEX..
1. Reindex is offline operation means..takes the db offline?
we have optimzation job through maintenace plan. When it started, will
it takes the database offline by issuing the below command? I heard one DBA
saying this.
ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
What is my understanding till now is, it will take the table offline by
issuing xcluve lock.. is that correct?
2. Database is avaiable for use when reondex is happening?
3. When users are already connected, this optimation jobs starts, it will
kill the existing users?If no, if the user is having a lock on table, reindex
is also for the same table, reindex will wait for that user to release the
lock on the same object or it will kill the user?
4. It will allow new users while it is running?
Any one can throw some light on above points.
Thanks,
SuchiFirst, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
(Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
See inline below for comments on your questions:
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Suchi" <Suchi@.discussions.microsoft.com> wrote in message
news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> HI All,
>
> Environment: SQL server2000, SP4, on win2003.
> I have few quetions regarding DBCC DBREINDEX..
> 1. Reindex is offline operation means..takes the db offline?
No, it isn't offline in that sense.
> we have optimzation job through maintenace plan. When it started, will
> it takes the database offline by issuing the below command? I heard one DBA
> saying this.
> ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
DATABASE would be executed, then there's no way you can access the database and execute the DBCC
DBREINDEx commands.
> What is my understanding till now is, it will take the table offline by
> issuing xcluve lock.. is that correct?
Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
lock (from memory).
> 2. Database is avaiable for use when reondex is happening?
Yes, except for the table currently being reindexes and having the lock.
>
> 3. When users are already connected, this optimation jobs starts, it will
> kill the existing users?
No
> If no, if the user is having a lock on table, reindex
> is also for the same table, reindex will wait for that user to release the
> lock on the same object or it will kill the user?
Wait. Just as any type lf locking/blocking.
>
> 4. It will allow new users while it is running?
Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
same resource.
>
> Any one can throw some light on above points.
>
> Thanks,
> Suchi|||Thanks a lot Tibor..I am clear on REINDEX now..
"Tibor Karaszi" wrote:
> First, note that the command being executed (DBCC DBREINDEX) is one thing, and the environment
> (Maint Plan, your own scripts etc) from where you execute it is another thing. The environment might
> add some commands etc. For instance, in 2000 you have an option to "repair minor problems" for the
> DBCC CHECKDB, which makes the environment (Maint Plan) to set the database in single user mode.
> See inline below for comments on your questions:
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Suchi" <Suchi@.discussions.microsoft.com> wrote in message
> news:BF8AF05A-B75E-4FF2-9D7A-40013A5D7F82@.microsoft.com...
> > HI All,
> >
> >
> > Environment: SQL server2000, SP4, on win2003.
> >
> > I have few quetions regarding DBCC DBREINDEX..
> >
> > 1. Reindex is offline operation means..takes the db offline?
> No, it isn't offline in that sense.
>
> > we have optimzation job through maintenace plan. When it started, will
> > it takes the database offline by issuing the below command? I heard one DBA
> > saying this.
> >
> > ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE.
> Your dba is incorrect (unless some incredibly funky environment is used). Actually, if that ALTER
> DATABASE would be executed, then there's no way you can access the database and execute the DBCC
> DBREINDEx commands.
>
> >
> > What is my understanding till now is, it will take the table offline by
> > issuing xcluve lock.. is that correct?
> Correct. SQL Server will acquire an exclusive lock if the table has a clustered index, else a shared
> lock (from memory).
>
> >
> > 2. Database is avaiable for use when reondex is happening?
> Yes, except for the table currently being reindexes and having the lock.
>
> >
> >
> > 3. When users are already connected, this optimation jobs starts, it will
> > kill the existing users?
> No
>
> > If no, if the user is having a lock on table, reindex
> > is also for the same table, reindex will wait for that user to release the
> > lock on the same object or it will kill the user?
> Wait. Just as any type lf locking/blocking.
>
> >
> >
> > 4. It will allow new users while it is running?
> Having alock on a resrource will not prohibit new connections to the database. It will not prohibit
> connections execute queries. It will only cause blocking of there is a clonflicts for locks on the
> same resource.
>
> >
> >
> > Any one can throw some light on above points.
> >
> >
> > Thanks,
> > Suchi
>