Serializing access to a table row in HANA

Question

Whereas I am not allowed to use either identity columns or HANA sequences, I am forced manually to generate unique autoincrementing keys for tables. Here is my unsafe and naive key generation procedure, which stores unique counters in table TABLEKEYS and increments them at every execution:

CREATE PROCEDURE NewKey
(   IN  SeqName   NVARCHAR( 32),
    OUT NewKey    BIGINT
)
AS  rec_exists  INT;
    row_num     INT;
BEGIN
    SELECT SUM(1) INTO rec_exists
    FROM ( SELECT TOP 1 1 FROM TABLEKEYS WHERE "Name" = :SeqName ) T;
    IF :rec_exists IS NULL THEN         
        SELECT COALESCE(SUM(1),0) INTO row_num FROM TABLEKEYS;

        INSERT INTO TABLEKEYS("Code",  "Name",    "U_CurrentKey")
        VALUES               (row_num, :SeqName,  -1            );
    END IF;

    UPDATE TABLEKEYS SET "U_CurrentKey" = "U_CurrentKey" + 1
    WHERE "Name" = :SeqName;

    SELECT "CurrentKey" INTO NewKey FROM TABLEKEYS
    WHERE "Name" = :SeqName;
END;

How to make it reliable, so that it shall not return two identical keys under any circumstances, even when it is being called intensively from an hundred simultaneous connections? In MSSQL Server I should wrap its body in a transaction and apply locking hints to the table in the initial query, but I am not aware of their analogs in HANA. Is there a way in HANA to ensure that a table row is accessed strictly sequencially?

My procedure with corrections suggested by Lars and adapted for Business One user-defined tables:

CREATE PROCEDURE GTGetNewKeyInt
(   IN  TableName NVARCHAR( 32),
    OUT NewKey    BIGINT
)
AS  cur_key     INT;
    row_num     INT;
    row_num_txt VARCHAR(8);
BEGIN
    BEGIN
        DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
        END;
        SELECT "U_CurrentKey" INTO cur_key FROM "@GTTABLEKEYS"
        WHERE "Name" = :TableName
        FOR UPDATE;
    END;

    IF :cur_key IS NULL THEN
        LOCK TABLE "@GTTABLEKEYS" IN EXCLUSIVE MODE;
        SELECT COALESCE(SUM(1),0) INTO row_num FROM "@GTTABLEKEYS";
        row_num_txt = LPAD( CAST( row_num AS varchar ), 8, '0' );
        NewKey = 0;
        INSERT INTO "@GTTABLEKEYS"("Code",      "Name",      "U_CurrentKey")
        VALUES                    (row_num_txt, :TableName,  :NewKey       );
    ELSE
        NewKey = :cur_key + 1;
        UPDATE "@GTTABLEKEYS" SET "U_CurrentKey" = :NewKey
        WHERE "Name" = :TableName;
    END IF;
END;

score 1 · Accepted Answer · answered Nov 23 '16 at 16:59

1

First off: not using the built-in features like sequences or the IDENTITY column seems rather not like a great idea.

Anything you build yourself here, will be inferior in one or the other regard. But, hey, it's your code after all.

So, for selecting with locking, there is the standard SQL command

SELECT ... FOR UPDATE FROM...

(also see the documentation here)

Your program logic will be to

SELECT ... FOR UPDATE
do whatever you have to do
Update the sequence table
COMMIT or ROLLBACK

Your record will be locked as of step 1. In order to make the whole process more efficient and to decouple the performance for managing the sequence from the amount of data in the actual data table, you may want to keep the sequence in its own table (row store might be a good idea for this one, as you deal with a single record and lots of updates). That's rather close to how sequences work as well.

answered Nov 23 '16 at 16:59

Lars Br.

9,949
2
15
29

Thank you, Lars. My purpose is to develop a general utility class for Busienss One that shall work with both MSSQL and HANA versions, including B1 versions older than 9.2, which do not support user-defined tables with autoincrementing keys. It is important not to introduce any manual modifications to the company database but use only what B1 provides. I am not even sure whether I have any control on the table type (row-based or column-based), but I will check that. – Anton Shepelev Nov 23 '16 at 20:08
`SELECT ... FOR UPDATE` helps for updating a specific row, while the case when a new row is added may still go wrong. How can one fix that, i.e. block insertion until the procedure terminates if the "sequence" requested is not already present in the table? – Anton Shepelev Nov 23 '16 at 20:16
You can always lock the table (LOCK TABLE command...). But generally, you like to avoid serialisation and blocking in order to allow for better throughput. Sounds like your DB-agnostic approach is not doing you any favours here. As the specifics of locking and concurrency behavior is rather platform specific, I'd rather build that functionality specific to the platform. – Lars Br. Nov 24 '16 at 07:45
Lars, I have separate sets of analogous procedures for MSSQL and HANA, so they need not be RDBMS-agnostic, but they must have compatible interfaces and be B1-version-agnostic. There are trade-offs to this approach, but also a huge advantage—that we do not have to maintain parallel versions of our products. – Anton Shepelev Nov 24 '16 at 11:26

Serializing access to a table row in HANA

My procedure with corrections suggested by Lars and adapted for Business One user-defined tables:

1 Answers1