Locking Issue - t-sql in Access 2010

Question

Fellow Accessors, I am running into a 3624 error, very randomly.

1 - Access 2010

2- GUI Access DB with no table and NO LINKED TABLES. It only connects to backend when it needs to and  then closes connection.

3 - Backend Access Db with all of the tables and queries.

4 - Client DB setting is Record Locking selected, Shared on opening, Record Lock is No LOCK.

5 - All VBA code is using DAO.

I can see where it is occuring and it is 'RANDOMLY" occuring on a Insert. Here is the code:

set mydb = Opendatabase(networksharelocation,False)

mysql = "Insert into StatusHistory (ID, status_code) Select TOP 1 ID, 6 from Statushistory where ......."

mydb.Execute myssql

Someone obviously has something locked. I am a SQL DBA but do know the inner workings of Access. So, my question to you is even though the settings are Record Locking, can another t-SQL query do a page lock in Access even though I have Access settings as they are? I was expecting for single row logic with where clauses, it should just grab the row and not the page.

Notice that I am doing an Insert - not an update nor delete. But I am reading the same table I am going to insert to and I just cannot believe that this should be causing a problem.  Another query would have to be causing this.

Thanks,

MG

Answer 1

AFAIK, Access doesn't have real row-level locking since it's a shared file-based database.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 2

Ok. thanks David

MG

Answer 3

I don't know if it will make any difference, but you can try setting the locking in the BE.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 4

HI Bill,

IF BE means backend, I did. The locking is identical on both.

Thx

MG

Answer 5

Yes BE = back End.

The locking could be due to the fact that you are running a SELECT on the same table you are inserting into. Is it necessary to do a SELECT TOP? Maybe you can get the ID and put it into a variable, close the recordset and then run your insert.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 6

Hi Bill,

Thanks. We thought of that too but the locking is happening in other locations which are not doing Select like my example but direct inserts with variables. This was just one of the examples. The real problem we believe lies in the network latency tied to other people inserting. I am used to SQL where this type of things does not have the same issues and can be handle via row locking not page locking as well as isolation options.

We are going to put retry logic in the error handler that only retries for certain errors.

MG

Answer 7

It sounds like you are going in the right direction. It just might be network issues.

And by the way, Access does do row locking.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 8

HI Bill,

Many thanks. One more question though. Others have told me that that it does NOT do row locking.  Below screen shot I found on another forum about this that indicates when it will escalate to page locks.

Access 2010 Help:

Open databases by using record-level locking
 
Makes record-level locking the default for the currently open database.
Clearing this check box makes page-level locking the default for the
open database. The choice you make applies to data in forms, datasheets, and code that uses a recordset object to loop through records. This option does not apply to action queries or code that performs bulk operations by using SQL statements.

this makes me believe ANYTIME I uses t-sql with db.Execute "....."  It will give me a page lock.

So, If I convert my t-sql EXECUTE to recordset logic (rs.update)  would this go to record locking?

Thanks, MG

Answer 9

>So, If I convert my t-sql EXECUTE to recordset logic (rs.update)  would this go to record locking?

Perhaps.  But it might be no harder also convert your back-end to SQL Server, and in VBA continue to use DAO but with ODBC, or switch to ADO and OleDB.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 10

HI David,

Totally agree. I am a SQL DBA and already have been pushing this towards a SQL solution. Unfortuantly that will be down the road.  I in the past couple of decacdes, I also have built a couple of solutions with Access front end communicateing to SQL with oledb. Works well.

So, you think changing the t-sql to recordset updates "might or might not" fix this?

MG

Answer 11

>you think changing the t-sql to recordset updates "might or might not" fix this?

Yes and will require code changes on the same order of magnitude as migrating to SQL Server which _will_ fix it.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 12

Agreed

Answer 13

HI Bill,

Many thanks. One more question though. Others have told me that that it does NOT do row locking.  Below screen shot I found on another forum about this that indicates when it will escalate to page locks.

Access 2010 Help:

Open databases by using record-level locking
 
Makes record-level locking the default for the currently open database.
Clearing this check box makes page-level locking the default for the
open database. The choice you make applies to data in forms, datasheets, and code that uses a recordset object to loop through records. This option does not apply to action queries or code that performs bulk operations by using SQL statements.

this makes me believe ANYTIME I uses t-sql with db.Execute "....."  It will give me a page lock.

So, If I convert my t-sql EXECUTE to recordset logic (rs.update)  would this go to record locking?

Thanks, MG

I don't know if a recordset would lock the entire table, but I've never seen a recordset insert fail. Again, I recommend getting the values into variables and then running an insert. I think the SELECT is where your problem is.

---

Bill Mosca
www.thatlldoit.com
http://tech.groups.yahoo.com/group/MS_Access_Professionals

Answer 14

thx Bill

MG

Answer 15

Well, I should point out that a page lock and a record lock are in THIS context the same thing!

The access data engine can ONLY lock a page during an update. When you turn on row locking, what the ACE engine does is FAKE the record locking by simply PADDING the size of the record to = a full page!

Since access is now forcing the record to be the same size as a page, then a page lock thus only contains one record and then “presto” we now have row locking without really having row locking.

I should point out that the result of the above feature can often result in MASSIVE bloating and file increasing for given data updates.

For example, I have a sample database of xx Megs in size. I run this SIMPLE code:

   Dim rst        As DAO.Recordset

   Dim i          As Long

   Dim t          As Single

  

   Set rst = CurrentDb.OpenRecordset("contacts")

  

   t = Timer

   Me.Text1 = "working..."

   DoEvents

   Do While rst.EOF = False

      rst.Edit

      i = i + 1

      rst!City = "ci " & i

      rst.Update

      rst.MoveNext           

   Loop

   t = Timer - t

   debug.print "done in " & t & " seconds"

   Beep

AFTER running above code, the database is really MUCH the same size. (6.7 megs). If I turn on row locking, then the above grows by a WHOPPING 120 megs in size! So file goes from 6.7 megs to 126 megs!

So row locking really does not exist – if you turn the feature on then access just pads all records to the size of one page and continues using page locking.

But to answer your question? No difference between using a sql update or a recordset update does not change the page locking used. (and we now know that row locking and page locking are the SAME thing in the given context of this discussion).

edit: the real lesson here is that turning on row locking can result in MASSIVE file bloating and increasing of size for simple update code. The reason of course is the row locking setting forces Access to EXPAND and pad the record to = a page size.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 16

HI Albert,

Many thanks. Well, now it comes to inserts. Since the record does not exist yet, I would interpret that the Access engine would have to lock a page with data in order to make the insert?

Thks MG.

Answer 17

It depends on if the page is new or not for that incoming new record.

Not a bad question!

The answer is interesting.

Page locking is NOT used when adding these new records. And the reason is that since the new record does not YET exist in the database, then no need to lock the NEW page that is currently being filled up when adding those new records – the page not written out until its full of records but that don’t matter because the pages not yet saved so who cares since then the records don’t exist at this point in time (until the page is actually written to disk).

What this means is that adding (or appending ) new records into a NEW page means these new records are not EXPANDED to a page size and thus that huge bloat I talked about does not occur.

So for a record being edited, the expand occurs to one page size. And for starting inserts into an existing page, then again the record padding occurs. However, the instant we are adding new un-used pages, then records are not expanded in that new page since they don’t yet exist and thus don’t need to be locked and thus no need to be expanded.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Answer 18

Many Thanks Albert.

MG