How do I avoid dead lock from "select" queries
-
I have a Delphi application that makes "select" queries only (via ADO) to SQLServer. The application is used by over 30 users. I get a "Dead Lock" error about once a week.
My understanding is that a dead lock can occur only if there are "update" activities. How does "select" queries dead lock each other? And how to avoid such deadlock?
The error message is "Transaction (Process ID 87) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction".
For your information, I am using Delphi ADO to make the "select" queries to the SQLServer. I use default settings of the ADO, and have no "begin transaction" or "commit" before or after the "select" query.
Any idea will be appreciated.
Thanks in advance
-
Check out SET TRANSACTION ISOLATION LEVEL in BOL and see whether that gives you what you want.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
That would do it, but be careful... setting the transaction isolation level in procs may sometimes cause a conflict for lock type. You could use the WITH (NOLOCK) hint after the table names in the SELECT, instead. Both methods allow "dirty reads".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
As you've stated deadlocks usually only occur if there are update activities. So there must be something else that is causing the locking. Maybe something on the client, or something extra in a stored procedure or table definition??
Without knowing more about the environment in which the deadlock is occurring, I wouldn't suggest fiddling with transaction levels and query hints to get around the problem. I'd do a bit more investigation to find out why the deadlocks are occuring in the first place.
Take a look at this article and see if anything suggested leads to a better solution,
http://support.microsoft.com/default.aspx?scid=kb;en-us;832524
--------------------
Colt 45 - the original point and click interface
-
could part of the problem be that the delphi application you are using is binding table objects to grids within the delphi application?
we had that exact issue here for a long time, because grids were being bound to tables,instead of to a recordset which would be updated later.
The binding to a table cause a lock on the table, and might stay in place for a long time until the end user actually pressed the cancel or update buttons, which commiteted or rolledback the changes in the bound grid.
could that be a piece of the issue?
Lowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! -
We bind the Delphi grid to an TADOQuery, which makes use 'select' query to retreive the data from different tables. Could the ADOQuery lock the tables?
James
-
Take a look at this discussion thread and see if it helps any.
--------------------
Colt 45 - the original point and click interface -
Using No Lock is the way to fight and the problem is in sql and not in any other side.
The sql server issues a lock while executiing a select query too. I am not aware what effect dose it have, but certainly would like to get rid of it.
The wait type when I run a select query from reporting services report is shown as "pageiolatch_sh" and "latch_ex". As far now, I am able to search and know that they are light weight, but not the way to overcome them.
No Lock seems to be the solution, but still the process shows as its being blocked.
Anticipating replies soon.
Thanks,
Jwalant Natvarlal Soneji
-
Jwalant, please start a new thread to post your problems instead of resurrecting threads that are over two years old.
Quite apart from the fact that waits and locks are two totally seperate things, "pageiolatch_sh" and "latch_ex" wait occur during physical i/o operations. They are an indication that the query is waiting for a physical i/o operation to complete.
In order of preference, you may need to,
a) tune your queries to avoid uneccessary reads which cause additional physical i/o,
b) add more memory so data is held in memory longer therefore reducing physical i/o,
c) add more physical disks to increase the i/o capacity of the server.
--------------------
Colt 45 - the original point and click interface
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply