Need help designing a locking strategy

former_SQLA_member1694877 · ‎2017 Aug 06

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.

Breck_Carter · ‎2017 Aug 07

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