default record locking vs open dabases by using record-level locking

guys, what's the difference between these 2 options?

they seem to contradict.

so i put default record locking = no locks.

then right below it says open databases by using record-level locking.

isn't it an oxymoron in its more quintessential sense?

locking
developingprogrammerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
"No locks" is a mis-nomer.  What it means is "No Edit Locks" or in other words, it gives you optimistic locking.  That is the locking only occurs when you go to update the record (be it at page level or record level).

Edit record locks, or pessimitic locking, places locks as soon as you start to edit a record and don't let go until the update is done.

Also note that record level locking does not work unless you first open a recordset with ADO.   DAO 3.61 was finialized well before record level locking was added and not updated.

Last, record level locking only works for some operations.   Long Value pages (memo and OLE fields) and index operations all use page level locking no matter what.   DML operations are also locked on pages.

So I would not bother with it.  If you really need record level locking, there are other wasy to get it.

Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
developingprogrammerAuthor Commented:
whao, beautifully explained Jim!!

i see, so there are 2 concepts which sound the same. 1) record locking, 2) record-level locking

record locking:
no lock is optimistic locking
edited record is pessimistic locking
--------->all records<--------- what is this Jim? does it mean it locks all the records in the entire table?

record-level locking
page LEVEL locking locks 1 page of data - for access 2007 it's 4kB of data (cause unicode now)
record LEVEL locking is just the size of the record - however much space it takes up, no more no less.

this is correct Jim?

seems like microsoft used pretty obfuscating language with no easily referenceable documentation to expound on their parlance
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<--------->all records<--------- what is this Jim? does it mean it locks all the records in the entire table?>>

 Yes.

<<record-level locking
page LEVEL locking locks 1 page of data - for access 2007 it's 4kB of data (cause unicode now)
record LEVEL locking is just the size of the record - however much space it takes up, no more no less.>>

 Yes, that's correct, but there are a few twists with JET.  First is that a record cannot span a page.   Second is that all fields except memo and OLE fields are stored in the "fixed" portion of the record, unless the memo or OLE fields are less then 30 bytes.

  So trying to use record level locking when you have memo or OLE fields is almost impossible.   As soon as the fields are too big, they are moved to long value pages (LVP's) at which point, those pages are locked at page level no matter what.   So you just got shot in the foot and it's not even under your control.

 The other problem is that DAO was never updated and you must open a recordset (before anyone else) in a certain way with ADO in order for record level locking to work at all.   Last, there is nothing that tells you if record level locking is in effect or not.

 Record level locking was an add-on and an after thought.  It was not done well.  Well, that may be a bit harsh; they did add it on with no other impact, but it's not easy to use.

<<eems like microsoft used pretty obfuscating language with no easily referenceable documentation to expound on their parlance >>

 Yes, the "no locks" get everone.   Everyone believes it literially means no locking, but of course with any DBMS, that's impossible.  You must have some form of locking always to ensure the integrity of transactions (I mean consistently being able to perform record operations, not roll back/forward).

 But also keep in mind that Access has been around for twenty years now and has grown over that time.   It's not always possible to stay out of your own way when you keep adding on features.

Jim.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
One thing I should add; if you want "record level locking" the best way to achieve that is by padding records.

Since JET won't let a record span a page, you can use that to your advantage.   By adding enough text fields (filled, because their stored variable length), to ensure that the record size is larger then half a page, you'll never get more then one record per page and thus have record level locking even though the DB is locking on pages.

Wastes disk space sure, but disk space is cheap and the best part about this is you can do it selectively on a table by table basis.   It's not an all or nothing deal.

I've actually found over the years that it's very rare that you actually need record level locking and if you do, it's usually on no more then a handfull of main tables.

Jim.
0
developingprogrammerAuthor Commented:
Everyone believes it literially means no locking, but of course with any DBMS, that's impossible.

whao Jim! ok i know it's a silly comment from me, but previously i wasn't clear that ALL DMSes needs locks. but now i've learnt! = )

By adding enough text fields (filled, because their stored variable length),

ah! this is one thing that gets me - some tutorials i've watched recommend setting field size to just as much as you need. but i always thought that the text field is a variable length field - cause text is actually a concatenation of char right(?), so why do we need to determine field size?
f1or is the Text datatype fixed length and the memo datatype variable length?

Jim how do you know how much space a particular record is taking up?

and also Jim, i'm so sorry that i'm missing the point, but record level locking vs page level locking - if we fill up the record until it is just more than half the page - what's the benefit of that? it essentially becomes page level locking right?

what i'm thinking for the reason why you want to fill up the record till just more than half a page is if we want to implement record level locking and some problem tables have something we need to adjust, then we need to fill those records to > half a page. something along those lines. is that correct?

thanks so much for your help Jim!! = ))
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<ah! this is one thing that gets me - some tutorials i've watched recommend setting field size to just as much as you need. but i always thought that the text field is a variable length field - cause text is actually a concatenation of char right(?), so why do we need to determine field size?>>

  It goes to the integrity of the data.   If your supposed to have a ten character string max, and you allow for 50, there's nothing stopping anyone from entering more then 10.  That's the only reason.   Many set their text fields to 255 by default and don't worry about it.

<<or is the Text datatype fixed length and the memo datatype variable length?>>

Both are variable length.  The difference is text is limited to 255 max and is stored with the reset of the record.   Memo's are stored else where and can be 64K in length.

<<Jim how do you know how much space a particular record is taking up?>>

 You have to add up the field sizes and overhead.

<<it essentially becomes page level locking right?>>  

   Your allowing the DB to use page level locking, but in effect get record level locking on a table by table basis because no more then one record ever fits on a page.

   If you were using page level locking (and keep in mind, years ago there was no record level locking)  and had more then one record on a page, if the page was locked, you run into concurrency issues, especially if your using pessimistic locking (Edited records).

 User A calls up a record, starts to edit and goes to lunch.   User B, C, D.... can't edit any other record on that page because A has it locked.

 But now if there is one record per page, user A doesn't cause a problem.

 and pessimistic is better from a user standpoint.  If you use optimistic, then users can over write each others changes.

 User A starts to edit record 1 (no lock is taken yet).   User B starts to edit record 1.   User A saves record 1 (page is locked, record is updated, and lock is released).  

  Now B goes to save, but gets a dialog that says "another user has changed the record while you were editing.  You can overwrite the other users changes, discard your changes, or cancel the save".

 They never know what to do.

 So on high concurrency tables,   having a record more then half a page, using page level locking, and Edited Record locks (pessimistic locking), is the best setup.

Jim.
0
developingprogrammerAuthor Commented:
whao Jim, that was beautifully explained. = )

just a small typo that would confuse fellow beginners like me:
The difference is text is limited to 255 max and is stored with the reset of the record.

i believe you meant rest of the record. i saw stumped for awhile but figured it out. i'm sure there are others out there who are slightly greener than me so probably this would help! = )

Jim one last thing, could you share with me how to add up the field sizes with overhead? perhaps with an example if that's not too much to ask? thanks!! = ))
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Yes, that was supposed to be "rest", not "reset".

<<Jim one last thing, could you share with me how to add up the field sizes with overhead? perhaps with an example if that's not too much to ask? thanks!! = )) >>

See:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_20078308.html#a5849926

 Only real difference I'm aware of is the OLE and Memo fields.  With JET 4.0, they are stored with the rest of the record if they are 30 bytes or less.   I would just figure the link size.  

Jim.
0
developingprogrammerAuthor Commented:
whao fantastic Jim! thanks for all the help you've given me for this question - and all the other ones as well! = ))
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.