Question about accessing data

Question

If two users are accessing same database table, How do we prevent users from overwriting each other’s data? Thank you, -Nimesh

Answer 1

There is no straightforward way to do that. You could use transactions to make sure that updates are resolved correctly (i.e. that you do not get partial updates) but you would probably lose the first set of changes as it will be overwritten by the second.

Two options you probably have are:

• implement some locking mechanism on the application level (i.e. when someone start editing a record put some flag somewhere which would prevent another user from doing the same)
• implement version control so each time someone writes to the database a new record is created. This way both sets of data will be in the database and you could have a logic in your application to merge them or select the one you like

Answer 2

Look at This discussion for a review of different strategies or techniques for database locking in an application.

Answer 3

Well, that depends. When you're editing a table, it's not like opening a file up in MSWord or something.

You are issuing commands that specifically add or alter one row or a group of rows at a time, so unless two people are editing the same row, then changes by both users will be preserved.

Tables can also be locked, either implicitly while you are performing an operation (or within a transaction), or explicitly if you know exactly what it is you want to prevent edits to. Most databases provide "row-level locking", which means the entire table does not need to be locked for every operation.

It gets a lot more complicated when you start thinking about transactions and MVCC. If you let us know a bit more information about what exactly the situation is you're interested in, we can provide you with some more specific assistance.

Answer 4

The slickest approach that I've ran across is to use an additional field called last_actv_dtm that is updated every time someone modifies the record. Assuming that your application queries the record first you should have the last_actv_dtm for the record that is being modified.

Use this SQL to perform the update.

UPDATE tab1
SET
    col1 = ?
    , col2 = ?
    , last_actv_dtm = GETDATE()
WHERE
    pkcol = rec.pkcol
    AND last_actv_dtm = rec.last_actv_dtm;

This will only update the row if it has not been modified since the application selected the record.

Answer 5

This is a big question with no easy answer. It call comes down to how willing are you to block one user while another user is working on it, and how do you prevent deadlocks and lousy performance while it's happening. Also, are you trying to prevent one user from updating the same row as the other, or just updating a different row in the same table? If user A updates the row, and then user B attempts to update the row, should it fail, update, or silently ignore it? Once you define the problem more, you can decide if need table level locks, row level locks, transactions, and whether you need various levels of transaction isolation.

Answer 6

Thank you all for your help!

Answer 7

Some database servers support a construct like the following:

SELECT column FROM table WHERE something = 'whatever' FOR UPDATE;

Which locks all the rows returned by the select until either a COMMIT or ROLLBACK are issued.

MySQL (InnoDB) and Oracle are two databases that support this.