Skip to main content

Lightweight object locking

I

I'm using Git and the APEX Deployment Tool, so I very rarely lose code because I effortlessly export any changes and commit them right away. Mostly, I'm on the other side, breaking the code of others, those who still live in the stone age and keep everything only in the database.

A friend of mine, Rafal, wrote about a great tool called Loki based on APEX. It might be a bit heavy, and I also have some customers who really don't want an APEX app to manage object locking.


So here is mine lightweight implementation. All you need is:


Simple process

  • you want to edit package, so you open it and compile it (right away, not after you work on it for half day)
  • this will trigger the DDL event which will be catched by our AFTER DDL schema level trigger
  • in this trigger we check the lock table, if the object is not locked by another developer
  • if not, the lock is yours and nobody else can compile this package for next 20 minutes
  • if the object is locked by someone else, you will get an error with developer name and lock_id, at this point you can talk to the other developer or you can take over his lock

So if you keep working on your package and compile it every 20 minutes, the object will remain just yours. If you need to lock it for a full day or even a week, you can. If you need to unlock someone else's object, you can do that too. And it's not just about locking packages, it also works for tables, views, materialized views, triggers, and you can customize it further. When you query the CORE_LOCK table, you get a history or overview of the changes.

If needed, you can also store the object itself and use it for backups and auditing. This is not implemented yet, but it is hinted at in the trigger.


You can also do some explicit lock/unlock operations, but generally you don't need them:

BEGIN
    -- example of the explicit lock
    core_lock.create_lock (
        in_object_owner     => USER,
        in_object_type      => 'PACKAGE BODY',
        in_object_name      => 'PACKAGE_NAME',
        in_locked_by        => NULL,
        in_expire_at        => NULL
    );

    -- extend specific lock
    core_lock.extend_lock (
        in_lock_id          => 0,
        in_time             => 20/1440
    );
    core_lock.extend_lock (
        in_lock_id          => 0,
        in_date             => SYSDATE + 20/1440
    );

    -- unlock pending lock
    core_lock.unlock (
        in_lock_id          => 0
    );
END;
/


Let's check parts of the source code, starting with the table:

CREATE TABLE IF NOT EXISTS core_locks (
    lock_id             NUMBER                GENERATED BY DEFAULT AS IDENTITY START WITH 1000 NOT NULL,
    object_owner        VARCHAR2(32)          NOT NULL,
    object_type         VARCHAR2(32)          NOT NULL,
    object_name         VARCHAR2(128)         NOT NULL,
    locked_by           VARCHAR2(128)         NOT NULL,
    locked_at           DATE                  NOT NULL,
    expire_at           DATE,
    counter             NUMBER(4,0),
    --
    CONSTRAINT core_locks_pk
        PRIMARY KEY (lock_id)
);


Schema level trigger:

CREATE OR REPLACE TRIGGER core_locksmith
AFTER DDL ON SCHEMA
DECLARE
    rec             core_locks%ROWTYPE;
BEGIN
    -- get username, but we dont want generic users
    rec.locked_by := core_lock.get_user();

    -- log the event in the audit log, here, we it is fine to log just in the generic log
    core.log_start (
        'event',            ORA_SYSEVENT,
        'object_owner',     ORA_DICT_OBJ_OWNER,
        'object_type',      ORA_DICT_OBJ_TYPE,
        'object_name',      ORA_DICT_OBJ_NAME,
        'user',             rec.locked_by,
        'user_host',        SYS_CONTEXT('USERENV', 'HOST'),
        'user_ip',          SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
        'user_lang',        REGEXP_REPLACE(SYS_CONTEXT('USERENV', 'LANGUAGE'), '^([^\.]+)', 1, 1, NULL, 1),
        'user_zone',        SESSIONTIMEZONE
    );

    -- evaluate only specific events and specific object types
    IF ORA_SYSEVENT IN ('CREATE', 'ALTER', 'DROP')
        AND ORA_DICT_OBJ_TYPE IN (
            'TABLE', 'VIEW', 'MATERIALIZED VIEW',
            'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TRIGGER'
        )
    THEN
        core_lock.create_lock (
            in_object_owner     => ORA_DICT_OBJ_OWNER,
            in_object_type      => ORA_DICT_OBJ_TYPE,
            in_object_name      => ORA_DICT_OBJ_NAME,
            in_locked_by        => rec.locked_by,
            in_expire_at        => NULL
        );
    END IF;
    --
EXCEPTION
WHEN core.app_exception THEN
    RAISE;
WHEN OTHERS THEN
    core.raise_error();
END;
/


Procedure handling the locks:

    PROCEDURE create_lock (
        in_object_owner     core_locks.object_owner%TYPE,
        in_object_type      core_locks.object_type%TYPE,
        in_object_name      core_locks.object_name%TYPE,
        in_locked_by        core_locks.locked_by%TYPE       := NULL,
        in_expire_at        core_locks.expire_at%TYPE       := NULL
    )
    AS
        rec                 core_locks%ROWTYPE;
    BEGIN
        -- check if we have a valid user
        rec.locked_by := COALESCE(in_locked_by, get_user());
        IF rec.locked_by IS NULL THEN
            core.raise_error('USER_ERROR: USE PROXY_USER OR CLIENT_ID', in_rollback => FALSE);
        END IF;

        -- check recent log for current object
        FOR c IN (
            SELECT
                t.lock_id,
                t.locked_by,
                t.expire_at
            FROM core_locks t
            WHERE t.object_owner    = in_object_owner
                AND t.object_type   = in_object_type
                AND t.object_name   = in_object_name
            ORDER BY
                t.lock_id DESC
            FETCH FIRST 1 ROWS ONLY
        ) LOOP
            IF c.locked_by = rec.locked_by THEN
                -- same user, so update last record with new expire day
                rec.lock_id := c.lock_id;
                --
            ELSIF c.expire_at >= SYSDATE THEN
                -- for different user we need to check the expire date first
                core.raise_error('LOCK_ERROR: OBJECT LOCKED BY "' || c.locked_by || '" [' || c.lock_id || ']', in_rollback => FALSE);
            END IF;
        END LOOP;
        --
        IF rec.lock_id IS NOT NULL THEN
            core_lock.extend_lock (
                in_lock_id => rec.lock_id
            );
        ELSE
            INSERT INTO core_locks (
                object_owner,
                object_type,
                object_name,
                locked_by,
                locked_at,
                counter,
                expire_at
            )
            VALUES (
                in_object_owner,
                in_object_type,
                in_object_name,
                rec.locked_by,
                SYSDATE,
                1,
                NVL(in_expire_at, SYSDATE + g_lock_length)
            );
        END IF;
        --
    EXCEPTION
    WHEN core.app_exception THEN
        RAISE;
    WHEN OTHERS THEN
        core.raise_error(in_rollback => FALSE);
    END;


Shared schemas

Still reading? Cool, since most of us work on shared schemas (where multiple developers share the same password), we can't rely on the user name. If you're lucky enough to have a proxy user, or if you're using SQL Workshop for whatever reason, you can start using this without any issues. Otherwise, you'll have to set the CLIENT_IDENTIFIER to distinguish yourself from the others.

BEGIN
    DBMS_SESSION.SET_IDENTIFIER('USERNAME');
END;
/


This solution is for a friendly environment where you really want just to prevent the lost code. If you want something more robust for auditing, you can create the lock table as IMMUTABLE and you can create the trigger on DB level targeting just your schemas. Otherwise table can be rigged, trigger can be dropped or disabled, package can be modified, username could be changed...


Tags:

Comments

Post a Comment