[AccessD] Connections and Performance

Drew Wutka DWUTKA at marlow.com
Fri Feb 2 12:49:34 CST 2007


The lock is on the .ldb, and yes, the lock is on the .ldb so Jet can see it,
but not be prevented from reading that part of the .mdb.

Drew

-----Original Message-----
From: JWColby [mailto:jwcolby at colbyconsulting.com] 
Sent: Friday, February 02, 2007 12:36 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connections and Performance

OK, I understand now.  The lock is not physical data in the file, but rather
using the OS' locking mechanisms.  Getting a "lock" is literally asking the
OS for a lock and getting a lock object from the OS.  Is the lock in
question a lock on the MDB or a lock on the LDB?  So the lock Is placed on
the LDB but is interpreted by jet as being on the equivalent byte range in
the MDB?

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Friday, February 02, 2007 1:08 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connections and Performance

A portion of the Jet whitepaper (for 3.51 and earlier, which they would kick
one out for 4.0.  A lot is the same, but there are some changes (like the
inability to see who actually is suspect in the db.)).

" Extended byte range locks are locks placed outside the physical boundaries
of a file - no data is ever physically locked. An example of this is placing
a lock at 10 million hex for a file that has a physical size of only 64
bytes. In other words, a lock is virtually placed at a location that does
not exist on the hard disk. This type of locking is used because extended
byte range locks are not limited by the size of the physical file, allowing
for locking algorithms that would not otherwise be possible. Also, placing
locks inside a data file would prevent other users from reading that data.
In the early dBASE days, a user could place a lock on a record located in
the data file that prevented everyone from reading that data - when printing
a report, for example."

So, even though the .ldb will never get larger then 16k, it is having locks
placed on bytes that don't exist on it.  Lots of locks.  So when jet goes to
read block X from an .mdb, it can read it whether there is a write lock on
it or not.  

Drew

-----Original Message-----
From: JWColby [mailto:jwcolby at colbyconsulting.com]
Sent: Friday, February 02, 2007 11:56 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connections and Performance

>Actual record locks on the database are stored in the .ldb too, but in 
>a
very odd way.  When I first read about it, I thought it was the oddest
thing.  Basically, the max size of an .ldb is going to be 16k.  (255*64).
However, there is a 'virtual' size of the .ldb.  If Jet needs to lock
something in the 25th megabyte of an .mdb, it creates a lock on that
position in the 'virtual .ldb', even though the .ldb isn't that big, file
read/write code can lock bits outside a file's actual space.  This prevents
any read/write locks on the actual .mdb.

Well you lost me down around the "virtual" part.  If the "virtual" part is
in the LDB file then it has to be inside of the physical LDB file.  The OS
will not allow writes to physical disk space that is not assigned to that
file.  If it did allow that then you would get the possibility of
overwriting data in areas of the disk that the file doesn't own.  The FAT
system (and the others as well) specifically assigns sectors to files using
a chaining mechanism.  While it is possible to work around that fat system
using low level calls to the bios, I doubt seriously that Access is doing
that.

Are you saying that it grows and shrinks the LDB file, placing data out past
the first 16 KB, and that area holds the lock info??  I always just assumed
that the lock file said "this user is ACTIVELY accessing the MDB RIGHT NOW",
and the actual locking info was stored inside of the MDB somewhere.  It
isn't apparent why an LDB file is even required.  The info stored in the LDB
could just as easily be stored inside the MDB as well.  The only reason I
can see is to allow external applications to see who is in the database
without having to poke around in the MDB file itself.

Your explanation was uhhh... Virtual I suppose.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Drew Wutka
Sent: Friday, February 02, 2007 12:32 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connections and Performance

The Jet LDB white paper explains it.

Essentially the .ldb is going to be 64 bytes per user.  Each chunk is 32
bytes for the machine name, 32 bytes for the Access User name.  Then there
is a chunk of bytes in the .mdb itself, 2 or 4(not sure about Jet 4.0, I
haven't seen a white paper on it) byte chunks, and there are 255 of them.
(The max number of users).  The first chunk (in the .mdb) relates to the
first 64 byte chunk in the .ldb.  The smaller chunks in the .mdb are changed
based on the users activity.  

Actual record locks on the database are stored in the .ldb too, but in a
very odd way.  When I first read about it, I thought it was the oddest
thing.  Basically, the max size of an .ldb is going to be 16k.  (255*64).
However, there is a 'virtual' size of the .ldb.  If Jet needs to lock
something in the 25th megabyte of an .mdb, it creates a lock on that
position in the 'virtual .ldb', even though the .ldb isn't that big, file
read/write code can lock bits outside a file's actual space.  This prevents
any read/write locks on the actual .mdb.

Drew

-----Original Message-----
From: JWColby [mailto:jwcolby at colbyconsulting.com]
Sent: Friday, February 02, 2007 10:31 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Connections and Performance

No, but the lock file does (LDB).  I have never really figured out how.  If
you open it in a text editor it is mostly empty.

John W. Colby
Colby Consulting
www.ColbyConsulting.com

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte Foust
Sent: Friday, February 02, 2007 11:20 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Connections and Performance

I've certainly had more than 5 FEs connected to a single BE.  The back end
doesn't keep track of connections in Access.

Charlotte Foust

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
Sent: Friday, February 02, 2007 5:58 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Connections and Performance

I've read several times that maintaining a connection between a FE and a BE
will increase the performance of the FE because it doesn't need to reconnect
before transferring data.  The connection here would be a bound form
connected by a table link to a table in the BE.

But, the connection limit for one BE is 5 FE's.  So, will maintaining
connections on more than 5 FE's reduce performance?  Seems logical, but I
was wondering if this is correct or is there more to it?

Thanks!

Dan Waters

--
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