Can I block lines in the cursor if the cursor returns only one line count (*)?

Question

I would like to prevent users from inserting more than 3 entries with color = 'Red' into my FOO table. My intentions are to: A) get the current counter so that I can determine if another record is allowed, and B) prevent any other processes from inserting any red records while this process is in progress, therefore for update of .

I would 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 of my requirements or will it not only block lines in count (*) that have foo.color = 'Red' ?

Answer 1

This will prevent users from updating selected rows, rather than adding new ones. The only way to reliably apply such a rule is by combining a check constraint (in the "main" table) and a trigger in the "foo" table, which updates the main table. Something like this (using EMP and DEPT for dating):

 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 provides the desired locking by preventing multiple users from adding, updating or deleting multiple MANAGER employees at the same time.

Answer 2

Locking existing rows cannot prevent other sessions from inserting new rows.

One possible approach is to have COLORS tables that list possible colors. (After that, FOO.COLOR may have a link to a foreign key for COLORS.COLOR.) Then lock the corresponding line in Colors before doing your inserts and updates. This will result in serialization of all hits having the same color.

Answer 3

What database are you working in? In DB2, for example, you can control the locking behavior a bit by adding "WITH [locking level]", while the locking level is one of four predefined locking levels. In general, I would not, however, assume that the database would lock things exactly the way you indent it — there are also things like escalating locks. If you want to prevent any data from being inserted into the table, again in DB2 you can make the LOCK TABLE table in EXCLUSIVE MODE.

Answer 4

You can use Oracle CONTEXT to store the NAME of the Oracle user who is trying to insert / update / delete in the FOO table. You delete (manually) Oracle CONTEXT when an Oracle user commits or rolls back using my USER_LOCK and USER_UNLOCK stored procedures. This way you can avoid attachments / updates and deletions at the same time with multiple Oracle users, as you provide access to one Oracle user at a time.

Using the USER_LOCK procedure (name_of_the_user), you can add the Oracle username in the context of Oracle. Using the USER_UNLOCK procedure (name_of_the_user), you can remove the Oracle username in the context of Oracle. Using view locked_users, you can determine if the Oracle user is locked or not, because if he is locked, his name appears in the view. The following code creates all Oracle structures to achieve all of 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; /