Question Re: Need help designing a locking strategy in Technology Q&A

Need help designing a locking strategy

Former Member — Sun, 06 Aug 2017 15:21:56 GMT

Hello,

I need to lock a table row for a "long" period of time. I have a row containing a BLOB from which a user is presented with a screen of information that they can interact with for a long (perhaps hours) period of time. I want the first user that accesses this row to have update ability and all others that access the row to be prevented from altering any row information. Once that first user is done then another user can obtain "write" permissions.

The way this could be done is to "set a flag" in the row (or elsewhere) when the row is first accessed and in code test to see if the flag is set and if so block attempts to alter the row data. However, this has the vulnerability that if that first user crashes or is disconnected in some other way we are left with a permanently locked row (requiring an Admin to detect the problem and then take action to reset the row). What I have implemented instead uses Level 0 Isolation along with:

SET TEMPORARY OPTION "blocking" = 'ON'; SET TEMPORARY OPTION "blocking_timeout" = '10000';

This allows the first user to get a Write lock and other users will get an error so the client code can act as needed. It takes advantage of the fact that the server (as I understand it) constantly monitors connections and if it detects that a connection has died it frees all associated locks. That takes care of the connection dying problem and works well except that we are leaving the lock in place for perhaps several hours. As I understand it that ties up a db connection for all of that time and, at a minimum, makes db connection pooling ineffective, not to mention we are probably going to hit some server limit on connections. This is for a SQLAnywhere 17 High Availability system.

What can you comment on/recommend for a better way to accomplish this:

1) Am i correct in my understanding that the server will free the lock if the connection dies?

2) Are my fears about exhausting the available connections correct?

3) Can you recommend a better way to keep a "lock" of some time on a row in place?

Thank you.

Re: Need help designing a locking strategy

VolkerBarth — Mon, 07 Aug 2017 04:12:56 GMT

As I understand it that ties up a db connection for all of that time

AFAIK, a blocked transaction also ties up a worker, which is a more important resource than a connection (although with v12 and above, the number of workers (aka "multiprogramming level") will be increased automatically by default), so that might be a problem or not.

How exactly does the first transaction get its write lock? By really doing an UPDATE on the row and leaving that transaction open until the user finishes work, or do you issue a blocking SELECT, say via a cursor?

FWIW, here's a thread dealing with a different situation ("unit of work") but also with "long transactions" and with several solutions discussed. With just a few superficial thoughts, I'd think a "non-blocking" solution might be to modify a flag in the table that a user is starting to "do work" (say, by storing his connection number within the row and then committing) and to disallow other users to modify the row when that flag is set. A disconnect event could make sure that the row is "unlocked" when the connection unexceptedly dies. That would not block other users.

However, you would have to implement your own "notification system" to tell those that the other user's work is done, if that is a requirement, too. On a database level, you could both use "MESSAGE TO CLIENT FOR CONNECTION" / "WAITFOR ... AFTER MESSASE BREAK" or v17's new mutex objects, however, those "waits" would again block the workers servicing the according connections.

Re: Need help designing a locking strategy

Breck_Carter — Mon, 07 Aug 2017 06:57:39 GMT

I need to lock a table row for a "long" period of time.

Since you are dealing with a single row, and seem to be looking for a simple solution, you may not need the techniques discussed in What are best Practices for Unit-of-Work Lock Management?.

One way to lock a row against updates is to update the row yourself, right away, before the "long period of time" begins... and let SQL Anywhere's default locking and blocking mechanisms do all the work for you.

Volker mentioned this technique:

By really doing an UPDATE on the row and leaving that transaction open until the user finishes work.

One way to do that is to (a) add "update count" or similar small column to the row, and (b) have all connections "signal intent" to update the row by immediately updating the small column without comitting.

This technique does NOT require any of the following:

changes to isolation level
fiddling with database options
special code to release locks when Bad Things Happen

This technique also does not fill up the rollback log with blob data for a long period of time because although the whole row is locked, the whole row is not necessarily written to the rollback lock (e.g., when only a small column is actually updated).

As Volker noted, for each connection that is blocked and waiting, you will be using up one extra server-level ActiveReq (the number of server workers handling client-side requests) out of the MultiProgrammingLevel pool (the maximum number of requests that can be processed at one time)...

...so the question is, how many users are you expecting to update the same blob at the same time? ( on average, one, right? )

The following demo assumes you have control over the application architecture, and you can force every connection to attempt the intent-signalling UPDATE before proceeding with their work on the blob.

Set up for testing

CREATE TABLE t ( 
   pkey         INTEGER NOT NULL DEFAULT AUTOINCREMENT PRIMARY KEY,
   update_count INTEGER NOT NULL DEFAULT 1,
   blob         LONG BINARY NOT NULL );

INSERT t ( blob ) 
VALUES ( xp_read_file ( 'C:\\\\projects\\\\foxhound_benchmark_inventory_db\\\\inventory17.db' ) );

-- Show that an INSERT doesn't have to put anything in the rollback log.

SELECT DB_PROPERTY ( 'RollbackLogPages' );

DB_PROPERTY('RollbackLogPages')
'0'

-- Show that an UPDATE of the blob DOES put a lot of data in the rollback log.

UPDATE t
   SET blob = xp_read_file ( 'C:\\\\projects\\\\foxhound_benchmark_inventory_db\\\\inventory16.db' )
 WHERE pkey = 1;

SELECT DB_PROPERTY ( 'RollbackLogPages' );

DB_PROPERTY('RollbackLogPages')
'11423'

-- Show what's in the table, ready for concurrency testing.

COMMIT;

SELECT @@SPID, pkey, update_count, LENGTH ( blob ), @@VERSION FROM t ORDER BY pkey;

@@SPID,pkey,update_count,LENGTH(t.blob),@@VERSION
1,1,1,72130560,'17.0.7.3399'

Connection 1 signals intent to update the blob

UPDATE t
   SET update_count = update_count + 1
 WHERE pkey = 1;

SELECT @@SPID, pkey, update_count, LENGTH ( blob ), @@VERSION FROM t ORDER BY pkey;

@@SPID,pkey,update_count,LENGTH(t.blob),@@VERSION
1,1,2,72130560,'17.0.7.3399'

-- Show that this UPDATE didn't put anything in the rollback log.

SELECT DB_PROPERTY ( 'RollbackLogPages' );

DB_PROPERTY('RollbackLogPages')
'0'

Connection 2 blocked from signalling intent

UPDATE t
   SET update_count = update_count + 1
 WHERE pkey = 1;

-- The block details as shown by Foxhound...

       Blocked By:  1 / dba / Breck / - / ddd17-1 
     Block Reason:  Row Transaction Intent, Row Transaction WriteNoPK lock on dba.t 
 Locked Row Query:  SELECT * FROM dba.t WHERE ROWID ( t ) = 40370176;
   Last Statement:  update  
                      "t"
                      set "update_count" = "update_count"+1
                      where "pkey" = 1
   Last Plan Text:  ( Update [B]
                    ( IndexScan t t )
                    )

Re: Need help designing a locking strategy

VolkerBarth — Mon, 07 Aug 2017 07:40:08 GMT

However, this has the vulnerability that if that first user crashes or is disconnected in some other way we are left with a permanently locked row (requiring an Admin to detect the problem and then take action to reset the row).

Just to comment on that: If you have to "clean up" data for lost connections/crashed applications, a Disconnect system event can be used to do so automatically, such as the sample from this FAQ:

SET OPTION PUBLIC.disconnect_procedure

That is, if you really need to "flag" the "start to edit" by the first updater and have to commit that (so other users see the modified flag), a Disconnect event should solve the problem of manually having to "clean up" that flag, particularly when you store the connection number of the according user, since the event can easily identify the connection number.

Re: Need help designing a locking strategy

justin_willey — Mon, 07 Aug 2017 08:36:41 GMT

nice :). I particularly like the way it doesn't need any clearing up if the connection is lost (or the db crashes).

Re: Need help designing a locking strategy

VolkerBarth — Mon, 07 Aug 2017 09:56:23 GMT

(or the db crashes)

OK, clean-up for that one is quite difficult to implement even with a "Disconnect" event...:) (Reminds me of the question about a missing "DatabaseShutdown" event, as opposite of the existing DatabaseStart event...)

Re: Need help designing a locking strategy

justin_willey — Mon, 07 Aug 2017 10:27:03 GMT

Yes - clean-up code for these kind of things needs to go in a Database Start event as well as in Disconnect events. That's part of what makes Breck's solution so neat.

Re: Need help designing a locking strategy

VolkerBarth — Mon, 07 Aug 2017 10:48:50 GMT

Well, I'd still wonder if a user connection should really be blocked that long - what will the UI tell the user? "Please wait until user XY has finally done with that thing...", even if that may take several hours?

For longer transactions, I guess it might be more reasonable to set option "blocking_timeout" to a small timespan (e.g. some seconds) and return the default blocking timeout error to the application so a user might do other work and try lateron (aka "polling" whether the row is still blocked), without blocking a connection for a long time... (All that would still work with Breck's scenario, without additional clean-up overhead.)

But of course, that behaviour is application-specific, so only AIK can tell:)

Re: Need help designing a locking strategy

Breck_Carter — Mon, 07 Aug 2017 14:59:06 GMT

what will the UI tell the user?

I know what the user will say...

Re: Need help designing a locking strategy

Breck_Carter — Mon, 07 Aug 2017 15:12:29 GMT

"DatabaseShutdown" event

If you could have that, could you also have a "Disembark" event?

Re: Need help designing a locking strategy

VolkerBarth — Tue, 08 Aug 2017 03:33:01 GMT

Well... a DatabaseShutdown system event would be helpful for cleaning up a controlled shutdown, say, for saving the current cache contents in v17 or for other clean-up stuff... Of course it would possibly not be callable when the database server crashes or when a database assertion appears... But I'm quite sure in most cases databases are shutdown intentionally and controlled.

Of course part of such clean-up can be delayed for the next DatabaseStart event, however, properties as the former cache contents cannot.

Re: Need help designing a locking strategy

Vlad — Tue, 08 Aug 2017 03:37:43 GMT

Instead of the DatabaseShutdown event handler, I'd propose to design the application in the way that it can recover its data/state/what_should_be_cleaned when the DB starts.

If the shutdown process is controlled, this event is not needed. If a crash happens, this event is not fired 🙂

Re: Need help designing a locking strategy

Breck_Carter — Tue, 08 Aug 2017 05:32:58 GMT

I'm probably the last person who should post a flippant Julie Andrews gif... Foxhound desperately needs what Volker suggests (a DatabaseShutdown event) but Vlad's reality intrudes: Foxhound must (try to) do everything in the DatabaseStart event.

The point "in most cases databases are shutdown intentionally" certainly applies to embedded databases.

Re: Need help designing a locking strategy

Former Member — Tue, 08 Aug 2017 16:44:41 GMT

Hi Volker,

Thank you very much for your reply. You have made me aware of capabilities that I did not know were in the server and they look very powerful. Your first suggestion makes sense to me and it solves the problem of leaving the connection open for long period time. I have studied the example in the link you gave me at SET OPTION PUBLIC.disconnect_procedure and I believe I understand it (I am new to setting up events and stored procedures so please bear with me) but one item is not clear. Would you be so kind as to give me the syntax I would use in the handler to update the row to clear the connection ID that was initially set by the first user?

Also, I find the MESSAGE TO CLIENT to be exciting because I never thought there would be a capability available to notify other users that the lock has been removed. However, I don't understand how the client code actually receives the message (I am using embedded SQL) so could you clarify that please?

Finally, your suggestion of using a mutex is interesting so I went to learn about that and I wonder if you would recommend that over defining the event? Do you see any significant advantages or disadvantages for one approach or the other?

Again, thank you very much for your help.

Re: Need help designing a locking strategy

Former Member — Tue, 08 Aug 2017 16:51:34 GMT

Hi Volker,

The design I'm working towards allows the first user to access the road to have Write permissions. Subsequent users may still open the row and view the data but they are notified that they will not be able to make any changes and save back to the database.

Re: Need help designing a locking strategy

Former Member — Tue, 08 Aug 2017 16:58:48 GMT

Hi Breck,

Thank you for your extensive reply with the tests showing impact on the log. However, I'm confused about a couple of points. It seems you are suggesting that I use the default server locking when adding to the row the "signal to update intent" count without committing, but that was my original problem in that it would leave the transaction open for long period of time.It's obvious to me that there is something so that I do not understand about your suggestion. Also, I don't understand what I would do with this count showing the number of clients that are interested in being able to update the row. If you could elaborate a bit further I would appreciate that very much.

Thanks.

Re: Need help designing a locking strategy

VolkerBarth — Wed, 09 Aug 2017 03:56:22 GMT

Well, I still do not understand how your users are informed about a writing conflict: Are they just informed that the desired row is currently "under construction" and therefore they cannot themselves modify it and then

they are told to "try later" (leaving it to them how they find out about when the row is available for modification) or
they will get a notification as soon as the row is available again (if so, when several users wait for a row, who is the next who can lock the row?)?

The default locking mechanism Breck has shown offers both ways:

When the "blocking_timeout" is set to a small timespan (e.g. some seconds) and the row is still blocked, the request will return the default blocking timeout error, so you know your modification is currently not allowed, and you should try later. Your connection is not blocked, so you can do other work.
When the "blocking_timeout" is set to 0, your request will wait until the current writer has committed his change, so your work (at least the current transaction) is suspended. (Of course you could use another connection to do separate work.)

In my understanding, in case the user should be informed automatically when the row is available again, that means any kind of DB-internal wait mechanism must be established (a blocked connection as with option 2, a WAITFOR MESSAGE, a wait for a mutex), and all those will require a worker to do the wait. There's no free lunch here. And therefore it seems that the default locking mechanism might be much easier to use than the other alternatives.

Re: Need help designing a locking strategy

VolkerBarth — Wed, 09 Aug 2017 04:11:13 GMT

Would you be so kind as to give me the syntax I would use in the handler to update the row to clear the connection ID that was initially set by the first user?

Well, it would be the opposite of the UPDATE the user does to mark the row as "blocked". Enhancing Breck's sample wirh a "locked_by_userid" column, say the user would do the following:

update t
   set locked_by_userid = connection_property('Number')
   where pkey = 1 and locked_by_userid is null;
commit;
-- Now the row is marked as locked for other users, but does not use a DB lock to do so, so the application need to behave accordingly...
... 
-- loooooong work...
-- finally mark row as available
update t
   set locked_by_userid = null
   where pkey = 1 AND locked_by_userid = connection_property('Number');
commit;

If the connection would die between marking the row as locked and as free, a disconnect event could handle this such as:

create event EV_Disconnect
type "Disconnect"
handler
begin
   -- NOTE: Ommitting all reasonable debug messages and error handling here!
   if event_parameter('DisconnectReason') in ('abnormal', 'inactive') then
      update t
         set locked_by_userid = null
         where locked_by_userid = event_parameter('ConnectionID');
   end if;
end;

Re: Need help designing a locking strategy

VolkerBarth — Wed, 09 Aug 2017 04:33:29 GMT

Also, I find the MESSAGE TO CLIENT to be exciting because I never thought there would be a capability available to notify other users that the lock has been removed. However, I don't understand how the client code actually receives the message (I am using embedded SQL) so could you clarify that please?

This is just a basic "inter-connection-communication" facility: One connection must use a WAITFOR ... AFTER MESSAGE BREAK statement and will wait until a message is received (or the maximum wait time has been reached), and the other connection must use a "MESSAGE TO CLIENT FOR ..." statement to notify the other(s) (and must usually know the other connection id beforehand). The exchanged information is just the message text, so it is not at all related to a particular database object/row/whatever, you need to use the message text to specify what is meant.

For a basic understanding, see the last sample in the v17 docs.

With v17, in case you have to use somekind of IPC between two connections, I would highly recommend the use of mutexes/semaphores over the MESSAGE TO CLIENT facility as they are easier to handle. (FWIW: While "mutexes" and "events" are both terms of OS level IPC facilities, an event in SQL Anywhere is something totally different, so a SQL Anywhere "mutex" is not meant to replace the event I have spoken about here!...)

However, as stated in a new comment under Breck's answer, I still think its easiest to use the default blocking mechanism.

Re: Need help designing a locking strategy

Breck_Carter — Wed, 09 Aug 2017 13:05:39 GMT

For the record, the update_count column is just a column that can be updated in order to obtain a write lock on the row. It has no other purpose.

Yes, the technique presented does start a long-running transaction, that is the point: use the standard locking mechanism to prevent other people from starting a update dialog if one is already in progress.

A long-running transaction isn't necessarily evil, only if it has undesirable (and often unexpected) effects on other connections which is not the case here.

Nothing prevents other users from selecting and displaying the row if the default isolation level 0 (dirty reads) is in effect.

The technique can be enhanced to warn other users the row is being updated, instead of silently blocking them, as follows (using ISQL as the "application"):

BEGIN -- simulated application code block

DECLARE @sqlcode   INTEGER;
DECLARE @errormsg  VARCHAR ( 32767 );

SET TEMPORARY OPTION BLOCKING = 'OFF';

BEGIN 
   UPDATE t
      SET update_count = update_count + 1
    WHERE pkey = 1;
EXCEPTION
   WHEN OTHERS THEN
      SELECT SQLCODE, ERRORMSG() 
        INTO @sqlcode, @errormsg;
      SET TEMPORARY OPTION BLOCKING = 'ON';
      IF @sqlcode = -210 THEN
         MESSAGE STRING ( 'Connection ', @@SPID, ' - Try again later: ', @errormsg ) TO CONSOLE;
         RETURN; -- return to caller
      ELSE
         RESIGNAL; -- return to caller with error
      END IF;
END;

-- Carry on...

SET TEMPORARY OPTION BLOCKING = 'ON';
MESSAGE STRING ( 'Connection ', @@SPID, ' - OK to continue with update' ) TO CONSOLE; 

END; -- simulated application code block

-- MESSAGE output written to dbsrv17.exe -o file:

Connection 1 - OK to continue with update
Connection 2 - Try again later: User 'dba' has the row in 't' locked