Welcome to the SRP Forum! Please refer to the SRP Forum FAQ post if you have any questions regarding how the forum works.
Database table column locks with Lock()/Unlock() subroutines
in OpenInsight
I want to lock specific fields (with Lock()) in a database table in some situations. After I call Unlock() to remove the column-locks, all of the three editors report that the row is still locked, and RTI_Lock_Owner() still returns a lock-owner. Has anyone experience unlocking fields of a record such that the editors no longer report a locked record and RTI_Lock_Owner() returns empty?
(I suppose that the editors and RTI_Lock_Owner() are not sufficiently fine-grained to report locks on specific columns, but I thought that the lock table would show the entire row as unlocked once the column locks were removed.)
(I suppose that the editors and RTI_Lock_Owner() are not sufficiently fine-grained to report locks on specific columns, but I thought that the lock table would show the entire row as unlocked once the column locks were removed.)
Comments
I am able to duplicate the issue. Lock and Unlocking columns still leaves the record locked. You have to explicitly unlock the record yourself, which seems like a bug to me but I could see an argument for it being a design feature. One rationale is that OI does not maintain a list of columns you've locked. Thus, it doesn't know when you have unlocked all columns. It leaves this to you, as the developer, to make that decision.
However, if that were the case, then I think they should have provided another Boolean argument that allows us to unlock the record along with the columns.
What I find interesting is that there really is no such thing as a column lock. The Lock() routine is a wrapper around the Lock statement. The Lock statement does not have any syntax for identifying columns. This got me curious so I debugged the LOCK.RECORD primitive of an MFS I installed on a table. I then column locked a record.
I got two lock requests. One for the record KeyID and another for the KeyID : ColumnName. This is what I suspected was happening. A bogus key for the Lock statement was created. Since OI uses semaphore locking, this is entirely possible (and it is something we do quite often to control process workflow).
The UNLOCK.RECORD primitive only got one request: KeyID : ColumnName. This matches our test results.
That is interesting. Why the row does not automatically unlock when unlocking a column is now obvious.
Yes, I did wonder precisely how Lock() locks columns because of that.
I tested just now, and it was a complete surprise to me. I had not realized that, as long as the table handle is valid, one can 'lock' a nonexistent row, and OI merely adds it to a lock-table (or something like that since I don't understand the minutiae of OI lock-handling).
Thanks very much for the explanation. I also see now that my idea to merely unlock the real KeyID won't work. The 'lock' on the bogus KeyID : ColumnName will still be in place, preventing another lock on the column. So two steps are needed:
Unlock(Table_name, KeyId, ColumnName) unlock Table_handle, KeyId ...Know that you know about creating your own key for lock purposes, you can also use the GetKeyIDLock / ReleaseKeyIDLock services from the Database_Manager module. It doesn't support the passing in of multiple keys at once (although that would be an easy custom enhancement), but it might be a cleaner solution for you.
Yet again something I knew nothing about. I will definitely investigate.
I have absolutely no idea about this. See my answer below.
Yes, that is what I observed.
I'm somewhat curious about your use case for column locking, but that a secondary matter. Since I've given you an explanation of how locking works in OI as well as an alternative tool for locking, why don't you just handle this yourself rather than rely upon the Lock() routine? That way you can guarantee the dummy KeyID won't match a real KeyID.
Your idea to avoid the Lock() routine was something I considered. Certainly I can place a character between KeyId and ColumnName (a row marker apparently works fine) and then enforce in the code an exclusion of that character in actual key IDs, but we have a lot of code and this sort of check for illegal characters (e.g. quotes, commas, ...) is not done everywhere I am told.
Since whatever I implement to guarantee a unique KeyID will not be the same as what Lock() provides, I would need to ensure also that future programmers use my "solution" and not Lock()/Unlock().
For the time being I think I will place the column-locking on the backburner. I have implemented error-logging now; I am curious to see how much lock contention will occur in practice with the ordinary row-locking (also a new implementation for this particular table).
Given the operation of the functions Lock()/Unlock(), they don't seem to yield any benefit currently for locking columns, as you say. I think what is missing is real lock capability for columns in OI, so that, as one can lock an existing row without locking the whole table, a column could also be locked without locking the whole row.
(Along those lines, I've noticed that with a table column from DICT.TABLE opened in the editor, the Table Builder opens that column read-only, and vice versa, whereas the other columns are read-write. Edit: Ah yes, because those column definitions in the table builder are rows in DICT.TABLE.)
Locking in OI is fundamentally a social contract, so ALL locks allow the potential for data to be overwritten. You trust that other processes will respect existing locks.
I think the main difference here is that rows in a table are isolated from other rows in a way that is different from the ways columns are isolated from other columns. This is born out by the fact that the ReadV and WriteV statements still go through the READ.RECORD and WRITE.RECORD primitives. That is, the entire record is still read and written.
As an experiment, I performed an analysis to see how ReadV/WriteV differs from Read/Write. In a nutshell, WriteV does an internal Read before it performs its Write. This makes sense, it wants the latest version of the record on disk before it updates the specific column value and then writes it back. So, oddly enough, if your code depends upon performing a ReadV first, then I think it is more performant to just rely upon Read and Write. This assumes, of course, that the time gap between the Read and Write is very small so that the record is relatively fresh by the time the Write statement is executed.
One possible solution for you is to change the schema for this table to be a multipart key: Date * Process. Then it will only contain one value and you can have the atomic level locking that you desire.
A multipart key may be a way forward eventually (eventually, because of the large amount of work that would require throughout the code). I recall that we touched on this during the basic OI training you provided Lucas and me early last year. There is a whole lot more to OI of which I've seen only a glimmer, and I reckon I ought to revisit that training and extend it with the intermediate "edition."
I have run a test, adding a second key to a table. I have read a column value from an existing record identified by a single key, and then I have written that value to a new record identified by a new two-part key.
This works, though I don't know whether mixing single keys and two-part keys in the same table is valid. All the defined columns are available, whether one use a single key or a key in two parts. But in the two-part key I only want to use some few new columns I defined (as a way to recover from a stale-data overwrite due do the absence of locking or a non-write due to lock-contention). So probably a better approach would be a new multikey table solely to store the recovery information.
That depends on how you want to define valid. It is certainly valid from the standpoint that the flexibility of OpenInsight's database will allow it. Since dictionaries are external to the data tables (and thus are there for descriptive, rather than prescriptive, purposes) then low-level transactional processes do not make any distinctions between single part and multi-part keys.
You didn't even need to create a second key to your table (I'm assuming this is what you did in the dictionary). Keep in mind that a multi-part key is nothing more than a string with asterisks. From that perspective, what might appear as a multi-part key could still be viewed as a single part key - especially if you only have a KEY 0 defined in the dictionary. If the asterisks makes you uncomfortable (I'm speaking broadly, not to you specifically) then substitute them for another character: a dash, a plus, a slash, etc.
That is what I did; and multipart key as string did not occur to me. I think I unconsciously assumed the conceptual view of a multipart key to be the actuality rather than simply a way of looking at it, probably abetted by my background with explicitly and statically typed variables.