Hi,
We have a Java application that runs against a variety of backends
including Oracle and MSSql 2000 and 2005. Our application has a
handful of active tables that are constantly being inserted into, and
some of which are being selected from on a regular basis.
We have a purge job to remove unneeded records that every hour "DELETE
FROM <tableWHERE <datafield< <sometimestamp> ". This is how we are
purging because we need 100% up time, so we do so every hour. For
some tables the timestamp is 2 weeks ago, others its 2 hours ago. The
date field is indexed in some cases, in others it is not... the
DELETE is always done off of a transaction (autoCommit on), but
experimentation has shown doing it on one doesn't help much.
This task normally functions fine, but every once in a while the
inserts or counts on this table fail with deadlocks during the purge
job. I'm looking for thoughts as to what we could do differently or
other experience doing this type of thing, some possibilities include:
- doing a select first, then deleting one by one. This is a
possibility, but its SLOW and may take over an hour to do this so we'd
be constantly churning deleting single records from the db.
- freezing access to these tables during the purge job... our app
cannot really afford to do this, but perhaps this is the only option.
- doing an update of an "OBSOLETE" flag on the record, then deleting
by that flag... i'm not sure we'd avoid issues doing this, but its'
an option.
The failures happen VERY infrequently on sql2005 and much more
frequently on sql2000. Any help or guidance would be most
appreciated, thanks!
Bob 3  3908  bo******@gmail. com (bo******@gmail .com) writes:
We have a Java application that runs against a variety of backends
including Oracle and MSSql 2000 and 2005. Our application has a
handful of active tables that are constantly being inserted into, and
some of which are being selected from on a regular basis.
We have a purge job to remove unneeded records that every hour "DELETE
FROM <tableWHERE <datafield< <sometimestamp> ". This is how we are
purging because we need 100% up time, so we do so every hour. For
some tables the timestamp is 2 weeks ago, others its 2 hours ago. The
date field is indexed in some cases, in others it is not... the
DELETE is always done off of a transaction (autoCommit on), but
experimentation has shown doing it on one doesn't help much.
This task normally functions fine, but every once in a while the
inserts or counts on this table fail with deadlocks during the purge
job. I'm looking for thoughts as to what we could do differently or
other experience doing this type of thing, some possibilities include:
- doing a select first, then deleting one by one. This is a
possibility, but its SLOW and may take over an hour to do this so we'd
be constantly churning deleting single records from the db.
- freezing access to these tables during the purge job... our app
cannot really afford to do this, but perhaps this is the only option.
- doing an update of an "OBSOLETE" flag on the record, then deleting
by that flag... i'm not sure we'd avoid issues doing this, but its'
an option.
The failures happen VERY infrequently on sql2005 and much more
frequently on sql2000. Any help or guidance would be most
appreciated, thanks!
I would suggest that you start with posting the CREATE TABLE and
CREATE INDEX table for you table, and the exact statement that
you use. Please also indicate how the typical INSERT operation
looks like, assuming that the INSERT operation is the on the purge
is on conflict with.
One option you could consider is to a SET DEADLOCK_PRIORI TY LOW to
the purge job. If there is a deadlock, the purge is the one to go,
and not any other process.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx
On Jul 19, 3:13 pm, "bobdu...@gmail .com" <bobdu...@gmail .comwrote:
Hi,
We have a Java application that runs against a variety of backends
including Oracle and MSSql 2000 and 2005. Our application has a
handful of active tables that are constantly being inserted into, and
some of which are being selected from on a regular basis.
We have a purge job to remove unneeded records that every hour "DELETE
FROM <tableWHERE <datafield< <sometimestamp> ". This is how we are
purging because we need 100% up time, so we do so every hour. For
some tables the timestamp is 2 weeks ago, others its 2 hours ago. The
date field is indexed in some cases, in others it is not... the
DELETE is always done off of a transaction (autoCommit on), but
experimentation has shown doing it on one doesn't help much.
This task normally functions fine, but every once in a while the
inserts or counts on this table fail with deadlocks during the purge
job. I'm looking for thoughts as to what we could do differently or
other experience doing this type of thing, some possibilities include:
- doing a select first, then deleting one by one. This is a
possibility, but its SLOW and may take over an hour to do this so we'd
be constantly churning deleting single records from the db.
- freezing access to these tables during the purge job... our app
cannot really afford to do this, but perhaps this is the only option.
- doing an update of an "OBSOLETE" flag on the record, then deleting
by that flag... i'm not sure we'd avoid issues doing this, but its'
an option.
The failures happen VERY infrequently on sql2005 and much more
frequently on sql2000. Any help or guidance would be most
appreciated, thanks!
Bob
Read "Analyzing deadlocks with SQL Server Profiler", "How to resolve a
deadlock", "Resolving Deadlocks in SQL Server 2000". Consider
clustering your tables on your timestamp columns.
BTW, unlike Erland, we set deadlock priority to high so that our
purging interferes less with other activities.
Alex Kuznetsov, SQL Server MVP http://sqlserver-tips.blogspot.com/
On Jul 23, 9:03 am, "bobdu...@gmail .com" <bobdu...@gmail .comwrote:
We can try the clustered index, but i'm NOT convinced why this is
going to resolve the page lock issue... i've checked the execution
plan for some of our queries and it looks like it sorts first, then
does a clustered index scan of the ID. Is this really going to have
more page locks than if the index was on the date?
If your table is clustered on the criteria you use for purging, then
your historical data and your current data are physically stored on
different pages (if you provide some grace period between current and
historical). Also your purging touches as little pages as possible and
as such runs faster. This does not guarantee you will not have
deadlocks, but it might decrease their probability. Note that index
entries for current and historical data in NCIs will still intertwine,
so you still may get deadlocks.
Alex Kuznetsov, SQL Server MVP http://sqlserver-tips.blogspot.com/ This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics | |
by: AKS |
last post by:
I am getting lot of deadlocks in my application. As it is very complex
ti avoid deadlocks at this stage of application we have done few steps
to lessen the impact.
We have added retries after deadlock is capturted.
We have added select * from TABLE with (nolock) wherever possible.
But interestingly second step is not working. I have few simple select
statements where i am using nolock criteria still I am getting
deadlock victim error....
|
by: hd |
last post by:
Are there any standard approaches used to purge records from database
in merge replication senario ?
We are using merge replication between two sql server 2000 databases.
These databasess have differnt purging frequencies ( purging frequency
of subscriber database is 6 month where as that of publisher is year).
Is there anyway to purge data from subscriber and publisher without
getting these deletion reflected in replication ( basically...
|
by: Matt White |
last post by:
We've found deadlocks in the trace file that were not captured by our
Powerbuilder application. Some deadlocks are trapped or, at least,
reported to the user as a db error, and others are completely silent.
We've also seen evidence of strange data that would be explained by
unprocessed deadlocks, although we've not yet proven that the
unreported deadlocks are killing updates to the db.
Putting a raiserror into various parts of the same...
|
by: MikL |
last post by:
Hi all,
I'm regularly getting the "deadlocked..you're the victim" message when two
threads work on a table at the same time via JDBC. The two threads don't
update the same records. I suspect the cause is related to index or page
locks, and/or the fact that the UPDATE statements are doing table scans
because there's no index on the primary key (no, I'm not the DBA!)
1. Thread A selects some records to play with:
|
by: Marcus |
last post by:
Hello all,
I am trying to figure out when it is appropriate to use shared and
exclusive locks with InnoDB. Using shared locks (lock in share mode), I
can easily create a scenario with 2 clients that deadlocks - start 2
transactions, open 2 shared locks, and both try to insert a new row
before either commits.
Using exclusive locks (for update) I cannot come up with a scenario that
results in a deadlock, since the very nature of the...
| | |
by: Mike Carr |
last post by:
I am running into an issue. Recently I installed IBuySpy Portal and then converted the data source to odp.net. When debugging the app my machine would freeze or become really slow. I can reproduce it by:
Setting a breakpoint somewhere in the code, hitting F5, once the application has completely loaded hit the stop button, make a quick change and then select F5 again.
It appears that the debugger attaches to the previous aspnet_wp.exe...
|
by: salad |
last post by:
I was wondering how you handle active/inactive elements in a combo box.
Let's say you have a combo box to select an employee. Joe Blow has
been selected for many record however Joe has left the company and has
been flagged inactive. If you have a filter on the rowsource like
Where Active = True
then Joe's name would not show up in the combo list. This would be fine
if the combo is associated with a field that is null/0. It would not be...
|
by: minapatel |
last post by:
Hi,
I am trying to purge cases using an sql cursor:-
cursor all_cases is
cursor all_cases is
select c.id00_warehouse, c.id00_case_nbr, c.id00_status_flag, c.id00_dlm
from phpick00 a, chcart00 b, idcase00 c
where a.ph00_shipto = 'Telford'
|
by: John Rivers |
last post by:
There are many references to deadlock handlers that retry the
transaction automatically.
But IMO a deadlock is the result of a design flaw that should be
fixed.
My applications treat deadlocks like any other runtime error - they
are logged and fixed.
There seems to be a an opinion that deadlocks are inevitable !
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
| | |
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| | |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
