Locking error between SQL Server 2005 and Access 2003

[MSAccessRookie]

When my application (Access 2003) tries to update one of the tables (SQL Server 2005), a locking condition sometimes occurs. The Activity Monitor reports that the two locked processes have the following codes.

LCK_M_S   PAG:8:1:5198
AND
LCK_M_IX  PAG:8:1:5198

Has anyone experienced this before, and/or can anyone help me to interpret the error message? Is there an interaction between Access and SQL Server that I am missing?

 

[SQL_Hell]

That is standard behaviour for sql server, everthing creates a lock of some type. Are these actually deadlocked processes and they show as blocking = 1 in activity monitor?

LCK_M_S is a shared lock, created by a select statement

LCK_N_IX is a lock that is about to become an exclusive lock, basically its about to write some data to the disk at which point it will upgrade to an exclusive lock during the data write.

Nothing you have shown here indicates a problem...

 

[MSAccessRookie]

That is standard behaviour for sql server, everthing creates a lock of some type. Are these actually deadlocked processes and they show as blocking = 1 in activity monitor?

LCK_M_S is a shared lock, created by a select statement

LCK_N_IX is a lock that is about to become an exclusive lock, basically its about to write some data to the disk at which point it will upgrade to an exclusive lock during the data write.

Nothing you have shown here indicates a problem...

I did not think that these was a problem (per se), but when they are both present, NOTHING ELSE WORKS! I was unaware about looking for the blocking = 1 as a method of problem determination. We will try again this morning, and I will let you know.

Assuming that blocking = 1, what would be the next logical step(s)?

 

[SQL_Hell]

Yep keep an eye out for 'blocking' and 'blocked by' columns...they are the deadlocked processes. The column that shows blocking but is not showing blocked by (should only be one) is the culprit and probably the cause.

Then make a note of the SPID number, then run the following (lets say blocking spid was 15)

dbcc inputbuffer(15)

This will give you the code thats being run at the point the process was deadlocked

 

[MSAccessRookie]

Yep keep an eye out for 'blocking' and 'blocked by' columns...they are the deadlocked processes. The column that shows blocking but is not showing blocked by (should only be one) is the culprit and probably the cause.

Then make a note of the SPID number, then run the following (lets say blocking spid was 15)

dbcc inputbuffer(15)

This will give you the code thats being run at the point the process was deadlocked

Where do I run the "dbcc inputbuffer(15)" command?

UPDATE:

I found out where to enter the command, and ran a test this afternoon. The result of the test indicated something interesting. When the Datasheet opens, only a small part of the 9000 records are actually loaded. If nothing else happens, then the table becomes locked and no one can write to it. Any attempt to write creates a deadlock situation with the datasheet process in control of the lock. However, if I force the datasheet to load all of the data by pressing the "goto the last record" icon on the bottom, the lock is released!

While I am pretty sure that i can get Access to go to the end and then reposition to the beginning (or any other record for that matter), effectively loading all of the records, I have to believe this is neither the correct solution, not the most efficient one. I am also not aware of a way to get the datasheet to load to the end.

QUESTION:

Is there a way to get Access to load the complete dataset when it is larger than the arbitrary minimum load size?

 

[SQL_Hell]

No that I know of, this is what I have been suspecting has been going on all along (in your other thread also).

Your form is returning records but doesn't return all the records so it holds the shared lock at sql server, then you try to update a record and sql server tries to create an exclusive lock this in turn makes the shared lock upgrade to an exclusive lock (lock escalation) then we have a deadlock.

I really don't understand why the user cant just search for one record an update accordingly, why does the whole data set need to be returned? surely the user isn't going to scroll through all 9000 records anyway so what's the point. Imagine how bad this problem will be if you have 2 million records?

When was this database last re-indexed?

 

[MSAccessRookie]

No that I know of, this is what I have been suspecting has been going on all along (in your other thread also).

Your form is returning records but doesn't return all the records so it holds the shared lock at sql server, then you try to update a record and sql server tries to create an exclusive lock this in turn makes the shared lock upgrade to an exclusive lock (lock escalation) then we have a deadlock.

I really don't understand why the user cant just search for one record an update accordingly, why does the whole data set need to be returned? surely the user isn't going to scroll through all 9000 records anyway so what's the point. Imagine how bad this problem will be if you have 2 million records?

When was this database last re-indexed?

I am pretty sure that the database is backed up and re-indexed every night (during the week), but I am not sure about Saturday and Sunday.

 

[MSAccessRookie]

Is a DeadLock not an Error?

All morning, I have been trying to find a way to identify that a Deadlock is occurring, (or is about to occur), and have not yet been successsful. Is it even possible to see the condition ahead of time, or have an ON Error handle it once it does occur? If so, then steps could be taken to avoid the deadlock.

 

[SQL_Hell]

You cannot do anything to identify whether a deadlock is about to occur, but you can capture when a deadlock does occur.

Here is code for that

select * into #tmp
from sysprocesses

select * from #tmp
where blocked = 1

Your ideas about handling the deadlock with an error routine are definitely not the way to go, the only to stop a deadlock is to kill the process that is blocking. If you kill processes then you are essentially compromising your data integrity, if you automate the killing of processes then your data could get in a real mess and you would have no idea how to rectify the problem.

The best approach is to make the code efficient enough to not make deadlocks in the first place and as we discussed before having each user return 9000 records every time they open an Access form and the way Access deals with that is your problem.

I still want to know what is blocking what? this is the first point of call when addressing this kind of problem.

 

[MSAccessRookie]

You cannot do anything to identify whether a deadlock is about to occur, but you can capture when a deadlock does occur.

Here is code for that

select * into #tmp
from sysprocesses
 
select * from #tmp
where blocked = 1

Your ideas about handling the deadlock with an error routine are definitely not the way to go, the only to stop a deadlock is to kill the process that is blocking. If you kill processes then you are essentially compromising your data integrity, if you automate the killing of processes then your data could get in a real mess and you would have no idea how to rectify the problem.

The best approach is to make the code efficient enough to not make deadlocks in the first place and as we discussed before having each user return 9000 records every time they open an Access form and the way Access deals with that is your problem.

I still want to know what is blocking what? this is the first point of call when addressing this kind of problem.

Sorry that I did not make my intentions clear in regards to what I was thinking about.

One thing that we have discovered is that these locks are always caused by a single process (so far at least), and it is always the Read-Only procedure that causes the lock. Furthermore, the Read-Only procedure still runs during the locking issues, and exiting the datasheet or form that is running the Read-Only procedure almost always frees up the system for the other users.

I was hoping to catch the error in advance and then create a msgBox telling the user that the record was unavailable for writing at this time, also telling them which user had the record locked (if that were possible). That way, there would be no deadlocks, and the users could sort out the problem for themselves.

 

[Banana]

Exactly what does this "Read-Only procedure" does?

If it was to generate a reporting, then it would probably be best to not run when there are users using the database.

Alternatively, (and I know diddly about MS SQL), you can redesign so that the records necessary for the report are fetched with low priority or something like that.

 

[MSAccessRookie]

Exactly what does this "Read-Only procedure" does?

If it was to generate a reporting, then it would probably be best to not run when there are users using the database.

Alternatively, (and I know diddly about MS SQL), you can redesign so that the records necessary for the report are fetched with low priority or something like that.

I wish I could redesign the problem away. The problem is based on the interaction between two queries:

The Read/Write Query qryPeopleAlphabetic5 has the following structure:

SELECT IIf(IsNull(L_Name),"",L_Name) & IIf(IsNull(L_Name),"",IIf(IsNull(F_Name),"",", ")) & IIf(IsNull(F_Name),"",F_Name) AS Expr1, Person_ID
FROM tblPeople
ORDER BY 1;

The Read Only Query qryPeopleAlphabetic has the following structure:

SELECT Expr1, Person_ID, DateAdded, Title, F_Name, M_Initial, 
L_Name, email, Notes, FormalName, SemiFormalName, CommonName
FROM ViewPeopleAlphabetic
ORDER BY Expr1;

qryPeopleAlphabetic calls the SQL Server View ViewpeopleAlphabetic, which has the following structure:

SELECTTOP 100 Percent
(
CASE
WHEN L_Name ISNULL THEN''
ELSE L_Name
END)+(
CASE
WHEN L_Name ISNULL THEN''
ELSE(
CASE
WHEN F_Name ISNULL THEN''
ELSE', '
END)
END)+(
CASE
WHEN F_Name ISNULL THEN''
ELSE F_Name
END)AS Expr1,
Person_ID,
DateAdded,
Title,
F_Name,
M_Initial,
L_Name,
email,
Notes,
(
CASE
WHEN Title ISNULL THEN''
ELSE Title +' '
END)+(
CASE
WHEN F_Name ISNULL THEN''
ELSE F_Name +' '
END)+ L_Name AS FormalName,
(
CASE
WHEN F_Name ISNULL THEN Title +' '
ELSE F_Name +' '
END)+ L_Name AS SemiFormalName,
CommonName,
SSMA_TimeStamp
FROM dbo.tblPeople

 

[Banana]

What a minute.

Are you calling the query from Access or within MS SQL?

If it's from Access, then you are sucking in *WAY* too much thing because you're forcing Jet to evaluate everything locally with Iif(Isnull())!!!

 

[MSAccessRookie]

What a minute.

Are you calling the query from Access or within MS SQL?

If it's from Access, then you are sucking in *WAY* too much thing because you're forcing Jet to evaluate everything locally with Iif(Isnull())!!!

The Read/Write query is processed in MS Access so that the recordset remains writeable.

The Read Only query is an MS Access query that calls an SQL Server View, and does not need Read/Write Access.

 

[Banana]

I understand that, but the point is that you're using functions that MS SQL doesn't understand and therefore Jet has to tell MS SQL "Send me everything and I'll do it myself." and thus you are sucking a lot of records, possibly the entire table and thus locking far more than what is necessary.

You will have to rewrite the query so that all processing is done on MS SQL, perhaps by using a stored procedure instead and returning a recordset that you need for read/write query.

 

[MSAccessRookie]

I understand that, but the point is that you're using functions that MS SQL doesn't understand and therefore Jet has to tell MS SQL "Send me everything and I'll do it myself." and thus you are sucking a lot of records, possibly the entire table and thus locking far more than what is necessary.

You will have to rewrite the query so that all processing is done on MS SQL, perhaps by using a stored procedure instead and returning a recordset that you need for read/write query.

I suspect that the query is getting the contents of the entire table. The purpose of the query is to allow addition of new records to the table, or to modify existing records. The latter requires access to the entire table to locate the record to modify.

 

[Banana]

I suspect that the query is getting the contents of the entire table.

It *is*.

Whenever you use Jet functions that MS SQL doesn't know; Iif(), IsNull() (I believe MS SQL uses IFNULL()?) you are forcing Jet to retrieve everything over the wire so it can process everything locally then showing you filtered recordset after it has evaluated all the criteria.

You want to make sure it's evaluated on server's side. To do that you need to use a stored procedure that can use MS SQL's native functions such as IFNULL() to evaluate the recordset in same way as you originally meant it only without sending the entire table to Jet. It should be MS SQL's job, not Jet's job to evaluate the data 90% of the time, the other 10% being where Jet is better suited than MS SQL (and are more likely to be just read-only).

I just realized one more thing- you said you wanted updateable recordset... This is probably why you didn't consider SP or PT. If this is indeed the case, then I strongly recommend that you use a narrow subset of the data. For example, giving it a where criteria to return, say, only last month's data and nothing before that will help reduce the amounts of data Jet need to evaluate locally and avoid locking too much rows. Alternatively, you can just use a SP, bind the form to it, then when you need to edit, click a button to enable editing and making the form unbound during edit mode, do what you need to do, then save it and return to bound state. Less work than outright bound form without the locking problem.

Also, have you read the whitepaper from MSDN regarding Jet and ODBC connectivity?

 

[MSAccessRookie]

It *is*.

1. Whenever you use Jet functions that MS SQL doesn't know; Iif(), IsNull() (I believe MS SQL uses IFNULL()?) you are forcing Jet to retrieve everything over the wire so it can process everything locally then showing you filtered recordset after it has evaluated all the criteria.
2. You want to make sure it's evaluated on server's side. To do that you need to use a stored procedure that can use MS SQL's native functions such as IFNULL() to evaluate the recordset in same way as you originally meant it only without sending the entire table to Jet. It should be MS SQL's job, not Jet's job to evaluate the data 90% of the time, the other 10% being where Jet is better suited than MS SQL (and are more likely to be just read-only).
3. I just realized one more thing- you said you wanted updateable recordset... This is probably why you didn't consider SP or PT. If this is indeed the case, then I strongly recommend that you use a narrow subset of the data. For example, giving it a where criteria to return, say, only last month's data and nothing before that will help reduce the amounts of data Jet need to evaluate locally and avoid locking too much rows. Alternatively, you can just use a SP, bind the form to it, then when you need to edit, click a button to enable editing and making the form unbound during edit mode, do what you need to do, then save it and return to bound state. Less work than outright bound form without the locking problem.
4. Also, have you read the whitepaper from MSDN regarding Jet and ODBC connectivity?

1. I think I understand what you are saying, but the current design of the application requires these statements to create a sorting key.
2. I would love to have the server evaluate it, but I need the resulting recordset to be writeable in case an update or addition is requested by the user.
3. Limiting the subset is not an option, since the users will need to see the entire contact list to determine the correct person to assign to each transaction type (WorkOrder, etc).
4. I do not believe that I have read the paper you refer to. Where can I find it?

 

[Banana]

[/LIST]

1. I think I understand what you are saying, but the current design of the application requires these statements to create a sorting key.
2. I would love to have the server evaluate it, but I need the resulting recordset to be writeable in case an update or addition is requested by the user.
3. Limiting the subset is not an option, since the users will need to see the entire contact list to determine the correct person to assign to each transaction type (WorkOrder, etc).
4. I do not believe that I have read the paper you refer to. Where can I find it?

Well, if you read the whitepaper, it will do wonder in illustrating how recordsets can be updateable, and let me assure you, having server evaluate it doesn't mean it can't be updateable. You just need to avoid using stuff that forces local evaluation.

You can get the paper here. Note that it was for 3.0 but MS has indicated that this is still good for 4.0.

 

[datAdrenaline]

Change qryPeopleAlphabetic5 SQL property from ....

SELECT IIf(IsNull(L_Name),"",L_Name) & IIf(IsNull(L_Name),"",IIf(IsNull(F_Name),"",", ")) & IIf(IsNull(F_Name),"",F_Name) AS Expr1, Person_ID
FROM tblPeople
ORDER BY 1;

To ..

SELECT (L_Name & "") & IIf(L_Name Is Null, F_Name, (", " + F_Name)) AS Expr1, Person_ID
FROM tblPeople
ORDER BY L_Name, F_Name;

The you avoid the VBA call to IsNull() ... the IIf() call is *probably* translated by ODBC and is not effecting you much.

But ... do take note that with out any criteria, you are pulling all the selected fields of all the records anyway.