February 16, 2011 at 2:46 pm
I have a weekly DTS package that executes a stored procedure every week. This stored procedure inserts new records in my products table. While this DTS runs, users are simultaneously using an Access application that could be modifying or adding records to this same products table.
The stored procedure that inserts into the products table looks like this:
INSERT INTO tblProducts
SELECT vwView1.Field1, vwView1.Field2, etc
FROM vwView1
INNER JOIN vwView2 ON vwView1.ID = vwView2.ID
LEFT OUTER JOIN vwProducts ON vwView1.ID = vwProducts.ID ON blah, blah.
WHERE vwProducts.ID IS NULL
One day, our DTS failed at the point where this stored procedure was called. The error message said that the transaction could not complete because the resources were blocked by another process, and this process was chosen as the victim.
So naturally, I assume that one of the MS Access users were probably saving to this table at the same time.
My question is this: Why would an INSERT procedure fail? After all, it's inserting. It should not conflict with other users who are modifying or adding records to the same table. vwView1 and vwView2 are based on completely separate tables. I'm only questioning the LEFT OUTER JOIN vwProducts though. vwProducts is based on tblProducts so it's doing a LEFT OUTER JOIN with itself. By the way, another programmer wrote this procedure, so I'm not sure what he was doing.
What gets locked by the Access application in a case like this?
Thanks in advance
February 16, 2011 at 4:04 pm
What your process ran into is a deadlock. When SQL Server sees a deadlock happen, it chooses one of the processes as the deadlock victim and terminates it.
A deadlock is when you have two processes running, each holding a lock on a resource that the other process needs. For example:
ProcessA holds a lock on ResourceA
ProcessB holds a lock on ResourceB
ProcessB needs a lock on ResourceA - has to wait until ProcessA releases its lock on ResourceA
ProcessA needs a lock on ResourceB - has to wait until ProcessB releases its lock on ResourceB
Since neither process can finish and release the locks it has taken out, neither process can continue. They are now deadlocked and one of the processes needs to be terminated.
If this is happening frequently, then you need to enable the deadlock graph and possibly use a profiler trace to identify the problem. You can look this up in books online for further details.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 16, 2011 at 4:21 pm
Just a quick question, is this an ADP that connects to a SQL 2k5 backend, or an actual MS Access database? The error is SQL so I assume you're connecting, but just wanted to confirm.
An Insert, if large enough, will eventually give up taking a bunch of locks and just escalate itself to the table level. If you're inserting into the clustered index and need to split pages, this problem could happen too on page locks.
There's a number of reasons this could occur. It's not just a matter of a single record update/insert blocking one other, it's more likely they've got selects holding locks on records another one wants to update/insert and is selecting off a table the first one is trying to modify.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
February 16, 2011 at 9:18 pm
Yes, it is an ADP file that is connected to SQL 2005.
So, I'm understanding that even when a SELECT executes, the record gets locked?
February 16, 2011 at 10:03 pm
Ray Cacciatore (2/16/2011)
Yes, it is an ADP file that is connected to SQL 2005.So, I'm understanding that even when a SELECT executes, the record gets locked?
Yes, depending on isolation levels. Check out BOL (Books Online) under Transaction Isolation Level for more details. It opens a shared lock on the row(page/table) when it reads the record, and releases it at certain events after, depending on the level.
Now, you'd have to look into your ADP to determine what isolation levels it's using as well. I don't remember its default offhand (been a while since I dealt with MS Access), but there's a server default, a database default, and a connection default. Access will set a connection default, so it's part of what you'll need to look into.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply