how the data being shared (1 Viewer)

[hfsitumo2001]

locking is in File/Options/Client Settings

It may be somewhere slightly different depending on the Access version.

Thank you gemma

 

[gemma-the-husky]

Isn't it automatically lock if 2 people are working on the same record?, The one is locked is the later person?, but if the other people, is just to view the record, he/she will be able right?. Let's say other people just want to see the price of an item in the inventory while the other is keying the issuance of the same item of inventory. What could it happen in this situation.

Thank you

no it doesn't automatically lock. And it generally doesn't. matter if you have multiple readers. (ie people reading records, but not writing changes). It only becomes an issue with multiple writers. Optimistic locking (ie no locking) works on the basis that multiple users changing the same record concurrently is likely to be rare in most cases.

Have a look on wikipedia or other articles about record locking strategies, and deadlock/deadly embrace/inconsistent updates. It's all part of dealing with the same thing. Allowing multiple users to access data simultaneously while maintaining the integrity of the data.

In your example, reading the price shouldn't be affected by a user issuing inventory anyway.

 

[hfsitumo2001]

In your example, reading the price shouldn't be affected by a user issuing inventory anyway.

OK Gemma, even tough in issuing form there are inventory table, sales detail, customer table linked. There will be a writing action in sales detail, but in customer and inventory just reading, because just choosing their data in combo box. There will be no problem even though there is a writing/edit on sales details table?

 

[Galaxiom]

There will be a writing action in sales detail,

There should not be anything written to the same record from multiple users. Each sale should be a separate record.

 

[Pat Hartman]

Optimistic locking = no locks
Pessimistic locking = edited record

Regarding queuing workflow. I have one app where batches of documents are scanned every day and the account they are associated with needs to be reviewed so its status can be determined. So the task list opens with the oldest item first. When someone needs to take on a task, they go to this list and generally pick the first item. In order to prevent people from all choosing the same item, when they click the review button, the task form runs an update query that logs the date and time and who is doing the review. The task list now shows that item as being assigned to Joe or Suzie or whoever chose it so the person looking for a task knows to choose something else. Everyone can still look at that client record but they are not allowed to modify it until the outstanding task is marked as complete and the "lock" is removed from the record.

 

[gemma-the-husky]

@hfsitumo2001

The real thing is, we generally do not need to worry about records being changed by other users while we are editing them. In general if you read a record, it doesn't matter what state the record is, as the timing of the read is of little consequence. If we read the record either before or after the record was edited, how would we know, and why would it matter? If we did a process at 9:55 am, and someone changed the record, so that doing the process at 10:05am would have produced a different result, in general the time we do our action should not make any difference to us. I mean that what we do is being handled correctly at 9:55am and also handled correctly at 10:05am. We may get a different result. An order may be accepted at 9:55am. By 10:05am the items may be out of stock, or the customer may have exceeded his credit limit, so the order may be rejected at 10:05am, but both response are correct.

A system often used in retail shopping carts is to let any shopper put an item in the cart, but then the availability is rechecked at the payment stage. So the store has 5 "widget" items in stock, and 10 people have them in their shopping carts. When the sixth person tries to check out, the payment system advises him there are none left, and removes the "widget" from his cart. This is, if you will, optimistic locking.

A different system is often used when booking theatre or plane tickets. We get offered a choice of seat at 9:55am. At 10:00am someone who had reserved 2 seats releases those seats. If we try again at 10:05am we would have been offered those 2 extra seats, but this doesn't affect the integrity of the system. We get the seats we reserved allocated to us for a limited period of time while we complete the checkout. This is pessimistic locking.

Either way works, and it's a matter of taste as to which system gives the best or the desired experience for the shopper and the store.

Now, the main thing is how the system achieves integrity to do either of the above processes, to make sure that the correct number of sales are achieved. (which is done behind the scenes) The pessimistic locking needs to actually prevent multiple users grabbing the same seats by locking them while the active user achieves the reservation. The optimistic system doesn't bother with this. It assumes there won't be a problem, but just before you pay for the goods (again behind the scenes) it rechecks to see if your original request is still valid. If it's not, it warns you that the details have changed, and it can't complete your transaction.

This is why pessimistic locking needs to be very carefully considered. You don't want to lock records and leave them locked forever, as your system won't work correctly. You need to time the locks so that you maintain them for as short a time as possible. Just long enough to deal with the checkout process. You also need a way to reverse the process if the lock cannot be obtained. You can get a situation where you secure Item 1, and are trying to lock Item 2, but someone else has locked item 2, and is trying to secure item 1. Neither of you can make progress, and the entire system can grind to a halt. This is what @The_Doc_Man was discussing earlier.
Some of the locking is managed automatically by the system (the database manager software), but some might need to be added by the programmer. So you generally don't want pessimistic locking at all unless you have a real special need for this approach, as you then need to add a lot of defensive code. Conversely and surprisingly with optimistic locking, the system can do the tidying up for you. It tries to repeat your original request at the final stage, and if it cannot be done now, it CAN tell you that the data has changed and it now can't fulfil your request.

There are still some technical points, but general speaking optimistic locking is sufficient, and that is why it is the default.

 

[hfsitumo2001]

@hfsitumo2001

The real thing is, we generally do not need to worry about records being changed by other users while we are editing them. In general if you read a record, it doesn't matter what state the record is, as the timing of the read is of little consequence. If we read the record either before or after the record was edited, how would we know, and why would it matter? If we did a process at 9:55 am, and someone changed the record, so that doing the process at 10:05am would have produced a different result, in general the time we do our action should not make any difference to us. I mean that what we do is being handled correctly at 9:55am and also handled correctly at 10:05am. We may get a different result. An order may be accepted at 9:55am. By 10:05am the items may be out of stock, or the customer may have exceeded his credit limit, so the order may be rejected at 10:05am, but both response are correct.

A system often used in retail shopping carts is to let any shopper put an item in the cart, but then the availability is rechecked at the payment stage. So the store has 5 "widget" items in stock, and 10 people have them in their shopping carts. When the sixth person tries to check out, the payment system advises him there are none left, and removes the "widget" from his cart. This is, if you will, optimistic locking.

A different system is often used when booking theatre or plane tickets. We get offered a choice of seat at 9:55am. At 10:00am someone who had reserved 2 seats releases those seats. If we try again at 10:05am we would have been offered those 2 extra seats, but this doesn't affect the integrity of the system. We get the seats we reserved allocated to us for a limited period of time while we complete the checkout. This is pessimistic locking.

Either way works, and it's a matter of taste as to which system gives the best or the desired experience for the shopper and the store.

Now, the main thing is how the system achieves integrity to do either of the above processes, to make sure that the correct number of sales are achieved. (which is done behind the scenes) The pessimistic locking needs to actually prevent multiple users grabbing the same seats by locking them while the active user achieves the reservation. The optimistic system doesn't bother with this. It assumes there won't be a problem, but just before you pay for the goods (again behind the scenes) it rechecks to see if your original request is still valid. If it's not, it warns you that the details have changed, and it can't complete your transaction.

This is why pessimistic locking needs to be very carefully considered. You don't want to lock records and leave them locked forever, as your system won't work correctly. You need to time the locks so that you maintain them for as short a time as possible. Just long enough to deal with the checkout process. You also need a way to reverse the process if the lock cannot be obtained. You can get a situation where you secure Item 1, and are trying to lock Item 2, but someone else has locked item 2, and is trying to secure item 1. Neither of you can make progress, and the entire system can grind to a halt. This is what @The_Doc_Man was discussing earlier.
Some of the locking is managed automatically by the system (the database manager software), but some might need to be added by the programmer. So you generally don't want pessimistic locking at all unless you have a real special need for this approach, as you then need to add a lot of defensive code. Conversely and surprisingly with optimistic locking, the system can do the tidying up for you. It tries to repeat your original request at the final stage, and if it cannot be done now, it CAN tell you that the data has changed and it now can't fulfil your request.

There are still some technical points, but general speaking optimistic locking is sufficient, and that is why it is the default.

Thank you Gemma for a very comprehensive explanation.

Frank