[AccessD] Page Locking Happening - But Set to Record Locking

Jim Dettman jimdettman at verizon.net
Sat Mar 7 00:48:42 CST 2009


William,

<<...and just for the lunacy of such things, note that MS gave us record
level 
locking by simply expanding the locked record to fill an entire page ...thus

the bloat :(>>

 That cannot be the case. You can go back and forth between locking modes
without the database size changing.

  But that remains a valid work around for if you really need record
locking.  Just pad the record so that it occupies more then half a page and
volia; record level locking!

Jim.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
Sent: Friday, March 06, 2009 11:04 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking

"This does not affect the functionality of the 'Open Databases Using
Record-Level Locking' checkbox on the Advanced tab in Access options.  All
my databases, FE and BE, have this checked." dan

...afaik, there has been no change in dao 3.6 to provide such functionality 
on its own ...I'd be more than pleased to find I'm wrong.

if you are using dao 3.6 without first opening the db using ado and setting 
record level locking, the setting on the advanced tab will not provide 
record level locking by itself
...create a table with a single byte record so that both records fall within

the 4028 byte page size ...make two entries.
...make certain no one else is in the db since first in defines the locking 
for everyone else.
...open the recordset with dao ...edit either record, the other is locked 
regardless of the advanced option setting.
...open it with an ado connection, set the record level locking option, 
close the connection
...open the recordset with dao ...edit either record, the other is not 
locked IF the advanced option setting is record level locking AND the form 
you're working on is set to edited record locking ...less any one of the 
three and you will get page level locking iirc.
...there is by the way a granularity setting with ado that will get rid of 
most of the bloat seen with record level locking but there is a performance 
penalty with what is an already slow ado connection compared to dao ...I 
have that stashed in my code files somewhere since I don't use it.

...and just for the lunacy of such things, note that MS gave us record level

locking by simply expanding the locked record to fill an entire page ...thus

the bloat :(

...one of the ever growing reasons to move to SQLServer be's :(

William

--------------------------------------------------
From: "Dan Waters" <dwaters at usinternet.com>
Sent: Friday, March 06, 2009 6:25 PM
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking

> Hi Jim,
>
> I read through this KB and also the referenced
> http://msdn.microsoft.com/en-us/library/aa189633.aspx article.
>
>>From these, it looks pretty certain that record locking can't be changed
> programmatically during run-time by DAO.  Only ADO can do this, and only 
> for
> a connection object.
>
> This does not affect the functionality of the 'Open Databases Using
> Record-Level Locking' checkbox on the Advanced tab in Access options.  All
> my databases, FE and BE, have this checked.
>
> So, I suspect that what I was seeing was that someone had edited a memo
> field, and had not yet saved the changes, since those are handled via page
> locks regardless of the setting.  If this becomes an issue, perhaps I can
> switch to SQL Server with table links.
>
> Thanks!
> Dan
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Friday, March 06, 2009 3:23 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking
>
> Dan,
>
>  Here's the link to the article (http://support.microsoft.com/kb/306435)
> and William is 100% correct that it is not a bug in the traditional sense,
> but rather "by design"
>
>  I always refer to it as a bug though because I consider it to be broken.
> When you modify something as fundamental as locking in a database product,

> I
> think it's unconscionable when you don't have it working right out of the
> gate and you know about it!  Especially given that DAO was still the best
> object lib for working with JET databases.
>
> Jim.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
> Sent: Thursday, March 05, 2009 9:35 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking
>
> Wow!  That's a head-turner!
>
> I do have to say that I haven't seen this behavior in Access 2002 or 2003.
> So perhaps they did fix it in those versions.  But this client uses Access
> 2000.
>
> And yes - if you can give me a lead to the KB article I will be in your
> debt!  I don't know how but it's a good sentiment!  :-)
>
> Thanks!
> Dan
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Thursday, March 05, 2009 6:58 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking
>
> Dan,
>
>  Even though you are set for "record level" locking, not all operations 
> are
> done at that level.  Index updates, memo pages, DML operations, etc. are
> always done at page level.
>
>  Also the first user in must be set for record level locking otherwise
> everyone defaults to page level regardless of setting.
>
>  There is also a bug where DAO in code will not record level lock unless
> you first open a recordset in ADO.  There is a MSKB article on that which 
> I
> can dig out if you want.
>
> Jim.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
> Sent: Thursday, March 05, 2009 6:11 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Page Locking Happening - But Set to Record Locking
>
> Twice now I've seen what appears to be page locking in the BE mdb when 
> that
> file is set to record locking.  About 8 contiguous records show locking -
> reminds me of Access 97, but they are using Access 2000.
>
> Has anyone seen this before or know of a fix?
>
> Thanks!
> Dan
>
>
>
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list