Can I lock rows in a cursor if the cursor only returns a single count(*) row?

Question

I would like to restrict users from inserting more than 3 records with color = 'Red' in my FOO table. My intentions are to A) retrieve the current count so that I can determine whether another record is allowed and B) prevent any other processes from inserting any Red records while this one is in process, hence the for update of.

I'd like to do something like:

cursor cur_cnt is
select count(*) cnt from foo
where foo.color = 'Red'
for update of foo.id;

Will this satisfy both my requirements or will it not lock only the rows in the count(*) who had foo.color = 'Red'?

Answer 1

What database are you running? In DB2 for instance you can control the locking behaviour somewhat by appending 'WITH [locking-level]', whereas locking-level is one of the 4 predefined locking levels. In general I would however not assume that the database will lock things exactly the way you indent it too - there are also such things as lock escalation. If you want to prevent any data to be inserted into the table, again in DB2, you can do 'LOCK TABLE table IN EXCLUSIVE MODE'.

Answer 2

You can use an Oracle CONTEXT in order to store the NAME of the Oracle user which is attempting to insert/update/delete in the FOO table. You empty (manually) the Oracle CONTEXT when the Oracle user commits or rollbacks, by using my stored procedures USER_LOCK and USER_UNLOCK. In this way, you are able to avoid inserptions / updates and deletions in the same time from more than one Oracle user, because you grant access to one Oracle user at a time.

With the procedure USER_LOCK (name_of_the_user) you can add the name of the Oracle user in the Oracle context. With the procedure USER_UNLOCK (name_of_the_user) you can remove the name of the Oracle user in the Oracle context. Using the view locked_users, you can determine if an Oracle user is locked or not, because if it is locked, its name appears in the view. The following code creates all the Oracle structures in order to achieve all this:

CREATE OR REPLACE PACKAGE my_pkg
IS
   PROCEDURE set_session_id (p_session_id NUMBER);

   PROCEDURE set_ctx (p_name VARCHAR2, p_value VARCHAR2);

   PROCEDURE close_session (p_session_id NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY my_pkg
IS
   g_session_id   NUMBER;

   PROCEDURE set_session_id (p_session_id NUMBER)
   IS
   BEGIN
      g_session_id := p_session_id;
      DBMS_SESSION.set_identifier (p_session_id);
   END set_session_id;

   --===============================================
   PROCEDURE set_ctx (p_name VARCHAR2, p_value VARCHAR2)
   IS
   BEGIN
      DBMS_SESSION.set_context ('App_Ctx',
                                p_name,
                                p_value,
                                USER,
                                g_session_id);
   END set_ctx;

   --===============================================
   PROCEDURE close_session (p_session_id NUMBER)
   IS
   BEGIN
      DBMS_SESSION.set_identifier (p_session_id);
      DBMS_SESSION.clear_identifier;
   END close_session;
--===============================================
END;
/

    CREATE OR REPLACE CONTEXT APP_CTX
     USING MY_PKG
     ACCESSED GLOBALLY
/


CREATE OR REPLACE TYPE test_type AS TABLE OF VARCHAR2 (30);
/



CREATE OR REPLACE FUNCTION f_convert2 (p_list IN VARCHAR2)
   RETURN test_type
   PIPELINED
AS
   --l_string        LONG        := p_list || ',';
   l_string        VARCHAR2 (4000) := p_list || ',';
   l_comma_index   PLS_INTEGER;
   l_index         PLS_INTEGER := 1;
BEGIN
   LOOP
      l_comma_index := INSTR (l_string, ',', l_index);
      EXIT WHEN l_comma_index = 0;
      PIPE ROW (SUBSTR (l_string, l_index, l_comma_index - l_index));
      l_index := l_comma_index + 1;
   END LOOP;

   RETURN;
END f_convert2;
/



CREATE OR REPLACE FORCE VIEW locked_users (utente)
AS
     SELECT COLUMN_VALUE utente
       FROM TABLE (
               f_convert2 (
                  REPLACE (
                     LTRIM (RTRIM (SYS_CONTEXT ('app_ctx', 'Var1', 4000), '*'),
                            '*'),
                     '**',
                     ',')))
   ORDER BY 1 ASC
/



CREATE OR REPLACE PROCEDURE user_lock (ne_user IN VARCHAR2)
IS
BEGIN
   DECLARE
      indice                NUMBER;
      appoggio_variabile1   VARCHAR2 (250);
   BEGIN
      -- my_pkg.close_session(1234);
      my_pkg.set_session_id (1234);
      appoggio_variabile1 := SYS_CONTEXT ('app_ctx', 'var1');
      DBMS_OUTPUT.put_line (appoggio_variabile1);

      IF INSTR (appoggio_variabile1, ne_user) >= 1
      THEN
         BEGIN
            DBMS_OUTPUT.
             put_line ('The user ' || ne_user || ' is already locked!');
         END;
      ELSE
         BEGIN
            my_pkg.
             set_ctx ('Var1', appoggio_variabile1 || '*' || ne_user || '*');
            DBMS_OUTPUT.
             put_line ('The user ' || ne_user || ' is now locked.');
         END;
      END IF;
   END;
END user_lock;
/



CREATE OR REPLACE PROCEDURE user_unlock (ne_user IN VARCHAR2)
IS
BEGIN
   DECLARE
      indice                NUMBER;
      appoggio_variabile1   VARCHAR2 (250);
   BEGIN
      -- my_pkg.close_session(1234);
      my_pkg.set_session_id (1234);
      appoggio_variabile1 := SYS_CONTEXT ('app_ctx', 'var1');
      DBMS_OUTPUT.put_line (appoggio_variabile1);

      IF INSTR (appoggio_variabile1, ne_user) = 0
         OR appoggio_variabile1 IS NULL
      THEN
         BEGIN
            DBMS_OUTPUT.
             put_line ('The user ' || ne_user || ' is already unlocked!');
         END;
      ELSE
         BEGIN
            my_pkg.
             set_ctx ('Var1',
                      REPLACE (appoggio_variabile1, '*' || ne_user || '*'));
            DBMS_OUTPUT.
             put_line ('The user ' || ne_user || ' is now unlocked.');
         END;
      END IF;
   END;
END user_unlock;
/

Answer 3

Locking existing rows can't prevent other sessions from inserting new rows.

One possible approach is to have a COLORS tables that lists the possible colors. (Your FOO.COLOR could then have a foreign key reference to COLORS.COLOR.) Then lock the appropriate row in COLORS before doing your inserts and updates. This will serialize all accesses that deal with the same color.

Answer 4

This will only prevent users from updating the selected rows, not from adding new ones. The only way to reliably enforce such a rule is by a combination of a check constraint (on a "master" table) and a trigger on the "foo" table that updates the master table. Something like this (using EMP and DEPT for familiarity):

alter table dept add (manager_count integer default 0 not null,
   constraint manager_count_chk check (manager_count <= 3));

create trigger emp_trg
before insert or update or delete on emp
for each row
begin
    if inserting or updating then
        if :new.job = 'MANAGER' then
            update dept
            set    manager_count = manager_count+1
            where  deptno = :new.deptno;
        end if;
    end if;
    if updating or deleting then
        if :old.job = 'MANAGER' then
            update dept
            set    manager_count = manager_count-1
            where  deptno = :new.deptno;
        end if;
    end if;
end;

This achieves the desired locking by preventing more than one user from inserting, updating or deleting 'MANAGER' employees at a time.