{"id":120,"date":"2004-05-31T15:02:08","date_gmt":"2004-05-31T05:02:08","guid":{"rendered":"http:\/\/www.malcolmgroves.com\/blog\/?p=120"},"modified":"2004-05-31T15:02:08","modified_gmt":"2004-05-31T05:02:08","slug":"optimistic-locking-in-eco","status":"publish","type":"post","link":"http:\/\/www.malcolmgroves.com\/blog\/?p=120","title":{"rendered":"Optimistic Locking in ECO"},"content":{"rendered":"

One of the ECO Tutorials<\/a> up on the Borland website provides a good introduction to ECO and databases. But I’ve had a couple of different people ask me about how to deal with locking. I’m assuming you’ve read that tutorial<\/a> before reading the rest of this article. Also, I’m assuming that you’re actually using a PersistenceMapper component that persists to a relational database. If you’re using the PersistenceMapperXML component then the rest of this article doesn’t apply to you. Sorry. <\/p>\n

Here’s the problem: Using my ECO application, I load a particular Person object from the database and start editing values. Meanwhile, Damien who’s also running the same application, loads up the same Person object. Given that I have not yet persisted my changes to the database, when Damien loads his instance, he gets the data from the database. He then changes the Firstname property. I persist my changes to the database, and all is fine. However, at some point Damien is going to try and persist his changes to the database. Should his changes overwrite mine? Should his attempt to persist the changes fail? What if I didn’t change the Firstname attribute? Should it still fail?<\/p>\n

What happens when he does try and persist his changes depends on how you, the developer of this app, have configured optimistic locking. <\/p>\n

First, let’s look at where you setup optimistic locking, then we’ll look at what the different options mean. Firstly, you need to turn optimistic locking on. Bring up your ECOSpace, click on an empty area of the designer so that the Object Inspector shows the properties of the ECOSpace. In the ECO | Persistence section, you should see a boolean property called OptimisticLocking. Set this to True. <\/p>\n

But that’s not all. You need to specify the type of optimistic locking you want to occur for each class in your system. Bring up one of your class diagrams, select a class (make sure it’s the class you have selected, and not one of its attributes or methods) and look in the Object Inspector. Once you’ve expanded the Eco sub-property, you should see a property called OptimisticLocking. The default value of this is, funnily enough, Default, but you can also choose Off, ModifiedMembers, AllMembers or Timestamp. In the rest of this article we’ll be looking at what happens when Damien tries to persist his changes to the Person object, depending on which of these values you’ve selected. <\/p>\n

\"\"<\/p>\n

OptimisticLocking set to Off<\/strong><\/p>\n

Setting OptimisticLocking to Off on your class means that no checks are performed before this object is persisted. So, when Damien persists his changes, the following SQL is executed: <\/p>\n

update Person
set Firstname = ‘Barney’
where BOLD_ID = 3<\/p>\n

Not surprisingly, this will overwrite any changes I may have made to the Firstname attribute for this Person. Maybe that’s what you want? Certainly it will execute faster than some of the other setting I’ll mention shortly \ud83d\ude42<\/p>\n

OptimisticLocking set to Default<\/strong><\/p>\n

Default basically means that the OptimisticLocking behaviour is determined by this class’ ancestor. So, if Person descended from some other class, say a Contact class, then how Damien’s Person class behaved in this situation depends on the value of Contact’s OptimisticLocking property.<\/p>\n

OptimisticLocking set to ModifiedMembers<\/strong><\/p>\n

As the name might suggest, this value will mean that before ECO persists the Person object, it will check to see if any of the attributes that have been changed on the class, have also been changed in the database. What this means, in the case of our Person object where Damien edited the Firstname property, is that the following SQL will be executed first:<\/p>\n

SELECT B.BOLD_ID, B.BOLD_TYPE, B.Firstname
FROM Person B
WHERE B.BOLD_ID = 3<\/p>\n

ECO will then check to see if the returned value for Firstname is the same as the value in the class before Damien edited it. If its the same, then ECO assumes that this field has not been changed in the database, and proceeds to execute this:<\/p>\n

UPDATE Person
SET Firstname = ‘Barney’
WHERE BOLD_ID = 3<\/p>\n

However, if after executing the select, it finds that the value returned is different from the Firstname field prior to Damien’s changes, it throws a Borland.Eco.Internal.BoldSystem.EBoldOperationFailedForObjectList exception with the message "Optimistic locking failed for 1 objects".<\/p>\n

OptimisticLocking set to AllMembers<\/strong><\/p>\n

Hopefully by now you can guess this one. Like the previous example, ECO will check the database to see if the values are the same as when this Person object was first loaded, but this time it will check all attributes on the class, not just those that have been changed. So, in our example it will first execute SQL like this:<\/p>\n

SELECT B.BOLD_ID, B.BOLD_TYPE, B.Firstname, B.Lastname
FROM Person B WHERE B.BOLD_ID = 3<\/p>\n

If all fields have the same value as the attributes of our Person did prior to Damien’s changes, it will then execute the following SQL:<\/p>\n

UPDATE Person
SET Firstname = ‘Barney’
WHERE BOLD_ID = 3<\/p>\n

Note that it still only updates the fields that Damien changed, even though it first checks all fields.<\/p>\n

Pitstop<\/strong><\/p>\n

So far I don’t think any of these have been very difficult to grasp. One way to look at them is that each setting gradually increases the requirements that must be satisfied in order for an object to be persisted successfully.<\/p>\n

We started with no requirements (OptimisticLocking = Off), then we went to the requirement that any attributes that are changed must not have been changed by anyone else (OptimisticLocking = ModifiedMembers). We then stepped up a level again, requiring that none of the attributes of the class can have changed (OptimisticLocking = AllMembers). Well the last setting (OptimisticLocking = Timestamp) increases the strictness of these requirements another level again, but does it in a slightly different way.<\/p>\n

OptimisticLocking set to Timestamp<\/strong><\/p>\n

If you set OptimisticLocking to Timestamp on one or more of you classes, you’ll need to go out to your PersistenceMapper component (in your ECOSpace) and change the SqlDatabaseConfig.UseTimestamp property to True (see the picture below if that doesn’t make sense). You’ll also need to invoke the Evolve Database function so that ECO can create you a new table called ECO_TIMESTAMP. This table contains a single field called BOLD_TIME_STAMP, which despite its name, is an Int field (at least in the SQLServer example I’m playing with here).<\/p>\n

\"\"<\/p>\n

So, sticking with the same Person object example above, if we have OptimisticLocking set to Timestamp, here’s what happens when Damien tries to persist his Person object:<\/p>\n

First, ECO will execute the following SQL:<\/p>\n

UPDATE ECO_TIMESTAMP SET BOLD_TIME_STAMP = BOLD_TIME_STAMP + 1<\/p>\n

This does two things. It increments the Timestamp field, and also returns the current value. ECO can then check that this value has not changed since this objects’ fields were loaded from the database. If this value is the same, ECO will then execute this SQL to persist the object: <\/p>\n

UPDATE Person SET Firstname = ‘Barney’ WHERE BOLD_ID = 3<\/p>\n

If it finds the timestamp field has been changed since this object was loaded (ie. the value in the field is greater than the one stored with the object) it will, like the other OptimisticLocking settings, throw a Borland.Eco.Internal.BoldSystem.EBoldOperationFailedForObjectList exception with the message "Optimistic locking failed for 1 objects".<\/p>\n

It should be noted that all classes that are set to use Timestamp as their locking type will share the Timestamp field. So if Person and Appointment are both Timestamp-locked classes, and both persist to the same database, saving an Appointment object has the potential to stop someone else saving their Person object. This is neither good nor bad, it’s just how it works. Sometimes, given certain relationships between classes, this may be the exact behaviour you want. If not, use one of the other techniques.<\/p>\n

Now, obviously this is a very simplistic example, but hopefully it’s given you a feel for how Optimistic Locking works. The ability to assign different locking settings to different classes opens up quite a bit of flexibility. Looking at how each setting works, you might notice that each gradually increases the potential scope that ECO will check before persisting an object: from nothing, to changed fields of an object, to all fields of an object, and finally, with Timestamp, to potentially other classes. And if you want even more control, in a future article we’ll have a look at the concept of Regions.<\/p>\n

If you want to dig deeper, I would recommend you do what I did: Spend a little time in SQL Profiler (or a similar SQL monitoring tool) and play with the various Locking settings and see what SQL is thrown. <\/p>\n

Update: Krishnan<\/a> has some follow up comments, concerns, suggestions, and Jonas<\/a> responds in turn, along with some corrections for my article above. See what I’ve started?<\/p>\n

Technorati Tags: Delphi<\/a>,ECO<\/a><\/div>\n","protected":false},"excerpt":{"rendered":"

One of the ECO Tutorials up on the Borland website provides a good introduction to ECO and databases. But I’ve had a couple of different people ask me about how to deal with locking. I’m assuming you’ve read that tutorial before reading the rest of this article. Also, I’m assuming that you’re actually using a […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-120","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"http:\/\/www.malcolmgroves.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/120","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/www.malcolmgroves.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.malcolmgroves.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.malcolmgroves.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.malcolmgroves.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=120"}],"version-history":[{"count":0,"href":"http:\/\/www.malcolmgroves.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/120\/revisions"}],"wp:attachment":[{"href":"http:\/\/www.malcolmgroves.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=120"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.malcolmgroves.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=120"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.malcolmgroves.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=120"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}