Access 2003 deadlock issue

[Ron Hinds]

Access 2003 FE/SQL 2000 SP4 BE. I upsized this project a few years ago and
mostly everything has been working smoothly. But lately I have been getting
deadlocks on SQL server that are coming from Access queries, specifically
queries used to populate combo boxes. I have set the default record locking
for the project to "None" - this was one of the first changes I had to make
when I started this project. I find it strange that these combo box queries
are locking the underlying table because a combo is always a read-only
lookup, right? I have converted many of the combo box sources to SQL
Pass-Through Queries over time for performance's sake, but with hundreds of
forms and subforms it has been on a case-by-case basis when a performance
issue is noticed. A month or so ago when this locking problem started to pop
up, I couldn't solve it by just creating a SPT; I had to add the "WITH
NOLOCK" hint to the SQL of the SPT. The most recent issue I have yet to be
able to pin down which combo box is causing the problem, which is why I'm
posting a question here to see if anyone can explain why this has suddenly
become an issue after three years of not being an issue.

[Alex Dybenko]

Hi,
You start getting these errors because you getting more rows in your tables.
So make all read-only queries as pass-through with nolock option, also avoid
bound forms on big tables, open such forms with filter showing one or few
records
--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

Post by Ron Hinds
Access 2003 FE/SQL 2000 SP4 BE. I upsized this project a few years ago and
mostly everything has been working smoothly. But lately I have been
getting deadlocks on SQL server that are coming from Access queries,
specifically queries used to populate combo boxes. I have set the default
record locking for the project to "None" - this was one of the first
changes I had to make when I started this project. I find it strange that
these combo box queries are locking the underlying table because a combo
is always a read-only lookup, right? I have converted many of the combo
box sources to SQL Pass-Through Queries over time for performance's sake,
but with hundreds of forms and subforms it has been on a case-by-case
basis when a performance issue is noticed. A month or so ago when this
locking problem started to pop up, I couldn't solve it by just creating a
SPT; I had to add the "WITH NOLOCK" hint to the SQL of the SPT. The most
recent issue I have yet to be able to pin down which combo box is causing
the problem, which is why I'm posting a question here to see if anyone can
explain why this has suddenly become an issue after three years of not
being an issue.

[Armen Stein]

On Tue, 19 May 2009 09:22:05 +0400, "Alex Dybenko"

Post by Alex Dybenko
You start getting these errors because you getting more rows in your tables.
So make all read-only queries as pass-through with nolock option, also avoid
bound forms on big tables, open such forms with filter showing one or few
records

Also, if you're using joins, make sure that EACH table in the Select
clause has the NOLOCK hint, not just the first one.

I've written a slideshow on techniques for using Access as a
client-server front-end to SQL Server databases. It's called "Best of
Both Worlds" at www.JStreetTech.com/Downloads. It includes some
thoughts on when to use SQL Server, performance and security
considerations, concurrency approaches, and techniques to help
everything run smoothly.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

[Stefan Hoffmann]

hi Ron,

Post by Ron Hinds
Access 2003 FE/SQL 2000 SP4 BE. I upsized this project a few years ago and
mostly everything has been working smoothly.

The cases I saw where lacking of appropriate indices. Especially when
the tables where used in views with ORDER BY clause.


mfG
--> stefan <--

[Klaus Oberdalhoff]

Hi,

Locking

If using linked tabels and Acc 200x MDB and SQL Server 200x then

If you have a form (ie read only) which is based directly on an SQL
statement like
"SELECT * FROM blablabla WHERE ...;"
and if you have a SECOND form for editing the SAME data then SQL Server
creates a dead lock with Time-out ...

Having the SAME situation but using a stored query instead of a direct SQL
then there is no problem.

Maybe that info helps

mfg

Klaus

[Mary Chipman [MSFT]]

Interestingly enough you can still get deadlocks on RO data using a
NOLOCK query hint. I forget the details, but I do remember seeing that
roll by at some point in one of the SQL Server newsgroups.

Here's a couple of other things you can try:

--Cache static data in local Jet tables and use those for the combo
boxes. You can populate them once on startup, or on demand, instead of
continuously hitting the server every time a form opens.

--Fetch less data in the combos and make them "on demand". IOW, don't
load them when the form opens, let the user click in them and then
load the data from VBA code by executing pass-through queries, which
always return RO result sets.

If you have a bound form with 4 bound combos, then 5 queries are
running when the form loads. Not good when you have big tables and/or
a busy server and/or lots of users.

--Mary

On Mon, 18 May 2009 16:39:51 -0700, "Ron Hinds"

Post by Ron Hinds
Access 2003 FE/SQL 2000 SP4 BE. I upsized this project a few years ago and
mostly everything has been working smoothly. But lately I have been getting
deadlocks on SQL server that are coming from Access queries, specifically
queries used to populate combo boxes. I have set the default record locking
for the project to "None" - this was one of the first changes I had to make
when I started this project. I find it strange that these combo box queries
are locking the underlying table because a combo is always a read-only
lookup, right? I have converted many of the combo box sources to SQL
Pass-Through Queries over time for performance's sake, but with hundreds of
forms and subforms it has been on a case-by-case basis when a performance
issue is noticed. A month or so ago when this locking problem started to pop
up, I couldn't solve it by just creating a SPT; I had to add the "WITH
NOLOCK" hint to the SQL of the SPT. The most recent issue I have yet to be
able to pin down which combo box is causing the problem, which is why I'm
posting a question here to see if anyone can explain why this has suddenly
become an issue after three years of not being an issue.