![]() #define CONNECT_STR L" Provider = SQLNCLI. Change the Initial Catalog and Data Source to match your DB #import "c:\program files\common files\system\ado\msado27.tlb" no_namespace rename("EOF","adoEOF") TestSqlConnection.cpp : Defines the entry point for the console application. The CREATE TABLE is in the code, just add 2 rows with whatever int values you desire as the data. If you run this from 2 console windows at the same time, it will immediately deadlock (a RID deadlock). ![]() I put together this sample console app that repeatedly updates the same row. Additionally, pessimistic locking is the only way to guarantee that your solution reads the most current data, because one user can't change a record after another user has started to edit it. The main advantage of pessimistic locking is that after you have obtained a lock, you know that you won't encounter any locking conflicts as long as the record is locked. I just want to be able to go through a recordset, lock a row, change the data, update the row, unlock the row, and continue.Īccording to BOL, pessimistic locking should do exactly that. I'm not an ADO developer, but maybe it will be easier to isolate the problem in ADO (since it is built on top of OLEDB). So nothing should be blocked for very long. This is fine for this situation because the business rules say that you are supposed to grab the row, change the data, and update immediately. From the profiler I can see that this puts an X lock on the primary key (and everything else it needs) from the start which will block all other threads until the update completes (which is what I wanted). This also means that I don't have much control over the UPDATE statment as far as adding hints goes.īut, I was able to fix my deadlock problem by using an XLOCK in the SELECT at the beginning of the transaction. I will see if there is a way to make this smarter. It looks like IRowsetUpdate updates every column whether it has changed or not. The UPDATE has every column in the WHERE because I am using OLE DB (IRowsetUpdate). It doesn't show an S lock anywhere (unless I am missing something). I did not look closely at the XML - I was looking at the actual graph in Sql EM. Please click the Mark as Answer button if a post solves your problem! One way to stop the deadlocking is to use the WITH(UPDLOCK, HOLDLOCK) table hint on the update or WITH(XLOCK, HOLDLOCK) but these will increase blocking in the database which can impact performance. Is it necessary to have all of the columns in the WHERE clause for these updates, or can you just use the primary key to get the row to be updated? What is the Primary Key for this table? Can you post your table DDL for this table and it associated indexes? Then the Shared Lock is requests a convert to a Update Lock which will be blocked by the Exclusive Lock and you have your deadlock. The Update Lock is being requested to convert to a Exclusive Lock which is incompatible with the existing Shared Lock so it is blocked and waits on the Shared Lock to release. You have a Update lock and a shared lock. ![]() My main question is how do these 2 transactions get a U lock on the same resource at the same time? That is not supposed to happen. 00 (Intel X86) 18:15:01 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.1 (Build 2600: Service Pack 3) My next question: How can I control how and when Sql Server puts locks on the primary key? No matter what locking hint I provide, it always seems to lock the primary key in the same way (with a U lock that is increased to an So how are 2 transactions getting U locks on the primary key at the same time? If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock." ![]() Only one transaction can obtain an update (U) lock to a resource at a time. "To avoid this potential deadlock problem, update (U) locks are used. This is what I don't understand: according to BOL One of them tries to increase it to an X lock and the deadlock occurs. According to the deadlock graph, both transactions have U locks on the primary key at the same time. I have no problem protecting the row itself from concurrent access, but Sql Server is deadlocking while trying to update the primary key. I see that there is some contention between User and DeviceSession but I'm not sure where that's coming from.I have 2 threads running transactions that can update the same row in a table at the same time. I'm now trying to understand what's causing the deadlock I've never interpreted this kind of log before.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |