Q8.4: Optimistic versus Pessimistic locking?

This is the same problem another poster had ... basically locking a record to ensure that it hasn't changed underneath ya.

fcasas@ix.netcom.com has a pretty nifty solution if you are using ct-lib (I'll include that below -- hope it's okay Francisco ... :-)) ...

Basically the problem you are facing is one of being a pessimist or an optimist.

I contend that your business really needs to drive this.

Most businesses (from my experience) can be optimistic.

That is, if you are optimistic that the chances that someone is going to change something from underneath the end-user is low, then do nothing about it.

On the other hand, if you are pessimistic that someone may change something underneath the end-user, you can solve it at least as follows:

Solution #1

Use a timestamp on a header table that would be shared by the common data. This timestamp field is a Sybase datatype and has nothing to do with the current time. Do not attempt to do any operations on this column other than comparisons. What you do is when you grab data to present to the end-user, have the client software also grab the timestamp column value. After some thing time, if the end-user wishes to update the database, compare the client timestamp with what's in the database and it it's changed, then you can take appropriate action: again this is dictated by the business.

Problem #1

If users are sharing tables but columns are not shared, there's no way to detect this using timestamps because it's not sufficiently granular.

Solution #2 (presented by fcasas)

... Also are you coding to ct-lib directly? If so there's something that you could have done, or may still be able to do if you are using cursors.

With ct-lib there's a ct_describe function that lets you see key data. This allows you to implement optimistic locking with cursors and not need timestamps. Timestamps are nice, but they are changed when any column on a row changes, while the ct_describe mechanism detects changes at the columns level for a greater degree of granularity of the change. In other words, the timestamp granularity is at the row, while ct_describes CS_VERSION_KEY provides you with granularity at the column level.

Unfortunately this is not well documented and you will have to look at the training guide and the manuals very closely.

Further if you are using cursors do not make use of the

[for {read only | update [of column_name_list]}]
of the select statement. Omitting this clause will still get you data that can still be updated and still only place a shared lock on the page. If you use the read only clause you are acquiring shared locks, but the cursor is not updatable. However, if you say
update [of ...
will place updated locks on the page, thus causing contention. So, if you are using cursors don't use the above clause. So, could you answer the following three questions:
  1. Are you using optimistic locking?
  2. Are you coding to ct-lib?
  3. Are you using cursors?

Problem #2

You need to be coding with ct-lib ...

Solution #3

Do nothing and be optimistic. We do a lot of that in our shop and it's really not that big of a problem.

Problem #3

Users may clobber each other's changes ... then they'll come looking for you to clobber you! :-)