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.


  • 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. – Robert Harvey Oct 13 '17 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. – Andy Dufresne Oct 13 '17 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. – Robert Harvey Oct 13 '17 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. – Andy Dufresne Oct 14 '17 at 12:32
  • If you're implementing optimistic locking on a database with ACID capabilities, simultaneous updates are already handled. – Robert Harvey Oct 14 '17 at 14:35

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.

  • 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. – Andy Dufresne Oct 13 '17 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. – Andy Dufresne Oct 13 '17 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? – Andrei Socaciu Oct 13 '17 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. – Andy Dufresne Oct 14 '17 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. – Andrei Socaciu Oct 16 '17 at 9:28

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

  • 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? – Andy Dufresne Oct 13 '17 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 – Ewan Oct 13 '17 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. – Andy Dufresne Oct 14 '17 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' – Ewan Oct 14 '17 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. – Andy Dufresne Oct 16 '17 at 3:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.