Re: ECO : Optimistic Locking

First off, this post is in reply to a blog entry by Malcolm Groves at: http://blogs.codegear.com/malcolmgroves/archive/2004/06/28/659.aspx on Optimistic Locking done by ECO.

I first thought of replying on Malcolm’s blog site but then thought my entry might deserve a place of its own (presumptuous, but not vain I also come from the Java, J2EE world - and I have a deep respect for a lot of technologies in Delphi and Delphi.NET. Some of views on the topic might be bit off since I might not know how things work exactly (only plain ignorance ;). I have to admit that most of these issues have already been thrashed around in the J2EE world and Borland Enterprise Server provides a piece of functionality that is almost identical by name - but differing in its implementation. You will find a link to this functionality at the end of this post.

Malcolm’s entry stated how Optimistic Locking behaves when set to ModifiedMembers and when set to AllMembers. What caught my attention was that:

• Two SQLs are sent to the database for every update to the database.
• The isolation level for the above two SQLs themselves are seemingly undefined.

Firstly, it seems like sending two SQLs to the database for every update to the database is not a very scalable solution. One, it introduces more RPCs into the picture if the ECO Server-side application and database are on different machines. Two, it imposes a higher load on the database than necessary. That is, there are now more CPU cycles utilized at the database-tier. This could very quickly lead to the database-tier becoming a performance bottleneck which will then ripple up tiers to manifest itself as a scalability problem. This whole issue of two SQLs seems trivial till you actually consider high-concurrency OLTP-like applications where you want squeeze every little bit of performance out of your tiers since you are typically dealing with large amounts of data.

Secondly, the last point assumes that the two SQLs will run in proper isolation. What I mean by this - is not whether the two SQLs will run in the same transaction (which they must - else ECO is doing something horribly wrong); but what the isolation level will be of that transaction. Off the top of my head - I would say you atleast need a REPEATABLE READ isolation level (most database default to READ COMMITTED in which case you still have a problem). Imagine, under a high concurrency, T1 and T2 are two transactions:

    T1 performs a SELECT (prior to its UPDATE)
    T2 performs a SELECT (prior to its UPDATE)
    T1 assumes it can update the database and issues an UPDATE that succeeds
    T2 assumes it can also update the database and issues an UPDATE that also succeeds

I believe the above will occur if you select (pun intended) the default READ COMMITTED isolation level.

So, what is the solution you may well ask? Or more importantly is there a solution?

Yes.

The idea is to kill two birds with one stone. Or in this, to just use one SQL in lieu of two. And what kind of an SQL would that be? Let’s see. In ECO, after the first SQL is executed, it checks to see if the value returned by the database is different from those prior to edits/changes. This implies that ECO is holding this old value somewhere - whether it be an OptimisticLocking mode of ModifiedMembers or AllMembers. So, the smart thing for ECO to do would be to use that information in its WHERE clause. Let me give an example in the context of Malcolm’s example: Damien and I are working with a Person whose first name happens to be ‘Barney’.

Damien makes a change to this Person’s first name and calls him ‘Loony’ and while I make a change and decide to call him ‘Goony’. Assuming the problem in point two (above) does not exist, ECO will issue a SELECT when Damien’s transaction has to succeed. It will find that the database contains ‘Barney’ which is what Damien started out with in the first place. His transaction will succeed.

My transaction will then issue a SELECT, find that the person’s first name is now ‘Loony’ and not ‘Barney’ - which is not what I received in the first place (I received ‘Barney’) and so my transaction will fail. All well - assuming of course that problem in point 2 above does not exist.

The other way of doing things - maybe a bit more efficient - is to let ECO do a bit more work. Since ECO ‘knows’ its old values - that is, the values it receives when it issued the request to get the data to the user, it cannot be that hard for ECO to ‘diff’ these values with what the user had edited/changed. So, ECO should know what columns have actually changed. In this context of this example, it should know that the only change is one to the firstName column. And it also ‘knows‘ what the old value is (Barney) and new value (either Loony | Goony) is. And it could use this information to construct the WHERE clause.

    UPDATE Person
        SET firstName = ‘Loony’
        WHERE Person.BOLD_ID = 3 AND Person.firstName = ‘Barney’

And the other transaction would issue:

    UPDATE Person
        SET firstName = ‘Goony’
        WHERE Person.BOLD_ID = 3 AND Person.firstName = ‘Barney’

In this case, only one of the above two transactions will succeed since the WHERE will fail if it cannot find the given row. And in the case of an Optimistic Concurrency of AllFields, the WHERE clause would contain the values of all columns that the user first read in. So something along the lines of:

    UPDATE Person
        SET firstName = ‘Goony’
        WHERE Person.BOLD_ID = 3 AND Person.firstName = ‘Barney’ AND Person.lastName = ‘Slater’ AND Person.someField = ‘oldValue’ …

So, this approach:

• Uses only one SQL
• Is the ‘correct’ way. No ‘holes’ to be punched through it.

And also does not suffer from any potential scalability problem (side-effect

I’d be interested in hearing other’s opinions on this . . .

-krish

Borland Enterprise Server uses the same approach: http://info.borland.com/devsupport/bes/faq/all_versions/ejb/cmp.html#_how_optimisitic

Share This | Email this page to a friend

Posted by Krishnan Subramanian on July 16th, 2004 under Borland Enterprise Server, General, J2EE |