I need help locking records.

[odin1701]

I thought I had this handled...however it seems that it's not quite 100%.

I have a database that has a single user form. This form, upon loading, has a recordsource which selects only those records which have two fields marked as "No".

The fields are named LOCKED and COMPLETED. They are just Yes/No fields.

If either one is marked as Yes, it is excluded in the query.

If they are both marked as No, they are included in a query which is a SQL query that uses TOP 100 to grab the first 100 records. It then runs a randomize on a seed which is different for every user of the database, and using that randomize it creates an alias based on that seed and a partial account number, then arranges the generated random number Descending. It then takes these group of 100 ordered and slects the first one by doing a TOP 1 query. So it's a nested query. It looks like this:

SELECT TOP 1 *, Rnd(tblMain.ACCTNUM) As Expr1 FROM
(SELECT TOP 100 * FROM tblMain WHERE
tblMain.COMPLETED = False AND tblMain.LOCKED = False)
ORDER BY Rnd(tblMain.ACCTNUM) DESC;

When the form is loaded, I have the following:

Randomize strSeed

The strSeed has a random number assigned to it which is pulled from a table of users. Each user has a unique number which is 9 digits long.

The unique numbers were generated at random.org.

I thought that all of these steps would prevent users from selecting the same record and being able to open it, but there are still a few who are getting the same record to work on. There are about 30-40 users at any one time using it.

Also, after the Randomize, I have this code in the Form_Load():

Me.Form.Requery
Me.LOCKED = True
Me.Refresh

I'm not sure if it needs the Requery, but I put it in to force it to grab a new record, and then set the LOCKED field to true and refresh.

Can anyone see my issue, or suggest a better way of doing this?

I cannot have the same person working on the same record at once - they shouldn't even be able to pull the same record up at all. They are doing phone calls based on these so we don't want the same person called twice.

Ideas are appreciated. Thanks!

 

[Banana]

This is just a guess but I would think that merely using random selection does not actually guarantee you won't select records that are locked. Even though you have a field to "lock" the records, your users still will be contending to lock the fields anyway, and then there's the possibility that row-level locking may not be actually used but rather page-locking.

I'm not sure if I follow the logic for randomizing user's account in the query- why would users want to get a batch of random records here, and why is the user's account used as a seed?
I'm guessing you're using a Access as backend? And you actually have 30-40 concurrent users?

In this scenario, I'd look at whether pessimistic locking would work here, or move the backend to a server-based RDBMS which also provides a better support for concurrency if you are keeping running into lock contention or deadlocks.

 

[pbaldy]

This is a shot in the dark, but try this right after setting the Locked field:

DoCmd.RunCommand acCmdSaveRecord

I don't think the Refresh is doing anything for you.

 

[odin1701]

This is just a guess but I would think that merely using random selection does not actually guarantee you won't select records that are locked. Even though you have a field to "lock" the records, your users still will be contending to lock the fields anyway, and then there's the possibility that row-level locking may not be actually used but rather page-locking.

Right it's possible based on query time that it's not yet locked, but by the time it loads things it is, but it's already selected. This is why I tried to introduce randomization. Because if you just did a SQL TOP 1 it would grab the same record for everyone that happened to query about the same time, which would be bad.

I'm not sure if I follow the logic for randomizing user's account in the query- why would users want to get a batch of random records here, and why is the user's account used as a seed?
I'm guessing you're using a Access as backend? And you actually have 30-40 concurrent users?

It's not randomizing the users account. There is a table which has the persons account as one field, access rights level in another, etc. It's just use to ensure that only those can log on to the database that have permission. I added another field called RandomSeed, and put a different random number in each field for each user. It is randomizing based off of that number, not the account. Users need to get random records in an attempt not to get the same record someone else is working on. You are correct I have a backend and frontend. And yes concurrent - all working at the same time.

In this scenario, I'd look at whether pessimistic locking would work here, or move the backend to a server-based RDBMS which also provides a better support for concurrency if you are keeping running into lock contention or deadlocks.

I can't move the DB to the actual SQL server - I would if I could, but it's not allowed. Red tape and all that. I'm not sure what pessimistic locking is, can you explain?

 

[odin1701]

This is a shot in the dark, but try this right after setting the Locked field:

DoCmd.RunCommand acCmdSaveRecord

I don't think the Refresh is doing anything for you.

While this works, it introduces a huge slowdown. Took about 10 seconds to ge the next record.

BTW, the DB currently has over 500,000 records in the table we're working with, and not all data is imported yet.

I know....I still want it on the SQL server

 

[odin1701]

Hmm..okay that must have been a fluke because now the speed is okay.

I will try doing the acCmd - see if that works out better tomorrow.

I have been pulling my hair out over this lol. The thing is that this process is very speed oriented and driven. We need to get as much done as possible as soon as possible.

They WERE using a shared spreadsheet until I found out about it. *sigh*

 

[Banana]

Pessimistic locking is a Access file setting - You would look in the Option dialog and set the locking.

As for not being able to move it to SQL Server - why not use SQL Server Express or MySQL so you can install it without need to go through IT (assuming you have an administrator account on at least one of machine in your department). Since they are free to use, that shouldn't be a problem, hopefully.

 

[odin1701]

Pessimistic locking is a Access file setting - You would look in the Option dialog and set the locking.

As for not being able to move it to SQL Server - why not use SQL Server Express or MySQL so you can install it without need to go through IT (assuming you have an administrator account on at least one of machine in your department). Since they are free to use, that shouldn't be a problem, hopefully.

Well....I AM IT lol. Except we have a contracted lease agreement on the SQL server so I can't touch it unfortunately. It's basically a way for HP to extort all sorts of money from you. It does make sense, being a global company and they provide 24/7 monitoring, but it's frustrating at times trying to do things I need for the local office.

I could install it, but it's not approved procedure, etc. More red tape. Must do what I can to get this done.

I will look at pessimistic locking, but I will go with the current change tomorrow and see how it holds up.

I didn't see much of a problem after the latest update, but there are two users who both seem to be getting the same records several times during the day. It's strange.

 

[Banana]

Be aware that the random function will return same sequence of numbers for same seed, so if there is a case of two users having the same seed, they will get the same sequence. Maybe check that this isn't the case?

 

[odin1701]

Be aware that the random function will return same sequence of numbers for same seed, so if there is a case of two users having the same seed, they will get the same sequence. Maybe check that this isn't the case?

I am aware. They all have different numbers.

Initially I was using Randomize Timer, but the system times are set by the server on login so there could be identical seeds using that - I was still running into more issues so I went with the random seed per username route.

 

[odin1701]

Okay I enabled pessimistic locking. That's all well and good.

Problem is that I can still select that locked record in a query. How can I prevent this?

 

[Banana]

They don't prevent selecting records about to be locked, but they do prevent editing a record that's been dirtied by other users.

The difference is this:

Optimistic locking:

Progress	User A			User B
1		Selects record #3
2		Starts editing.
3					Selects record #3
4		Still editing.		Starts editing.
5		Saves the edit.		Still editing.
6					Saves, get a conflict error.

Pessimistic locking

Progress	User A			User B
1		Select record#1	
2		Start editing.
3					Selects record #3.
4		Still editing.		Try to edit, is blocked.
5		Saves the edit.		Cannot edit.
6					Retry the attempt, and succeed
7					Editing the record.
8					Saves.

Did that clarify how it works?

 

[odin1701]

That's what I figured it did, but that won't help me at all. I only tried it as someone suggested it with regards to my issue. I didn't see how it would help though.

Users simply cannot be allowed to have the same record selected at all. We are calling customers based off of this - and we don't want to call the same person twice. For the most part it is working as is, with only a few problems. I would like to get it to 100%.

Another issue that I have is that users are locking (via a field) more than one record somehow. I don't understand how, as the record is only updated once the record is loaded into the form, and before the record is saved or a new record is retreived, it removes the lock. I had one user who had 9 records locked, which seems impossible. The only way I can see a user having more than one is if they force-close the database while having the form open, before saving it. They have no access to the locking field - only in the VBA is that available. It sets the lock when the form is opened on the record retrieved, and un-sets the lock before getting a new record or saving a record. So how it's locking more than one is beyond me - the users got no errors and didn't force-close the database.

 

[Banana]

That's what I figured it did, but that won't help me at all. I only tried it as someone suggested it with regards to my issue. I didn't see how it would help though.

Users simply cannot be allowed to have the same record selected at all. We are calling customers based off of this - and we don't want to call the same person twice. For the most part it is working as is, with only a few problems. I would like to get it to 100%.

Ah, I see. I was under the mistaken impression that you needed to lock the field for update, not for viewing. My apologies.

Still, I imagine the pessimistic locking would be useful for that VBA piece doing the locking because other clients wouldn't be able to access the record when it's being edited (e.g. being locked by a running query) and retrying will never reach those now-locked records.

If you can eliminate the "SELECT" and do something like this:

UPDATE a record ## to LOCKED = true
SELECT same record for the user

or at least wrap the process you already have in place in a transaction:

DBEngine.BeingTrans

'SELECT records...
'UPDATE to locked

DBEngine.CommitTrans

'display the records to the user in form by setting the recordset to the form

The aim here being that you want to wrap the locking operation into an atomic operation so there is no interruption or contention- you want the operation to be greedy as possible.

Another issue that I have is that users are locking (via a field) more than one record somehow. I don't understand how, as the record is only updated once the record is loaded into the form, and before the record is saved or a new record is retreived, it removes the lock. I had one user who had 9 records locked, which seems impossible. The only way I can see a user having more than one is if they force-close the database while having the form open, before saving it. They have no access to the locking field - only in the VBA is that available. It sets the lock when the form is opened on the record retrieved, and un-sets the lock before getting a new record or saving a record. So how it's locking more than one is beyond me - the users got no errors and didn't force-close the database.

How do the VBA unlock the record? On form's close event, presumably? If this is the case, then the solution would be to train the users to not do three-finger saluted or better yet, find out why they are doing (e.g. form locks up?) and address those issues so there is no need for the users to use the three-finger salute.

HTH.

 

[odin1701]

I know this is a bit long winded, and thank you for the help you have provided so far, it is much appreciated. I am overall more of a hardware person than a programmer, so some of what you say I'm not totally sure on.

Ah, I see. I was under the mistaken impression that you needed to lock the field for update, not for viewing. My apologies.

Still, I imagine the pessimistic locking would be useful for that VBA piece doing the locking because other clients wouldn't be able to access the record when it's being edited (e.g. being locked by a running query) and retrying will never reach those now-locked records.

If you can eliminate the "SELECT" and do something like this:

UPDATE a record ## to LOCKED = true
SELECT same record for the user

or at least wrap the process you already have in place in a transaction:

DBEngine.BeingTrans

'SELECT records...
'UPDATE to locked

DBEngine.CommitTrans

'display the records to the user in form by setting the recordset to the form

The aim here being that you want to wrap the locking operation into an atomic operation so there is no interruption or contention- you want the operation to be greedy as possible.

I'm not sure if this would help. Do you mean lock them via the field (It's just a field called LOCKED and is Yes/No), and drop the randomization? Since if it locked a random group it wouldn't necessarily be able to unlock the same. Or did you mean actual record locks - because I don't want a query to return any that are.

And yes, my main goal is to lock a record from viewing at all while someone else is working on it.

How do the VBA unlock the record? On form's close event, presumably? If this is the case, then the solution would be to train the users to not do three-finger saluted or better yet, find out why they are doing (e.g. form locks up?) and address those issues so there is no need for the users to use the three-finger salute.

HTH.

Here is how I am doing it now.

Upon opening the DB, the user opening is checked to see if they have access. If they do, it shows the menus that they have accessible based on their access level. Then it retrieves a random number from the user table, and initiates Randomize based on that number.

If they have basic level access (which anyone doing actual work will) the next step is to load the main form, frmMain.

The forms recordsource is set in the form itself, so when the form opens, it runs a SQL query and populates the field. The query is below, slightly changed from the original I was using:

SELECT TOP 1 *FROM
(SELECT TOP 200 * FROM tblMain WHERE
tblMain.COMPLETED = False AND tblMain.LOCKED = False)
ORDER BY Rnd(tblMain.ACCTNUM) DESC;

I increased the initial sample size to 200 to give a larger pool to generate random numbers based on. This seemed to clear up a lot of the issues. I only was using 100 previously.

Here is the code in the Form_Load() sub:

Me.LOCKED = True
    Me.COMPNAME = Environ("ComputerName") & " Date/Time:" & Now()
    DoCmd.RunCommand acCmdSaveRecord
    
    varSaved = False
    
    Me.Frame7.Enabled = False

I guess I should apologize for the "Frame7" haha...built this really quickly and haven't cleaned it up yet.

The first thing it does is set LOCKED to true (which should prevent it from showing up in other users queries), sets the field COMPNAME with the computer and date/time. This was added for troubleshooting so I could see who had what open, and when.

varSaved is just a boolean used to determine if they have saved the current record. If it's false, and they click on the close button, then it will not close and remind them to save first. It also will not let you save before completing it.

At this point, there should be a single record loaded to the form, with the LOCKED field set to True. Nobody else should be able to select it in a query. I realize that more than one person could possibly run a query close together and get the same record - especially using the TOP method in the SQL query. This is why I used a random seed to randomize the partial account numbers - then order descending. It should prevent this for the most part - or it seems like it.

After this, the person works the record. Once they complete it and it meets the set criteria, they can either press one of two buttons. Save Record or Get New Record.

They cannot move forward and no data changes are made by the VBA until they have marked the record complete (another Yes/No) and it determines that the form was properly filled out based on what they selected. It's just some simple If/Then stuff, doesn't make any changes to the LOCKED field yet.

(Not sure if it matters, but in the Form properties I have Modal set to Yes, PopUp set to Yes, Movable set to No, Record Selectors set to No, Navigation Buttons set to No, Scroll Bars set to Neither, Dividing Lines set to No, Auto Center set to Yes, Close Button set to No, Control Box set to No, Allow Filters set to Yes, Allow Edits set to Yes, Allow Deletions set to No, Allow Additions set to No, Data Entry set to No. Not sure if any of those can be an issue, but the goal was an unmovable dialog that the user cannot de-select or do anything else. I suppose Ctrl+F4 may still work, but why would someone do that anyway?)

If done right, here is the code for the different buttons (I am leaving out the If/Then statements as they shouldn't affect anything):

Save Button:

Me.LOCKED = False
    Me.COMPNAME = ""
    DoCmd.RunCommand acCmdSaveRecord
    varSaved = True

Once saved, the varSaved being set to True allows them to exit the database via the Exit button on the form. Otherwise it doesn't work.

Also, the only way that it can be saved is having the COMPLETED Yes/No box set to True (checked). The query should no longer select this record either since it's marked as complete.

Get New Record Button:

Me.LOCKED = False
    Me.COMPNAME = ""
    DoCmd.RunCommand acCmdSaveRecord
    Me.Form.Requery
    Me.Frame25.Enabled = True
    Me.Frame7.Enabled = False
    Me.LOCKED = True
    Me.COMPNAME = Environ("ComputerName") & " Date/Time:" & Now()
    DoCmd.RunCommand acCmdSaveRecord
    varSaved = False

Again, only does this if the record is marked completed and meets criteria.

Lock is removed, record saved, then the form's recordsource is requeried which should select a new record which is neither locked nor completed. Resets the default option groups enabled, sets the lock as true, adds computer name, etc. saves record and then sets the varSaved to false.

The only other button available is the exit button, which has this code:

If varSaved = False Then
    
        MsgBox "Current record is not saved.  Please save before closing."
        Exit Sub
    
    Else
    
        DoCmd.Quit
    
    End If

The users that have more than one locked record say no errors have come up and they have not forced the database to close, nor has it crashed, etc. Even if that did happen, it should only leave one record locked and uncomplete each time it happened.

When I open the main form, it locks my record. When I go to design mode, it's still locked and I can open it again and get a new record, and the other just sits. That's all that should happen in a crash or force-close situation that I can tell. I don't get how they are locking so many records at once.

 

[Banana]

I'm not sure if this would help. Do you mean lock them via the field (It's just a field called LOCKED and is Yes/No), and drop the randomization? Since if it locked a random group it wouldn't necessarily be able to unlock the same. Or did you mean actual record locks - because I don't want a query to return any that are.

Basically the suggestion here is that you should lock the record by toggling the LOCKED field to yes then select that field for the user's viewing. The point is that users should be selecting only records that they've already locked, and if the query doing the actual locking (setting LOCKED = Yes) uses pessimistic locking, this should preclude other users from trying to set (LOCKED = yes) on the same record at same time and thus your query can be simplified to select only records where LOCKED = yes and username = username. When the user is done with the record, and need a new batch, you can re-run the (LOCKED=yes) query to get a new batch of records.

Did that help?

After this, the person works the record. Once they complete it and it meets the set criteria, they can either press one of two buttons. Save Record or Get New Record.

While you may locked down many properties and form's navigation, I don't think it's safe to rely on either events to unlock the records. I would consider doing unlock in only one place - Form's BeforeUpdate event. The save button & get new record button should only do one thing - save the record. Move all validation logic to the BeforeUpdate and you will have far more reliable way to unlock the record.

The code in BeforeUpdate would look like this:

Private Sub MyForm_BeforeUpdate(Cancel As Integer)

'Do validation
If something not valid Then
   Cancel = True
End If

'Insert as many validation as you need here

If Not Cancel Then
   Me.Locked = False
End If

End Sub

For more illustrations, search the board for Ghudson's "Better Mouse trap" which uses similar principle.

I hope this clears things up.

 

[odin1701]

Okay I understand the BeforeUpdate. So that will run before the actual save takes place via the buttons right?

So in the save, it should just do the command to save the record, which will run through the BeforeUpdate first, unlock if okay, then save. And the get new will do the same, except after saving do the .requery?

Basically the suggestion here is that you should lock the record by toggling the LOCKED field to yes then select that field for the user's viewing. The point is that users should be selecting only records that they've already locked, and if the query doing the actual locking (setting LOCKED = Yes) uses pessimistic locking, this should preclude other users from trying to set (LOCKED = yes) on the same record at same time and thus your query can be simplified to select only records where LOCKED = yes and username = username. When the user is done with the record, and need a new batch, you can re-run the (LOCKED=yes) query to get a new batch of records.

So you're saying to assign a group of records to a user and then only query from those records until gone, then assign another group? But also set the locked then?

If I assigned a group to a user, then I wouldn't really need a locked field. I could use their username in a query easily enough and have it only return the ones that match them. I would need some sort of cleanup function to run at the start of the day but that wouldn't be difficult.

So, simplistically I would need to enable pessimistic locking on records in the SQL query first (not sure how to do this).

Then have it query say, 50 records without a username set, set the username to the field and commit this.

Then have it query and select a single record that has their username in it, and if it returns 0 records, have it grab 50 more, etc.

 

[Banana]

Yes, you are correct on BeforeUpdate's behavior.

Yes but the important point here is that you have to do it in an atomic operation by either doing a UPDATE query immediately on the table to set the username's name or LOCKED=yes. You don't want to do a SELECT query then UPDATE to LOCKED=yes because that could give you problems as you are having right now. By doing a UPDATE query immediately with a mechanism to recover gracefully should it fail (because someone else locked it first).

You can set pessimistic locking as a query's property (press Alt+Enter to view the property sheet in a query builder's design view- be sure to click on the gray area so you are viewing the Query properties, not fields properties.

However, I would think it may be preferable to set the file to use pessimistic locking
rather than a specific query given the nature of your operation.

With this in mind, yes you have the general concept - update the field to set username, then select only from that list.

 

[odin1701]

Thanks so much for the help. I will work on this some more, and post back if I run into any issues.

So far today, there have been no complaints of users getting the same record to work, so at least that is good. The multiple locked records is not really an issue, as I can just unlock them in the data table. I monitor the locked records periodically. Will be nice to not have to do that at some point.

As it stands, it's usable - and far better than the shared spreadsheet they were using. Not too bad for tossing it together in just a few days.

 

[gemma-the-husky]

I havent read the whole thread in detail

personally - i think if you are locking records/having to lock records there is something amiss

I have never needed to do anything other than the default optimistic locking

selecting items based on yes/no field is different - but is nothing to do with record locking per se.

out of interest, why would you want to prevent someone viewing a record while others are using it? - i would think you could just show an indicator to point out that it is in use (maybe not so easy) - but a user couldnt change it even with no locking (ie optimistic locking ) because then you get the "another user changed the record error"

I think maybe you are over complicating this.

your last quote
I don't get how they are locking so many records at once.

I am not 100% sure of all this, and my terminology may well be off - but locking may not work as you think - unless you are careful access actually locks a datapage of records rather than just a single record - which therefore includes both the record you want locking, and others associated with it (ie in the locality of the page/page cluster) I think this is for efficiency - I think any programme would find it difficult to lock just part of a datapage

bear in mind that with big hard disks the page/cluster size is also quite big 16K or something similar. ie a 1byte text file still takes up 16K of disk space. By the same token, a 100byte access record is on the same page as 160 other records, and these will all be locked

That is why record-locking strategies always recommend locking and releasing for the minimum time - ie dont open a file locked - and rarely open a record locked - just lock it just when you are trying to write it, and release the lock immediately afterwards.