Column Level Locking

Column Level Locking in INSERT commands.

Execture an insert into table command using the following table and user functions:

CREATE TABLE TBL (
  _n INTEGER PK,
  -- columns hosted on server cluster nodes
  _a INTEGER source="1.0.0.1:db=db;user=user",                   
  _b INTEGER source="1.0.0.2:db=db;user=user",
  _txt TEXT,
);
-- array of initialization keys for first row
DECLARE KEYS['_a','_b']=[0,1];
/* 
  list of used function parameters
   N:   integer, value in column TBL._n
   COL: text, column name
   X:   integer, input value of an expression
*/
-- function that returns value in column _n
-- - in row being inserted
PKID()=$_$ 
  RETURN                                                         
   'SELECT N FROM TBL WITH ROWID AS R 
     WHERE R='||TRANSACTION_ROWID();
$_$; 
-- function Stamp
-- - returns key when N=1 or value in 
-- - row _n=N-1 for column COL
STM(COL TEXT)=$_$                                                
  N=PKID();       
  RETURN
    N-1==0?                                                      
      KEY(COL):
      EXE('SELECT '||COL||'
             FROM TBL WHERE _n='||N-1);
$_$;
-- function Positive or Negative
-- - returns -1 for even values in
-- - column _n and 1 for odd
-- function Calculation
-- - returns COL*X*PON()+_a+_b+_c+_d in row N
PON() = $_$                   
  RETURN PKID()%2==0?-1:1;                                       
$_$;                              
CLC(COL TEXT,X INT) = $_$                                  
  N=PKID();                   
  RETURN 'SELECT '||COL*X*PON(N)+_a+_b||'                        
            FROM TBL WHERE _n='||N;
$_$;

Perform an insert into TBL command where values in columns _a and _b are derived from calculations of user-defined functions STM() and CALC(). Value inserted into column _txt is a concatenated string of columns _a and _b. Incremental value from series 1..2 is inserted into primary key column _n.

-- transaction start 
INSERT INTO TBL
  (
   _n,
   _a,
   _b,
   _txt
  )
  SELECT
    (
     N._N,
     CALC('_a',STM('_a'),
     CALC('_b',STM('_b'),
     (SELECT _a||_b FROM TBL WHERE _n = PKID())
    )
    FROM SERIES(1..2) AS N(_N)
);
SELECT * FROM TBL;

_n | _a | _b | _txt
===|====|====|=====
 1 |  0 |  2 | '02'
 2 |  2 | -1 | '2-1'
-- transaction end

Sprout system code:

-- class containing system functionality
TRANSACTION_ROWID() => SYS.ASYNC.INIT.row();
CLASS SYS()
$_$                                                        
...
  -- subclass containing functionality for execution
  -- - of asynchronous transactions
  -- - method to instantiate a request object from SQL string
  -- note: synchronous transaction object contains similar methods
  CLASS ASYNC()
  $_$
    ...                                                  
    METHOD INIT(SQL TEXT)
    $_$                 
      ...
      /* 
        method instantiates an object with the following
          .row()             - unique identifier of a new table row
          .levels[]          - array of transaction stack
                             - following is per each level in stack:
            .scalars[]       - array of scalar values  
            .functions[]     - array of function calls
            .statements[]    - array of sub-select statements 
        scalars[], functions[] and statements[] indices
        are unique identifiers of table columns 
      */
      ...
    $_$;
    -- functionality of asynchronous transaction execution
    -- - employs PGQ http://wiki.postgresql.org/wiki/PGQ_Tutorial
    METHOD THREAD(ARR ARRAY[])
    $_$
      ...
      /*                                                          
        accepts array of scalars, SQL functions or sub select
        statements, launches threads of asynchronous
        subtransactions via queue, monitors thread execution in
        delayed loop, and runs data definition statements on
        columns supplied in input array indices with results of
        thread executions
      */
      ...
    $_$;
  $_$;
  -- execute a SQL command
  -- - this method is called from SQL client
  -- - supplied with "insert into" command
  METHOD SQL(SQL TEXT)
  $_$                                            
    ...
    -- instantiate object request
    request = NEW SYS.ASYNC.INIT(SQL);
    -- iterate over levels of transaction stack
    LOOP i OVER 1..request.levels.length
      -- insert scalars
      SYS.ASYNC.THREAD(request.levels[i].scalars);
      -- insert functions
      SYS.ASYNC.THREAD(request.levels[i].functions);
      -- insert statements
      SYS.ASYNC.THREAD(request.levels[i].statements);
  $_$;      
...
$_$;

Advertisements