0

I am using an api to develop an inventory system for a company. I want to be able to allow the first person to select a row(s) from the mysql database to have a lock. The second person should be denied any data from the set of rows the first user has. Is that even possible?

The use case, the information in the database is constantly being added or updated by users. If user A does a select it will always be followed by an update. But if user B selects the information updates it before user A is done, all the work from user B will be lost when user A is done or visa versa.

I have tried to use transactions but it is not stopping a second user from getting the row the first user requests. 

start transaction;
select * from peak2_0.staff where 'First Name'='Aj';

update peak2_0.staff set `First Name` = 'aj' where 'First Name'='Aj';
commit;
AjDed
  • 17
  • 4
  • 1
    Locks are always dangerous as if not released they can really wreck your database. Why not design something idempotent so you can't reserve it twice? You can `SELECT` and `LOCK` but it's not clear what benefits this would have here, if any, as your use case is not explained. This demo SQL changes someone's name? – tadman Jul 08 '19 at 18:16
  • 1
    This is my first major project I have ever done and I am totally alone on this. Do you have a recommendation on how to design such a thing? – AjDed Jul 08 '19 at 18:20
  • It really depends what you're doing. This is a trivial modification, it's atomic, so there's no need for a lock in the first place. You'll also want to establish if you need [optimistic or pessimistic locking](https://stackoverflow.com/questions/129329/optimistic-vs-pessimistic-locking). – tadman Jul 08 '19 at 18:20
  • Transactions are generally meant for a sequence of actions requiring no user interaction; i.e. the program needs to work on data, or apply a set of changes a user has already completed, without other database clients messing with the data or using it in a transitional state. If you want a user to be able to do something like take an inventory item "offline" for several minutes while a user edits it, having a **field** in the table indicating the row is "locked" might be a better solution. – Uueerdo Jul 08 '19 at 18:29
  • Do you want to deny all access to data or just update? I would suggest you still allow view. – kerry Jul 08 '19 at 18:40
  • I will try that @Uueerdo. I am fairly new to this so, thank you!! – AjDed Jul 08 '19 at 18:41
  • @kerry I am ok with allowing the other users to view the data. They just CANNOT change it if someone else has started editing it. – AjDed Jul 08 '19 at 18:42
  • @ AjDed I would go with Uueerdo Create a status field if you go into an update routine, check if the status field is locked, if it is you cannot update, if it is not set, set it then no one else can update. Obviously clear the field when you are finished :-) – kerry Jul 08 '19 at 18:46
  • My 2 cents: change your approach - adopt versioning. If you want to notify someone else's is changing a specific "object" (not a row, but an entire model), use a KV store and put/del entries according to user navigation on your system. When user navigates to the edit page/screen of Customer X, put the customer X's ID on the KV. If someone else visit the edit page of that customer, your page/screen can immediately tell that model is being edited by someone else. Versioning should work fine along with the notification mechanism just described. Locking is generally hard and easy to mess up. – jweyrich Jul 08 '19 at 21:11

1 Answers1

0

As I mentioned in the comments, you can create a field (or two) for "locking" the entry while a user is working on it; more of a "down for maintenance" indicator of sorts, than an actual server lock. You can even make it atomic and recoverable with something like this:

UPDATE someTable 
SET locked_by = client_or_user_id, locked_when = now() 
WHERE [criteria for selected the record(s) being worked on] 
AND locked_by IS NULL
;

You can then select from the table to see if it got your program client id or users id for the lock. "Recoverable" in the sense that, should the client system go down before unlocking the data, a routine process (client side, or MySql event), can release any locks older than a certain amount of time. Alternatively, the original update, and anything that is trying to respect locks can have the standard lock checking condition be tweaked to something like AND (locked_by IS NULL OR locked_when < now() - INTERVAL 15 MINUTE)

If an editing client needs to hold a lock for longer, it can do so just by updating locked_when values further; or you could also/alternatively use a "lock until" field.

Optionally, you could even add a lock reason so clients attempting to access such an entry can be informed why it is unavailable.

Uueerdo
  • 15,723
  • 1
  • 16
  • 21