CODEWITHSUNDEEP
databaseconcurrencylocking
ekhidna
ekhidna

Reputation: 45

Concurrent editing of same data

I recently came up with a case that makes me wonder if I'm a newbie or something trivial has escaped to me.

Suppose I have a software to be run by many users, that uses a table. When the user makes login in the app a series of information from the table appears and he has just to add and work or correct some information to save it. Now, if the software he uses is run by many people, how can I guarantee is he is the only one working with that particular record? I mean how can I know the record is not selected and being worked by 2 or more users at the same time? And please I wouldn't like the answer use “SELECT FOR UPDATE... “ because for what I've read it has too negative impact on the database. Thanks to all of you. Keep up the good work.

Upvotes: 0

Views: 1087

Answers (3)

Stefan Steinegger
Stefan Steinegger

Reputation: 64628

This is something that is not solved primarily by the database. The database manages isolation and locking of "concurrent transactions". But when the records are sent to the client, you usually (and hopefully) closed the transaction and start a new one when it comes back.

So you have to care yourself.

There are different approaches, the ones that come into my mind are:

  • optimistic locking strategies (first wins)
  • pessimistic locking strategies
  • last wins

Optimistic locking: you check whether a record had been changed in the meanwhile when storing. Usually it does this by having a version counter or timestamp. Some ORMs and frameworks may help a little to implement this.

Pessimistic locking: build a mechanism that stores the information that someone started to edit something and do not allow someone else to edit the same. Especially in web projects it needs a timeout when the lock is released anyway.

Last wins: the second person storing the record just overwrites the first changes.

... makes me wonder if I'm a newbie ...

That's what happens always when we discover that very common stuff is still not solved by the tools and frameworks we use and we have to solve it over and over again.

Upvotes: 2

TomTom
TomTom

Reputation: 62101

Now, if the software he uses is runed by many people how can I guarantee is he is the only one working with that particular record.

Ah...

And please I wouldn't like the answer use “SELECT FOR UPDATE... “ because for what I've read it has too negative impact on the database.

Who cares? I mean, it is the only way (keep a lock on a row) to guarantee you are the only one who can change it. Yes, this limits throughput, but then this is WHAT YOU WANT.

It is called programming - choosing the right tools for the job. IN this case impact is required because of the requirements.

The alternative - not a guarantee on the database but an application server - is an in memory or in database locking mechanism (like a table indicating what objects belong to what user).

But if you need to guarantee one record is only used by one person on db level, then you MUST keep a lock around and deal with the impact.

But seriously, most programs avoid this. They deal with it either with optimistic locking (second user submitting changes gets error) or other programmer level decisions BECAUSE the cost of such guarantees are ridiculously high.

Upvotes: 0

Licantropo
Licantropo

Reputation: 54

Oracle is different from SQL server.

In Oracle, when you update a record or data set the old information is still available because your update is still on hold on the database buffer cache until commit. Therefore who is reading the same record will be able to see the old result. If the access to this record though is a write access, it will be a lock until commit, then you'll have access to write the same record. Whenever the lock can't be resolved, a deadlock will pop up.

SQL server though doesn't have the ability to read a record that has been locked to write changes, therefore depending which query you're running, you might lock an entire table

First you need to separate queries and insert/updates using a data-warehouse database. Which means you could solve slow performance in update that causes locks. The next step is to identify what is causing locks and work out each case separately.

rebuilding indexes during working hours could cause very nasty locks. Push them to after hours.

Upvotes: -1

Related Questions