Hi all,

I'm writing a desktop application which uses MySQL database as a data storage. In the application I have documents which are loaded from the database, edited in the client and then saved back to the database.

Now I'd like to implement a same kind of like functionality that for example Word offers when two users try to open the same document at the same time. I mean, first guy, who opens the document, gets read-write access to it and all openings after that lead to read-only versions of the document.

I'm using versioning to give me long transactions for the UI tier, but I don't like the default schema they offer me (the guy, who saves first, succeeds) so I planned to implement this kind of locking by creating DocumentLock persistent object.

When user opens some document, the search is conducted to DocumentLock objects and if there is already existing lock object for opened document, the document is opened read-only (= one boolean property in the Document object is set to true). If there is no such an object, a new lock object is created, persisted to the database and the document is opened read-write.

My problem is that I haven't been able to create a proper strategy for searching and creating lock objects in an atomic way. Either I end up having two read-write documents open or get BatchUpdateException from DB saying that a deadlock has occurred. Using common sense I think that this kind of schema should be implementable without a fear of deadlocks in this way:



How could I implement this kind of transaction schema using Hibernate?

Or is there some better way to implement this kind of "long transaction" locking?


Sincerely,

Jouni Hartikainen

I don't have time right now to explain it in more details, but what you need is an "Offline Pessimistic Lock". This is what Martin Fowler calls it in Enterprise Application Architecture book (is that name correct?). This pattern can be implemented _on top_ of Hibernate, persisting and loading Lock objects with a LockManager that executes Hibernate calls. It's not trivial, but not much code. I'll write a blog entry about it soon...

You also have to be aware of the disadvantages of this pattern! Fowler mentions them, but doesn't emphasise the problems enough. Read Gavin's weblog entry about "Locking in the middle tier" on http://blog.hibernate.org/ and use the pattern only in some parts of your application and never for every data access operation.

I've talked to some other people about that recently and they always had the impression that the _locking_ operation is the issue. It is not, the problem is _checking_ for the lock in every call.

Oh btw, this was a "Google it Yourself" answer :)

OK. Thanks for the quick answer. As I now see, the strategy that I selected was exactly the same than Fowler presents in his book. The problem for me is to figure out how to implement this strategy with Hibernate in easiest and most efficient way. As I already understood, I need to use SERIALIZABLE transaction isolation level and LockMode.UPGRADE to get SELECT ... FOR UPDATE queries to DB, but this kind of strategy just leads me getting SQL exceptions saying that a deadlock happened in the DB. Maybe this is all about the crappy MySQL? Don't know..



I think for me (at least in performance way) the checking of the locks is not that big problem, since documents are opened and closed relatively seldom (user normally opens the document, works with it for half an hour and then closes it etc..).

If you write the article you talked about, please post some info about it here, so that I'll can read it. :) Especially implementation details and instructions are very welcome.


Sincerely,

Jouni Hartikainen

Just for information..

I managed to implement this locking schema by creating a LockManager domain object which contains a Map of Lock objects. When user wants to open a document, the LockManager is first loaded from the database using LockMode.UPGRADE (which effectively forces SELECT ... FOR UPDATE query). When the LockManager is loaded, also all Locks are loaded (because they are in the LockManager's "locks" Map).

When the whole object with locks is in memory, the locks Map is looked if it alreay contains a lock for the document which user is opening. If it contains the lock, the document is opened read-only. If it doesn't contain the lock, a new Lock object is created, added to the locks Map, Document is opened read-write and LockManager is persisted back to the DB (and then also the new lock gets persisted).

All this happens in the same DB transaction which means that no other client can interfere the process after first client has loaded the LockManager object. In fact all other clients are just blocked to their SELECT ... FOR UPDATE query (when they try to load LockManager). This means that I don't even have to check any deadlock exceptions or stale object exceptions in the code.

I tested this on PostgreSQL 7.4.3 using transaction isolation level "read committed" (the default). Didn't test is on MySQL and I think I migrate to PostgreSQL for now on. Thanks to Hibernate it took some 5 minutes to totally change the database backend of my whole application. I would say that's pretty amazing! :)

One negative point in this implementation is that now I got one "stupid" extra table for LockManager. Table is "stupid", because it will always contain exactly one row (I have only one LockManager). Is there any way I could get around this "problem"? Or should I just be happy that I got it working? :)

Another con in this implementation is that now I have to load all locks from the DB to memory to make the check for the existence of a lock. It would be certainly more efficient to make that search in the database and return only empty result list or a list containing already existing lock. Any ideas how to avoid this?


Sincerely,

Jouni Hartikainen