1

Our application allows users to read/add/edit/delete table records through the UI. To prevent concurrent users from editing the same table row (stale record scenarios) we use a row identifier and row version field in the table. These operations are now exposed through web services too. In order to reuse code the read api was designed to return the row id and row version values from the database so that the same values could be passed in the adjust api to identify a row in the table. So if a user wants to edit rows in a table, he needs to first fetch it using the read api to get the row id & row version for each row. These values need to be then passed to the adjust api call so that the system identifies the right row and perform the edit operation.

I understand that there are two issues with such an api design

  1. Internal database details are exposed through API's
  2. Performance - currently the adjust api call needs to be coupled with the read api call.

Business wise we do have a concept of key fields in the table which define functional uniqueness. We could use these user defined fields to identify the row but how would we prevent concurrent row updates without row version?

Do you have any suggestions on how could the api be designed to cater to the business requirements? Any real world examples that could be referenced.

Thanks.

Improve this question
11
  • 1
    ...but how would we prevent concurrent row updates without row version? -- Databases having ACID capabilities will do this for you automatically. Most SQL relational database systems qualify. Commented Oct 13, 2017 at 5:41
  • They do but this is about restricting simultaneous updates to the same row at the same time. If two users try to update the same row at the same time, one user should get an error. He should reload the row and try an update again since there was a conflict. Commented Oct 13, 2017 at 9:04
  • Properly designed databases handle simultaneous updates to the same row automatically; you just have to choose the method of contention resolution you want. See Andrei's answer for possible strategies. In most typical applications, this kind of contention is pretty rare anyway (most companies don't have two customer service people working on the same account at the same time, for example). Writing to a record is a very brief operation; simultaneous writes seldom occur in practice. Commented Oct 13, 2017 at 14:51
  • We do implement optimistic locking already in our application. Handling simultaneous updates to a record is a functional requirement that we need to implement. Please see my responses to below answers and let me your suggestions. Commented Oct 14, 2017 at 12:32
  • If you're implementing optimistic locking on a database with ACID capabilities, simultaneous updates are already handled. Commented Oct 14, 2017 at 14:35

2 Answers 2

Reset to default
1

This is a topic which basically affects every multi-user application. You can have different users editing the same data item at one time. There are 2 solutions to it:

Optimistic locking: This is pretty much what you have implemented, using a version number on each data item. When you save your data item after editing, you check that the version number in the database is still the one you have loaded. If it's not, it means someone has updated the item in the meantime, so you will show an error message to the user, and they will have to reload the data item and reapply their changes.

This solution works well for cases when the probability of concurrent change is small, and for when the changes to an item are relatively small, i.e. the user would not lose a couple hours of work if someone changed their edited data item.

Edited: To be more specific to your actual case: I don't see a problem with exposing version numbers, as they are not confidential information. You would return them as part of the edited row so there wouldn't be any performance impact either.

Depending on how public your API is, and how much you trust your clients, you could work around revealing IDs and Versions it by creating temporary aliases, and storing them in a table or cache. So the client would get ID abcd, which internally to you maps to Database ID 1 and version ID 3. However you'd have to do this whenever you return a list of records to the client, from which they would choose which one they want to edit, so it would be a big overhead.

Pessimistic locking: You would implement a lock service, and would call it to obtain a lock on your data item before being able to edit it. After saving you would release the lock. This means that only one user can edit the item at one time.

However it comes with certain drawbacks. For one you need to implement the lock service, then you have to implement some timeout mechanism, for the case that someone starts editing something and then goes home or on holiday. You might also need a way for an administrator to release locks manually on data items, if there are locks waiting for a timeout, but the users need to work on them urgently.

Improve this answer
7
  • I understand the locking approaches and as you realized we do implement Optimistic locking. The question is how do we expose an api for updating records without exposing the database identifier and version. Commented Oct 13, 2017 at 9:42
  • I do not follow the solution being recommended here. Are you saying what we do currently is not bad? To explain the performance impact - imagine the api caller wants to edit the record. Currently the api requires the row id for row identification. Since he would not have that, he would have to make an api call to read the records that are to be edited and then call adjust api passing the row ids. Commented Oct 13, 2017 at 11:15
  • Regarding performance: How can the caller know which record they want to edit without fetching a list of records first? Are the record "keys" well known to the clients, so that they can call the edit method without retrieving a list of records first? Commented Oct 13, 2017 at 13:44
  • Yes, you are right. The keys could be known by the client upfront and hence I mentioned it as a performance issue. Commented Oct 14, 2017 at 12:27
  • If the client already knows the key, it does not need to know the current version number to fetch the record, it always receives the latest version, including its number. When updating, it sends the record data along with the last known version number to the server, and the update will succeed or fail depending on whether the record was updated by someone else in the meanwhile. Assuming that the client needs at least once to fetch the whole record before issuing any update requests. Commented Oct 16, 2017 at 9:28
0

Add a Lock operation.

  • Client sends a randomly generated LockId
  • Sever checks to see if record is locked. if not locks it and stores the LockId against that row
  • If already locked return "Locked for editing"

Then the Edit operation.

  • Client sends changes and previously used LockId
  • Server checks LockId on the record matches the one sent. If so, changes and unlocks

This is the most basic version of the pattern. You can improve it with timeouts, edit requests, etc

Improve this answer
5
  • This basically means we implement pessimistic locking right? The approach has an advantage that the we don't have to expose database internals but it has a huge impact on the current functionality. Since the suggested change is core to the backend service, it would lead to changes in the UI code too. Is there an approach where the back-end and the front-end code do not change but we just tweak the web api to be exposed? Commented Oct 13, 2017 at 9:58
  • its not really either, its a way of informing the clients whether anyone else is currently editing the record. If you get many collisions you could add further messages as I say "someone else wants to edit this record" or even a dual editing thing like google docs Commented Oct 13, 2017 at 11:23
  • Ok. To implement this capability we need following changes in the application 1. Introduce a lockid column in the table. 2. Add a web api to apply a lock to the record before calling the adjust operation. 3. The UI operations that allow record updates should respect the lockId column value before performing the update. Step #3 seems to add a lot of scope in the core adjustment capability that is already implement. Let me know your thoughts. Commented Oct 14, 2017 at 12:43
  • well I would add a new Locks table rather than add more cols to your existing table. you can then reuse for all the tables and have it as a separate API and maybe a validation attribute to exiting apis? but your question is "how would we prevent concurrent row updates without row version?" if row version is fine for you then carry on, but if you get lots of "sorry someone else has changed that row while you were typing" then you need some way of communicating the simultaneous editing to other users, which in turn requires they register with the server as 'editing that row' Commented Oct 14, 2017 at 13:02
  • No we don't get a lot of simultaneous update errors and hence we implemented optimistic locking. The problem with row version are the ones I mentioned in my original post - 1. exposes internal database details. 2. Mandates the api caller to call the read api before calling the adjust api. Commented Oct 16, 2017 at 3:14

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.