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

William Hindman wdhindman at dejpolsystems.com
Sat Mar 7 11:14:00 CST 2009


...regular linking.

William

--------------------------------------------------
From: "Dan Waters" <dwaters at usinternet.com>
Sent: Saturday, March 07, 2009 11:03 AM
To: "'Access Developers discussion and problem solving'" 
<accessd at databaseadvisors.com>
Subject: Re: [AccessD] Page Locking Happening - But Set to Record Locking

> Hi William,
>
> I'd like to test this, but a question first...
>
> Are you using FE/BE scenario where the FE uses regular Access table links 
> to
> the BE?  Or is the connection between FE and BE made using an OLEDB
> provider?
>
> Dan
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of William Hindman
> Sent: Friday, March 06, 2009 10: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
>
>
> -- 
> 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