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

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.)

Comments

  • I don't know the underpinnings of RTI_Lock_Owner(), but I have no doubt that editors only look for record locks.
  • The record was not locked when the none of the fields were locked. I then locked a field and later unlocked it. At this point I expected the record also to be unlocked (last field unlocked would also remove the lock on the record), but not so. To get around this I can resort to explicitly unlocking the record after locking a field within the record, which seems to work.
  • Vince, sorry. I don't think I read your original post correctly when I first responded. Your second post set me straight.

    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.
  • edited May 2023
    One rationale is that OI does not maintain a list of columns you've locked.

    That is interesting. Why the row does not automatically unlock when unlocking a column is now obvious.

    The Lock statement does not have any syntax for identifying columns.

    Yes, I did wonder precisely how Lock() locks columns because of that.

    ...another for the KeyID : ColumnName... A bogus key for the Lock statement was created.

    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 ...
  • Just a quick correction to your code (which I assume was not tested but quickly typed out). Unlock() uses the name of the table not the handle to the table. You can also use Unlock() for the record key itself without having to resort to the Unlock statement.

    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.
  • You are indeed correct, Don (now corrected in the above post: 'Table_handle' -> 'Table_name'). It was quickly typed out, and my real code uses the table name as you guessed, not the handle.

    GetKeyIDLock / ReleaseKeyIDLock services from the Database_Manager module

    Yet again something I knew nothing about. I will definitely investigate.
  • @Don, now that I have had time to reflect on how the Lock function handles column-locking, do you know if OpenInsight can guarantee that the dummy KeyID won't match a real keyID for an existing row? As I understand it, a key ID for a database table row can in principle use any character that OI recognizes. So I wonder if a key ID in a lock table will always be unique. Is ColumnName concatenated with KeyID with nothing between them?
  • do you know if OpenInsight can guarantee that the dummy KeyID won't match a real keyID for an existing row?

    I have absolutely no idea about this. See my answer below.
    Is ColumnName concatenated with KeyID with nothing between them?

    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.
  • My use case is a situation in which a single table can be written from dozen of places in the program. The keys IDs for the rows are dates. Potentially different parts of the program over a network can write to the same row at the same time if they need to process something for the same date. However the columns in this table are for quite different things, for example a column for a despatch, one for a purchase, one for an order and so on. Essentially the table (it is an audit table) holds metadata for other tables. My idea to lock specific columns was to reduce the chance of lock contention.

    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).
  • I assume you are just using the WriteV statement to update these individual date fields?
  • edited May 2023
    That's correct. I'm not sure on best practice here. So all I've done is go back to my concurrent programming 101 principles (which have some applicability for a database accessed over the network, though in a single-threaded environment), and noting that the OI reference keeps saying to lock to be certain of data correctness.
  • I think your principles are sound but I don't think they are applicable to OI. Given that using Lock() against a column also locks the record, I question whether locking a column does anything meaningful. If anything, it seems to me that more overhead is occurring because more locks are being requested.
  • edited May 2023
    I see your point. (Oh, the elephant in the room.) The locking would have to be only on the column (assuming one can use a unique dummy keyID); hence avoid Lock(). For this particular table, which does not have a window front-end with default row-locking, it might be feasible. But I would still have to ensure that all the code operates the same way. Even so, no editor can currently recognize a column lock, so the potential for data to be overwritten still exists.

    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.)
  • Even so, no editor can currently recognize a column lock, so the potential for data to be overwritten still exists.

    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 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.

    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.
  • Very interesting, your remarks on ReadV and WriteV. I may need to rethink their use in some cases. For the occasional column-write, though, I suppose the code will be slightly neater with a ReadV reading a value directly into a variable.

    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.
  • I don't know whether mixing single keys and two-part keys in the same table is valid.

    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.
  • 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.

    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.
Sign In or Register to comment.