Optimistic v Pessimistic
By Richard Rost
8 months ago
Optimistic vs. Pessimistic Locking in MS Access
In this Microsoft Access tutorial, I will show you how to address common issues with optimistic and pessimistic record locking, as well as introduce page-level locking for efficient multi-user database management. Learn how to simulate a multi-user environment, understand the pros and cons of each locking type, and decide which method is best for your database needs. We'll explore how to apply different locking strategies to specific tables, ensuring data integrity while maintaining performance. Additionally, discover best practices for database distribution and setup to avoid common pitfalls.
Alyssa from Fort Collins, Colorado (a Platinum Member) asks: I followed your instructions on setting my forms to use edited record locking, and while it's generally effective, I'm having some issues. Occasionally, a record remains locked if a user begins editing but then leaves their session open, causing others to get stuck. Additionally, I'm facing issues with update queries failing due to lock violations. There are also instances when users receive the "another user has modified this record" error without anyone else editing the same record. Could you provide guidance on how to resolve these issues?
Members
In the extended cut, I'm going to show you a way to programmatically determine if Access already has the record locked and, if so, display a message to the user when they browse to that record, indicating that the record is locked and they can't edit it.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Links
Recommended Courses
Keywords
TechHelp Access, optimistic record locking, pessimistic record locking, page-level locking, multi-user database, edited record locks, locking issues troubleshooting, resolving lock violations, write conflict error, data conflicts resolution, resolving update query failures, Access database best practices, front-end distribution, Access Updater tool, SQL Server row versioning, record locking types comparison, handling concurrent data entries
Transcript
Today we're going to talk about optimistic versus pessimistic record locking and another kind called page-level locking. We'll get to that one later. Which one should you choose for your multi-user Microsoft Access database?
Today's question comes from Alyssa in Fort Collins, Colorado, one of my platinum members. Alyssa says, I followed your instructions on setting my forms to use edited record locking. While it's generally effective, I've been having some issues. Occasionally, a record remains locked if a user begins editing but then leaves their session open, causing others to get stuck. Yeah, that's going to happen. Additionally, I'm facing issues with update queries failing due to lock violations. There are also instances when users receive the "another user has modified this record" error without anyone else editing the same record. Would you provide guidance on how to resolve these issues?
There are a bunch of different things here to talk about. First, for everybody else who's watching, if you have not yet watched my video on record locks, go watch this first. I explained the two different types. Although I realized after I read Alyssa's question, I actually did not refer to these record locking types as optimistic and pessimistic in that video. So let me go over what they are right now.
Here's my database folder. This is going to simulate a multi-user environment. The back-end file obviously will be on the server. Then I've got two users that I'll represent with front-end one and front-end two. These are identical copies of the same database. We're just using this to simulate two different users on your network. These will obviously be on different machines. Every user should have their own copy of the front-end file. You do not want your users sharing the same database file. That will cause all kinds of random problems. Don't do it. Every user gets their own front-end. It goes on their computer. Do not use the front-end across the network.
There are lots of problems that I see from people. Those are the reasons why. Go watch my video on splitting your database properly for more information. If you watch the other record locking video, I think that was one of the prerequisites. Moving forward, let me open up these databases here. Here's front-end one. This is my standard TechHelp database. I'm just going to resize this window like this so you can see them both at the same time. There's one. I'm going to open up two. There's database two. There's database one. Database one, this user's in here. He comes in here. He starts editing this record. You get the pencil. That means it's dirty. If user one comes in here and tries editing the same record, he also gets the pencil at 30 because there are no record locks set on.
User one is finished. He closes the record. It's saved. User two now tries to close the record and it says, oh, I got a write conflict. This is what's called optimistic record locking, which means the database is just trusting that the users are going to handle these kinds of conflicts. We're being optimistic and we're going to assume that two people are going to try to edit the same record at the same time. That's optimistic. You can either save your changes, which means you're going to overwrite what user one just did, or you can copy it to the clipboard so you can review them, or you can drop your changes. If your changes weren't important, you hit drop. Can't save the record at this time. You want to close the database object anyway, yeah. Now the other person's changes happen. This is optimistic record locking.
Pessimistic, well, that's when you go into the design here and under data, you set record locks to edited record. There's all records too. All records, like I said in the other video, means that if anyone edits any customer, all of the customer records in the entire table are locked. You almost never use this. Sometimes I'll use this for helper data. You know, your little table; it's got stuff in it like Mr., Mrs., Ms., and, you know, suffixes and prefixes because sometimes you only want one user at a time to be able to edit that, or like admin settings, those kinds of things. Usually, you'll pick edited record. Change that and save it. You got to make sure that you have the exact same changes in all of your databases. If two tables or two forms have different settings, then that's going to cause problems too.
We'll talk about this more in a few minutes. In fact, just for good habits, I should just close this database and then distribute a new copy of Front-End One to user two. We'll close this one. Delete that, and then we're going to distribute a new copy of Front-End One with the changes in it. This will now go to Front-End Two. See what I just did there? I just copied Front-End One. That's typically what you do in a network situation. You've got your admin developer copy that you work on. You make your changes and then you push it to your users. Lots of different ways to do this. One way is my Access Updater, I'll talk about that at the end of the video.
Now we got Front-End One. We'll open that guy up and then we got Front-End Two. We'll open that guy up. There's the videos. Why don't you call it video two? Now we've got pessimistic locking on. Pessimistic means if I come in here now and start editing this record, another user comes in here, they immediately see that it's locked. They cannot physically make changes. That's pessimistic. The database is saying, I can't do it. I also got a hold of it. You can't change it right now.
There are pros and cons for each method. I briefly mentioned them in the other video, but let's go over them in more detail now so you get the full gist.
Optimistic locking. This is the default state in a shared Access database. Even if you don't think you have record locking on, you do. It's just called optimistic locking. Access doesn't check that lock. It doesn't check that information until you go to commit your changes to the table. It doesn't look when you open and start editing the record if it's locked or not. So if two users edit the same record, one will lose their changes.
The pros: it's faster. It's more efficient and there's less interference from stuff happening with stuff being locked. However, the cons: conflicts must be resolved manually. If you just spent 15 minutes typing in a user record and it turns out that someone edited it in the background, you're going to lose your work or they are. So that's not a very good solution in that case. Choose this one if you've got lots of simultaneous users, but they're rarely editing the same record at the same time and performance is a concern. You've got hundreds of thousands or millions of records, but maybe you're in a call center and you're almost never editing the same record twice. Then you could use this. This is okay. If conflicts almost never happen. Remember, you can apply different types of locking to different tables. Your customer table could have optimistic locking, but your order table could have pessimistic locking because that's a lot more important. It doesn't have to be the same throughout the entire database. Every table can be set up differently.
Pessimistic locking is the other one. You set this by changing the record locks property to edited record. If one user is editing the record, it immediately locks it and no other users can edit it.
The pros: it prevents data conflicts and it's great for critical data because you want to make sure you absolutely don't have a conflict with, but it's slower. It can cause bottlenecks in a busy multi-user database. Records will stay locked if a user forgets to move off of them. Someone's editing a customer record and they go to lunch and it's still on their screen. It's staying unlocked. One thing, and I believe I mentioned this in the other video, update queries or any kind of query or even records sets and code that affect the entire table. Any record that's locked could affect that update query from processing. In fact, I think I went through an example of this in that other video.
Use this when you need strict data integrity, finance, medical, legal, bank account information, that kind of stuff. Again, you can apply it to just the specific critical tables. It prevents conflicts before they happen. You can use this when you don't have too many users editing the same data at once. Even if you've got millions of records, if they're not editing the same records in that big table, if conflicts are a problem, this is okay.
There is a third solution. It's kind of a hybrid between the two. It's called page-level locking. Again, I mentioned this briefly in the other video. It provides better efficiency in a large database, but at the cost of potentially locking more records than intended. Instead of just locking the record that's being edited, Access will lock an entire page of records in memory. A page is a four-kilobyte chunk of data, which typically includes multiple records depending on the record size. If they're teeny tiny records, like if it's a junction table, and it's, you know, it's just got two IDs in it, you might lock 50 records. Versus if it's a customer record with lots of demographic data, whatever, it might only lock five or six records. It depends on the record size. If a user starts editing a record, all records stored in that same page are locked as well. This means users that are trying to edit nearby records will also get locked out, even though they're not editing the exact same record.
You find this in the file and then go to options. It's going to be under, where is it? The Client Settings. Scroll down right there. Default record locking. This default record locking just affects new forms that you create. Since I use my blank template anyways, I've already got it set in those ones. This is the checkbox you're looking for right there. Open databases by using record-level locking. If you turn that off, now we're going to be using page-level locking, which will lock more records, but it's slightly more efficient in bigger databases. I'm not going to turn that on though because I don't want it. I'm going to cancel in fact.
The pros of page-level locking: instead of locking individual records, Access locks a block of records at once, which can be faster in large databases requiring fewer locks. If you've got a very large database with hundreds of thousands or millions of records, page-level locking may reduce what's called lock contention and improve performance. Instead of having, you know, 50,000 little locks here and there, you might have just, you know, 10. You're locking big blocks of records instead.
The cons: like I mentioned, if two users are editing different records that happen to be on the same page, both get locked. You have the same problem with update queries as well. Use this if your database is very large and you notice that individual record locking is causing performance issues, slowing your database down and avoid it when users frequently edit different records at the same time since it might create unintended conflicts. I've dealt with clients in the past where they have both situations going on. You might have a client that's got lots of people just looking up data all day long, just reading, reading, reading, reading, pulling up client records, that kind of stuff. So you don't need any record locking at all in those cases usually. I've had other ones where it's very seldom that they look stuff up, but it's constant data entry. Like I said before, I think call centers. You pull up a customer's information and you're constantly typing up other data related to it. Again, it all depends on your specific needs.
Other notes, make sure everyone has their own front end as I said before and that is all in caps. This is PowerPoint. Very important. Do not have people sharing the same front-end file. Do not let multiple users just connect through it over the network. Lots of people do this and lots of people have problems. That's the best way to corrupt your database. Of course, it goes without saying don't use OneDrive, Dropbox, Google Drive, or any of those types of file-sharing services to share your Access database. No, no, no, no.
Make sure everyone's record locking settings are the same; otherwise, you'll get this guy. I get this question all the time. Could not update currently locked by user, whatever on machine, whatever. Yes, my computer's name is Picard. I simulated this by simply turning on edited record, pessimistic locking on one database and leaving the other one optimistic. When the pessimistic locked it and the optimistic didn't know about it, it caused this problem. It went to check the record when it went to save it and it said it's locked by someone else. You could also get this with a corrupt database as well. So a compact and repair if you suspect that's the case.
Finally, I should mention that SQL Server supports something called row versioning where it's basically possible to track changes at the field level. Access only goes down to the record level. It's all-or-nothing for that record. But with SQL Server, you can actually basically lock individual fields. Two people could be editing the same customer. As long as they don't edit the same fields, you're fine. If one person edits the first name and the second person edits the last name, it'll allow it. There are some things you got to do with triggers and timestamp columns and lots of other things. Yes, you could set something like this up in Access to handle that kind of conflict resolution in the background. It would involve a significant amount of coding. If you really want to see how to do it, let me know.I'm not going to do it as a TechHelp video, but maybe as a separate seminar or something. It will involve some coding. But it's possible to handle that conflict resolution. It's kind of like synchronizing remote databases. But there you go. That's pretty much everything you need to know about record locking in Microsoft Access.
Again, in my other video, in my record locks video and the extended cut, I do show you how to set up custom record locking, where we actually have a record locks table and we can handle the record locking ourselves. There are all kinds of cool things you could do with it. But watch this video for more information on that.
And like I've stressed multiple times during this video, it's important that all of your users have their own front-end database file running on their C drive or at least a drive on their local machine, not over the network. And if you want an efficient way to easily distribute new updates to all of your users with one click, check out my access update.
Alright, that's going to do it folks. That's your TechHelp video for today. Hope you learned something. Live long and prosper my friends. I'll see you next time.
TOPICS:
Optimistic record locking
Pessimistic record locking
Page-level locking
Simulating a multi-user environment
Splitting a database properly
Setting record locks in Design view
Copying the front-end database to users
Pros and cons of optimistic locking
Pros and cons of pessimistic locking
Applying different locking to different tables
Pros and cons of page-level locking
Client settings for record locking
Avoiding shared front-end files
SQL Server row versioning and field-level locking
Lock contention and performance improvement
COMMERCIAL:
In today's Quick Queries video, we're going to tackle the issue of record locking in Microsoft Access, discussing optimistic, pessimistic, and page-level locking. We'll talk about the pros and cons of each method, including data conflicts, speed, and performance in multi-user environments. You'll learn how to decide which locking method suits your database best and get tips on setup, such as ensuring each user has their own front-end file. Avoid common pitfalls, like using network-shared front-ends or file-sharing services. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. What is optimistic record locking in a Microsoft Access database?
A. It locks records immediately upon being edited to prevent conflicts.
B. It allows multiple users to edit the same record simultaneously, resolving conflicts during saving.
C. It locks an entire page of records in memory.
D. It is only used for critical data like finance and medical records.
Q2. What is the main advantage of optimistic locking?
A. It prevents data conflicts before they happen.
B. It only applies to critical data.
C. It improves performance when many users rarely edit the same record simultaneously.
D. It allows for field-level tracking of changes.
Q3. How does pessimistic record locking work in a Microsoft Access database?
A. It locks the record at the field level, preventing any changes.
B. It allows multiple users to edit the same records at the same time.
C. It locks records as soon as one user starts editing them, preventing others from editing.
D. It locks pages of records rather than individual records.
Q4. What is the primary disadvantage of pessimistic locking?
A. It prevents any record from being edited.
B. It may cause records to remain locked if the user leaves their session open.
C. It is less efficient in large databases.
D. It allows conflicts to occur frequently.
Q5. In which scenarios is pessimistic locking most beneficial?
A. In databases with lots of simultaneous users rarely editing the same record.
B. In situations where performance is a critical concern.
C. When data integrity and preventing data conflicts are crucial.
D. When multiple users need to edit the same records simultaneously.
Q6. What is page-level locking?
A. It locks the database file to ensure only one user has access at a time.
B. It locks an entire table when one record is edited.
C. It locks a block of records (a page) in memory, which can include multiple records.
D. It allows field-level permissions for each user.
Q7. When might you choose page-level locking for your database?
A. When the database contains only a few records.
B. When the database is large and regular record-level locking causes performance issues.
C. When all users are on the same network drive.
D. When only a single user accesses the database.
Q8. Why is it important to ensure that each user has their own front-end database in a multi-user Microsoft Access setup?
A. It reduces the likelihood of database corruption.
B. It allows users to access the database from different locations.
C. It enables all users to edit the same record simultaneously.
D. It is more secure against data breaches.
Q9. What might happen if you mix different record locking settings among users in the same database?
A. All records will be permanently locked.
B. The database will automatically resolve any conflicts.
C. Users may encounter the "could not update currently locked by user" error.
D. It enables a smoother performance with fewer technical issues.
Q10. Which feature is supported by SQL Server but not at the record level by Access?
A. Optimistic locking.
B. Pessimistic locking.
C. Row versioning, allowing field-level change tracking.
D. Page-level locking.
Answers: 1-B; 2-C; 3-C; 4-B; 5-C; 6-C; 7-B; 8-A; 9-C; 10-C.
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.
Summary
Today's TechHelp tutorial from the Access Learning Zone covers record locking in a multi-user Microsoft Access database. We'll explore optimistic and pessimistic record locking, and later on, discuss page-level locking. These methods determine how records are managed when multiple users try to edit them at the same time.
Alyssa from Fort Collins, Colorado has encountered some issues while using edited record locking as I recommended. She's noticed that sometimes a record remains locked when a user begins editing it but doesn't close the session, which blocks others from accessing it. Update queries also fail due to lock violations, and users encounter errors about modifications by others, even when no one is editing the same record. Let's address these challenges.
For anyone unfamiliar with record locks, I suggest watching my initial video on the topic where I explain different locking types, though I didn't refer to them as optimistic and pessimistic back then. Let's now define these.
Imagine a multi-user setting: the back-end of the database is on a server, with each user having their own front-end. This setup prevents problems caused by users sharing the same database file. To emphasize, each user must have their own front-end on their local machine, not across the network.
Optimistic record locking is like trusting users to manage conflicts themselves. If two users edit the same record, the database doesn't check for conflicts until changes are saved. It's efficient and fast, beneficial for many users not editing the same record frequently. However, conflicts need manual resolution, which can lead to lost work. You can apply different locking types to different tables based on your database's needs.
Pessimistic record locking, on the other hand, locks a record immediately when a user starts editing, preventing others from making changes. It's ideal for ensuring data consistency in critical operations, though it's slower and can create bottlenecks in busy settings. Use it where data integrity is crucial, but be careful with users leaving records open and unresolved lock issues affecting queries.
Page-level locking uses a hybrid approach, locking a whole page (a 4KB chunk) that includes multiple records. This can improve efficiency in large databases, but it might lock unintended records. It's suitable for large databases with performance issues due to individual record locking, but avoid it in scenarios where frequent edits to various records occur simultaneously.
In summary, whether to use optimistic, pessimistic, or page-level locking depends on your unique situation. Make sure everyone is using their own front-end and the same locking settings to avoid errors such as "could not update." Remember, sharing databases over file-sharing services like OneDrive or Dropbox is a definite no.
Finally, SQL Server offers field-level locking through row versioning, unlike Access, which is limited to record-level. While setting up Access to handle field changes is possible, it requires advanced coding and isn't covered in this video.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.
Topic List
Optimistic record locking
Pessimistic record locking
Page-level locking
Simulating a multi-user environment
Splitting a database properly
Setting record locks in Design view
Copying the front-end database to users
Pros and cons of optimistic locking
Pros and cons of pessimistic locking
Applying different locking to different tables
Pros and cons of page-level locking
Client settings for record locking
Avoiding shared front-end files
SQL Server row versioning and field-level locking
Lock contention and performance improvement
|
