More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
Optimistic versus Pessimistic locking in Sybase?
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
... 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.
(Continued on next question...)
