CODEWITHSUNDEEP
databasemultithreadingnhibernateconcurrencylocking
Devkev
Devkev

Reputation: 119

Database- or user-level locks

I'm currently facing the following problem:
I have a C# .NET application connecting to a database (with the use of NHibernate). The application basically displays the database content and lets the user edit it. Since multiple instances of the application are running at the same time (on the same and on different workstations) i'm having concurrency problems as soon as two users modify the same record at the same time.

Currently I kind of solved the issues with optimistic locking. But this is not the perfect solution since one user still looses its changes.

Now i came up with the idea of having the application lock an entry every time it loads a new one from the database and release the lock as soon as the user switches to another entry. So basically all entries which are currently displayed to the user are locked in the database. If another user loads locked entries it will display them in a read-only mode.

Now to my actual question:
Is it a good idea to do the locking on database level? Which means i would open a new transaction every time a user loads a new entry and lock it. Or would it be better to do it through a "Lock Table" which holds for example a key to all locked entries in a table?

Thanks for your help!

Upvotes: 1

Views: 686

Answers (1)

dezhik
dezhik

Reputation: 1030

Is it a good idea to do the locking on database level?

Yes, it is fine in some cases.

So basically all entries which are currently displayed to the user are locked in the database.
...
Or would it be better to do it through a "Lock Table" which holds for example a key to all locked entries in a table?

So you lock a bunch of entries on page load? And when would you release them? What if the editing will take lots of time (e.g. had started editing entry and then went for a lunch)? What if user would close the page without editing all these locked entries, for how long entries would remain locked?
Pessimistic locking and "Lock Table" help to avoid some problems of optimistic locking but bring new.

Currently I kind of solved the issues with optimistic locking. But this is not the perfect solution since one user still looses its changes.

Can't agree that this is loosing, because in your case if validate and commit phases are performed as a single atomic operation then entry wouldn't be corrupted and only one transaction would be successful (let suppose it is the 1st), another would be rolled back (2nd).
According to NHibernate's Optimistic concurrency control

It will be atomic if only one of these database transactions (the last one) stores the updated data, all others simply read data.

The only approach that is consistent with high concurrency and high scalability is optimistic concurrency control with versioning. NHibernate provides for three possible approaches to writing application code that uses optimistic concurrency.

So the 2nd transaction would be gracefully rolled back and after that user could be notified that he has either to make new edit (new transaction) or skip this entry.

But everything depends on your business logic and requirements. If you don't have high contention for the data and thus there wouldn't be lots of collisions then I suggest you to use Optimistic locking.

Upvotes: 1

Related Questions