I have a customer using our program with SQL server and is
occasionally getting a "Transactio n (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this? 5 4615
On Jun 22, 8:17 am, Altman <balt...@easy-automation.comw rote:
I have a customer using our program with SQL server and is
occasionally getting a "Transactio n (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?
Read "Analyzing Deadlocks with SQL Server Profiler" in BOL. http://sqlserver-tips.blogspot.com/
Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.
"Altman" <ba*****@easy-automation.comw rote in message
news:11******** **************@ k79g2000hse.goo glegroups.com.. .
>I have a customer using our program with SQL server and is
occasionally getting a "Transactio n (process ID xxxxx) was deadlocked
on lock resources with another process and has been chosen as the
deadlock victim." From what they are telling me, there shouldn't be
any deadlock happening as they say this happens when they invoicing in
a different program that is accessing a different database. Also the
error is happening on an SQL Select from a view and this select is
then showing data in an HTML table for the user. I don't think this
view should need to lock anything, I just want to read the data. Is
there anything I can do to fix this?
On Jun 23, 4:10 am, Erland Sommarskog <esq...@sommars kog.sewrote:
Oscar Santiesteban (o_santieste... @bellsouth.net) writes:
Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.
This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.
On Jun 26, 10:30 am, Altman <balt...@easy-automation.comw rote:
On Jun 23, 4:10 am, Erland Sommarskog <esq...@sommars kog.sewrote:
Oscar Santiesteban (o_santieste... @bellsouth.net) writes:
Try using
select * from table (NOLOCK)
where xxxx = xxxx
This will not lock the database as it reads.
This may on the other hand lead to that the query returns incorrect
results, which may even more seroius. There are situations where NOLOCK
is called for, but you need to understand the implications. If you
don't - don't try it.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarsk og.se
Books Online for SQL Server 2005 athttp://www.microsoft.c om/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.c om/sql/prodinfo/previousversion s/books.mspx
I Think that the nolock will work for me. I understand the
implications and I think that my program will be able to handle it.
What I would've liked better was something like read committed but
didn't lock records.
If you are on 2005, consider snapshot isolation. http://sqlserver-tips.blogspot.com This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Andrew Mayo |
last post by:
Here's a really weird one for any SQL Server gurus out there...
We have observed (SQL Server 2000) scenarios where a stored procedure
which
(a) begins a transaction
(b) inserts some rows into a table
(c) re-queries another table using a subquery which references the
inserted table (correlated or not)
|
by: Jenny Zhang |
last post by:
I am running OSDL-dbt1 - an e-commerce workload
(http://www.osdl.org/lab_activities/kernel_testing/osdl_database_test_suite/osdl_dbt-1/)
against PostgreSQL: 7.3.3. During the test, I saw a lot of messages in
the database log file:
NOTICE: _sc_id=0, _add_flag=0, _itemcount=0, _i_id=597, _pp_i_id=159,
c_id=32760
ERROR: deadlock detected
WARNING: Error occurred while executing PL/pgSQL function shopping_cart
WARNING: line 311 at SQL...
|
by: Zeng |
last post by:
Hi,
The bigger my C# web-application gets, the more places I need to put in the
tedious retrying block of code to make sure operations that can run into
database deadlocks are re-run (retried) 3-4 times and give up if after that
it's still in deadlock. I'm very sure that many experienced people out
there already deal with this issue somehow. Is there an alternative to it?
Thanks for your comments and suggestions.
|
by: Grant McLean |
last post by:
Hi
First a simple question ...
I have a table "access_log" that has foreign keys "app_id" and
"app_user_id" that reference the "application_type" and "app_user"
tables. When I insert into "access_log", the referential integrity
triggers generate these queries:
SELECT 1 FROM ONLY "public"."application_type" x
|
by: Hugo Flores |
last post by:
Hi,
I'm getting a deadlock on my database.
Let me first tell you that this is a test database on a Win XP
Professional.
The SP where I'm getting the deadlock is this:
PROCEDURE UpdateTestFields
@id_Test int,
| |
by: Robinson |
last post by:
Apologies for the cross post, but I'm not too sure which group this belongs
in. At least I didn't get responses in the MSDE groups yet. Anyway what I
want to do is create a simple pattern for dealing with deadlock situations
in my VB.NET program. So far, I've come up with something like this
(below). The idea is simply to detect the deadlock exception and iterate
the operation until it finally succeeds (or we've tried it 3 times). Am I...
|
by: cwho.work |
last post by:
Hi!
We are using apache ibatis with our MySQL 5.0 database (using innodb
tables), in our web application running on Tomcat 5. Recently we
started getting a number of errors relating to
java.sql.SQLException: Deadlock found when trying to get lock; Try
restarting transaction message from server: "Lock wait timeout
exceeded; try restarting transaction";
We get such errors generally on inserts or updates while applying a
|
by: ThunderMusic |
last post by:
Hi,
We have a web application developped in asp.net (I think it's not relevant,
but well, it's so you know)... Yesterday, we received the following message
"Transaction (Process ID 69) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction. "
The thing is, the query was a simple select with inner joins between 3
tables (like select fields from table1 inner join table2......
|
by: DennBen |
last post by:
I am doing an update to set a field value = anothe field value (in the
same table) where it is not supplied. I'm handling this in the
trigger, but am getting deadlocks.
Do you see anything wrong with this that would cause deadlocking?
ALTER TRIGGER
ON .
|
by: desmondgreen |
last post by:
We have a problem with transaction management/concurrency when using ADO to update a database on a SQL 2005 database. We have a test application, to isolate and demonstrate the problem, where a VB6 client application gets a recordset and performs a simple update within the scope of an ADO transaction. When running multiple instances of the test application, failure is very quickly reached with a 'Transaction (Process ID x) was deadlocked on lock...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
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: 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: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |