Lock Types 2

[Skittle]

I have been cruising through MSDN making sure I understand recordset locking. One thing that I need clarifying concerns the .LockEdits method of a recordset.

It can be set to 'True' or 'False' and is used in the MSDN examples. In the reference books I use, the method is not used. I think that the .LockEdits sets the locktype of a recordset to Pessimistic when on and optimistic when off. It is therefore a more simplistic alternative to specifying the .locktype property to adLockOptimistic, adLockPessimistic, adLockReadOnly and adLockBatchOptimistic.

Could somebody confirm my conclusion?



Dazed and confused

 

[MakeItSo]

Dazed and confused - good song by Led Zeppelin btw...
LockEdits=False
refers to optimisticlock, which means, that the record in the table is only locked at the moment of the update and else is unlocked, whereas
LockEdits=True
means pessimistic lock, locking the record (in Acc.97 the entire table from the moment of opening on, if I'm not very mistaken...

Does that clarify your needs?

Andy

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[zemp]

faq222-3670 might shed some light.

Take Care,

zemp

"Show me someone with both feet on the ground and I will show you someone who can't put their pants on."

 

[CCLINT]

>It is therefore a more simplistic alternative to specifying the .locktype property

LockEdits and dbOptimistic is DAO
You can just specify this in the LockEdits argument of the OpenRecordset method using one of the lock constants (such as dbOptimistic), or in the recordset's LockEdits property, where true is the default and means pessimistic locking.

LockType, and adLockOptimistic, is ADO
You can just specify this in the LockType argument of the recordset object's LockType property or the LockType argument of the Open method

>in Acc.97 the entire table

Just the 2K Page.

 

[MakeItSo]

2k page: Yeah right, that was it - Too much anyway...

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[Skittle]

Dudes,

Zemp good FAQ but I'm still struggling a little with some of the concepts.

I am accessing an SQL server with a lock pessimistic, forward only recordset. I have specified a single table as the source.

I expected my program to read all the records from SQL server, stick them in my PC's memory and allow me to read through them. If I updated one of them, the updated record would be sent back to SQL Server. If somebody updated the record on SQL server I expected the update not to be included in my VB program because the recordset had become independant in the memory of my PC. I tested this by setting up a connection and debug breaking the code after the first recordset read.

To my surprise I find that even after opening the connection and moving to the first record in VB, I can still change the second record in the SQL database using SQL Enterprise Manager. More surprising is the fact that the change to the second record will be part of the record when I read it in my already running VB program with a RecordSet.MoveNext.

Therefore.....

Am I correct that with the exception of a Static cursor type, all recordsets hold one record at a time in memory and pull it in as required by the position of the recordset pointer against the recordset?



Dazed and confused

 

[MakeItSo]

Dazed and absolutely not confused - yeah, i see it exactly this way, too. That is also, why you should carefully choose between optimistic and pessimistic lock type.

Andreas Galambos
EDP / Technical Support Specialist
Bowne Global Solutions Wuppertal, Germany
(andreas.galambos@bowneglobal.de)
HP:

http://home.arcor.de/andygalambos

 

[CCLINT]

>Am I correct that with the exception of a Static cursor type, all recordsets hold one record at a time in memory

No. Well, if you mean by "memory" the amount of records held locally.

-When using a Client side cursor, all records will be pulled in.

-When using a Server Side cursor, the server's cursor manager is used, and the amount of records pulled in to the client depends on the CacheSize. This is usually set to 1 by default, meaning 1 record, or page, will be cached locally, and the rest will be held by the server.
You can change this value prior to opening the recordset.

(This default may be "ignored" by some objects, such as a datagrid, as the datagrid will still pull in all records - logically).

Concerning locks:
Some providers provide dynamically a lock mode property to the connection object.
And this can be overridden in code, with some providers, by defining record locking mode through the connection object, if the provider supports this property.
If the provider only uses Page locking, or the property is left at page locking, then the cache size of 1 basically affects the page.

A page could be a partial record, a whole record or several records. If you have 5 records and each hold .45K of data, then possibly the page spans over 4 records, and part of the fifth.
So, all 5 records get locked, if the lock mode is set to page locking.
If the lock mode is set to Record Locking, then just one record gets locked.

Pessimistic locking happens as soon as a record is edited, and ends when the record is updated.
Optimistic locking happens only during the actual Update period.
Optimistic locking is all you should ever need, if you take advantage of the ADO properties and provider dependent dynamic properties, in order define what type of Optimistic locking you want, and to catch and deal with the conflicts in record changes.

 

[Skittle]

Strewth CCLINT, you know your locking.

I get almost all of it. Just one area of your posting doesn't quite penetrate my tatty old brain.

1) How big is a page in K? It sounds like the old blocking
factor thing that I used to deal with in Cobol. Is
it a set size or one that can be set but has a default?

2) Page locking is the ADO default.
This could be overriden by the providers settings.

How can page locking/record locking be set to one or the
other in VB if the providor allows it?


Many, Many thanks. This is the sort of in between the lines knowledge the books never tell you.



Dazed and confused

 

[CCLINT]

Well, actually ADO doesn't do this page/record locking, but the DBMS does, in most cases.
My statement is mis-leading, or actually wrong .
So, the default locking is up to the DBMS. The page size is up to the DBMS. Some DBMSs have page locks, some record locks and some both lock modes.
The provider, if changing the lock mode is supported, will then provide the ADO connection with the dynamic property for the lock mode - the name could be anything - and this is passed on to the provider, then to the DBMS.

Reading your initial post I took it that you were confusing DAO with ADO because and also didn't see what DBMS you were using.
Somehow I "lost" the mention of SQL Server in a later post.

So, single record locking is what you are interested in and what applies.