Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Igor.

Asked: November 29, 2018 - 10:29 pm UTC

Last updated: December 12, 2018 - 3:30 am UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

I would like to implement a constraint for the following table:
CREATE TABLE GLOBAL_LOCKS 
( 
  RESOURCE_NAME VARCHAR2(50) NOT NULL, 
  IS_EXCLUSIVE CHAR(1) DEFAULT 'N' NOT NULL 
)


as follows:
1. RESOURCE_NAME must be unique if IS_EXLUSIVE = 'Y'
2. RESOURCE_NAME can be duplicate if all of them have IS_EXCLUSIVE = 'N'

--Example 1
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'Y');
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
-- should fail

--Example 2
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
-- should work

--Example 3
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'N');
INSERT INTO GLOBAL_LOCKS(RESOURCE_NAME, IS_EXCLUSIVE)
VALUES ('MY_RESOURCE', 'Y');
-- should fail



The idea is that exclusive and shared locks can be maintained. There can be only one exclusive lock for a given resource; but there can be multiple shared locks for a given resource.

This question is similar to https://asktom.oracle.com/pls/apex/asktom.search?tag=how-to-enforce-conditional-unique-on-multiple-columns


with LiveSQL Test Case:

and Connor said...

I've used an index to satisfy the single "Y" part, because that gives immediate feedback. For the Y|N combinations, then it means we need to wait until committing so we can use an mview for that

SQL> CREATE TABLE GLOBAL_LOCKS
  2  ( pk int primary key,
  3    RESOURCE_NAME VARCHAR2(50) NOT NULL,
  4    IS_EXCLUSIVE CHAR(1) DEFAULT 'N' NOT NULL
  5  );

Table created.

SQL>
SQL> create unique index GLOBAL_LOCKS_IX
  2  on GLOBAL_LOCKS ( case when IS_EXCLUSIVE = 'Y' then RESOURCE_NAME end );

Index created.

SQL>
SQL>
SQL> create materialized view log on GLOBAL_LOCKS
  2    with rowid (resource_name, IS_EXCLUSIVE, pk) including new values
  3  /

Materialized view log created.

SQL>
SQL> create materialized view GLOBAL_LOCKS_CHECKER
  2  refresh fast
  3  on commit as
  4  select
  5    RESOURCE_NAME,
  6    count(*) c,
  7    count(decode(IS_EXCLUSIVE,'N',1)) n_cnt,
  8    count(decode(IS_EXCLUSIVE,'Y',1)) y_cnt
  9  from  GLOBAL_LOCKS
 10  group by RESOURCE_NAME;

Materialized view created.

SQL>
SQL> alter table GLOBAL_LOCKS_CHECKER add constraint GLOBAL_LOCKS_CHECKER_CHK
  2   check ( y_cnt = 0 );

Table altered.

SQL>
SQL> -- should fail
SQL> delete GLOBAL_LOCKS;

0 rows deleted.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (10,'MY_RESOURCE', 'Y');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (11,'MY_RESOURCE', 'Y');
INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (11,'MY_RESOURCE', 'Y')
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.GLOBAL_LOCKS_IX) violated


SQL>
SQL> -- should fail
SQL> delete GLOBAL_LOCKS;

1 row deleted.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (10,'MY_RESOURCE', 'Y');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (11,'MY_RESOURCE', 'N');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (MCDONAC.GLOBAL_LOCKS_CHECKER_CHK) violated


SQL> select * from GLOBAL_LOCKS_CHECKER;

no rows selected

SQL>
SQL> -- should be ok
SQL> delete GLOBAL_LOCKS;

0 rows deleted.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (21,'MY_RESOURCE', 'N');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (22,'MY_RESOURCE', 'N');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from GLOBAL_LOCKS_CHECKER;

RESOURCE_NAME                                               C      N_CNT      Y_CNT
-------------------------------------------------- ---------- ---------- ----------
MY_RESOURCE                                                 2          2          0

1 row selected.

SQL>
SQL> --should fail
SQL> delete GLOBAL_LOCKS;

2 rows deleted.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (31,'MY_RESOURCE', 'N');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (32,'MY_RESOURCE', 'N');

1 row created.

SQL> INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (33,'MY_RESOURCE', 'Y');

1 row created.

SQL> commit;
commit
*
ERROR at line 1:
ORA-12008: error in materialized view or zonemap refresh path
ORA-02290: check constraint (MCDONAC.GLOBAL_LOCKS_CHECKER_CHK) violated


SQL> select * from GLOBAL_LOCKS_CHECKER;

RESOURCE_NAME                                               C      N_CNT      Y_CNT
-------------------------------------------------- ---------- ---------- ----------
MY_RESOURCE                                                 2          2          0

1 row selected.

SQL>
SQL>
SQL>


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Very helpful but one of the cases doesn't seem to work

Igor Pashchuk, December 08, 2018 - 3:33 am UTC

Hi, Connor,

Thanks a lot for this!

It worked almost perfectly except for one of the crucial cases:
DELETE FROM GLOBAL_LOCKS;
COMMIT;
INSERT INTO GLOBAL_LOCKS(pk,RESOURCE_NAME, IS_EXCLUSIVE) VALUES (10,'MY_RESOURCE', 'Y');
COMMIT;


I changed the constraint from:
alter table GLOBAL_LOCKS_CHECKER add constraint GLOBAL_LOCKS_CHECKER_CHK
  check ( y_cnt = 0 );


to:
alter table GLOBAL_LOCKS_CHECKER add constraint GLOBAL_LOCKS_CHECKER_CHK
  check ( y_cnt = 0 or (y_cnt = 1 and n_cnt = 0))

Connor McDonald
December 12, 2018 - 3:30 am UTC

Nice catch.

fragile and baroque version

Racer I., December 12, 2018 - 10:37 am UTC

Hi,
--drop table tlockholders;

--drop table tlocks;

create table tlocks (id number(10) not null, name VARCHAR2(100) not null, yn_exclusive char(1) not null);

create unique index plocks on tlocks (id);

alter table tlocks add constraint plocks PRIMARY KEY (id) using index plocks;
 
create unique index ilocks on tlocks (name);

insert into tlocks values (1, 'lock1', 'Y');

insert into tlocks values (2, 'lock2', 'N');

commit;

create or replace function isexcl(pID number) RETURN CHAR DETERMINISTIC
IS
  r CHAR(1);
BEGIN
  SELECT MAX(yn_exclusive) INTO r FROM tlocks WHERE ID = pID;
  RETURN CASE WHEN r IS NULL THEN 'N' ELSE r END;
END;
/
show errors;
/

create table tlockholders (id_locks NUMBER(10) NOT NULL, pos NUMBER(4) NOT NULL, name VARCHAR2(100) NOT NULL, ef CHAR(1) AS (SUBSTR(ISEXCL(ID_Locks), 1, 1)));

create unique index ilockholders on tlockholders (id_locks, pos);

alter table tlockholders add constraint flocks foreign key (id_locks) references tlocks(id);

alter table tlockholders add constraint clocks check (CASE WHEN ef = 'Y' AND pos = 1 THEN 'Y' WHEN ef ='N' THEN 'Y' ELSE 'N' END = 'Y');

insert into  tlockholders (id_locks, pos, name) values (1, 1, 'holder11');

--insert into  tlockholders (id_locks, pos, name) values (1, 1, 'holder12');

--insert into  tlockholders (id_locks, pos, name) values (1, 2, 'holder12');

insert into  tlockholders (id_locks, pos, name) values (2, 1, 'holder21');
 
insert into  tlockholders (id_locks, pos, name) values (2, 2, 'holder22');

--insert into  tlockholders (id_locks, pos, name) values (3, 1, 'holder31');

commit;


To make the deterministic promise somewhat true, the YN_exclusive field should be protected against updates via a trigger.

Exclusive lock holders must use pos 1. Non-exclusives must make sure to use different pos values.

regards,

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.