record-level locking clarification

[vientito]

I would like to clarify the concept of record-level locking



Say I have a TBLA which consists of two fields TBLA_FLD1 & TBLA_FLD2
&

TBLB which consists of three fields TBLB_FLD1 & TBLB_FLD2 & TBLB_FLD3

Now two users have declared the usage of the database as shared and pessimistic

I have generated a query in addition to TBLA & TBLB which is QryCombined which basically just a join of the two and consists of all five fields

If user1 has brought in a record query from QryCombined and edits on TBLA_FLD1 & TBLB_FLD3

At the same time user2 is trying to bring in as well QryCombined but with TBLA_FLD2 & TBLB_FLD1 and trying to edit both, will user2 be blocked by user1 who's editing the other fields

For all I know, the locking is based on record-level locking so I would suppose one record of QryCombined will comprise of all five fields so editing in any of them should actually block another user from editing. However I am not sure whether it is just a subset of the record that is the contesting area because user1 recordset has only two fields out of five, which do not happen to intercept the other fields that user2 is working on.

[rpeare]

What is the question?

If the question is 'how do I prevent record locking' don't allow live access to your data, use unbound forms instead of queries or bound forms.
Doing data entry on a query, in my opinion, is a very bad practice, as is allowing direct access to your tables through bound forms (an opinion not everyone shares just my personal bias).

[vientito]

the question is "what is the exact boundary of a record refered to in record-level locking?" physically where does it refer to? is it a whole row in a query that straddles all the fields? Or is it just the row of fields that I explicitly request for in my opening SQL statement?

I have learned that record is related to cache size in the local machine so it may block even when it is not hitting the exact field where it is contested between two users

[rpeare]

I believe it locks the entire record. So if you are editing jane doe's record nobody else can at the same time (theoretically). It doesn't matter if you are looking at field a b and c and the other person is look at field x y and z. So in your original example if you are allowing data entry to occur on a query with linked tables it is, in effect, locking two records one from each table.