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;

Answer 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

1. SELECT ... FOR UPDATE
2. do whatever you have to do
3. Update the sequence table
4. 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.