SQL & PL/SQL

I'm experimenting with an ODCIIndex maintained index for implementing a Constraint on a Snowflake Schema View .. a specific type of SQL Assertion.

Design goal is to (eventually) Assert that the number of students in a class is ≤ a room's max_occupancy

Right now, I'm stuck on trying to implement a row level shared read lock on the dimension table(s) to achieve the Business Requirement.

Business Requirements

• adding/removing student to the Biology class should not interfere with another session adding/removing different students to the Biology class
  • experimenting with 23ai RESERVABLE column for count(*) N of students per class
• adding/removing students to the Biology class should not interfere with room changes of the Math class and vice versa
• adding/removing students to the Math class should prevent room changes of the Math class until locks are released via commit/rollback
• room changes of the Math Class should prevent adding/removing students to the Math class until locks are released via commit/rollback
• to be clear: "room changes" implies that the max_occupancy for 1 or more classes is being changed

Current Technical implementation

• two types of INDEXTYPE ( ODCIIndex ) are being created
  • one for Dimension tables ( Rooms, Classes )
  • one for Fact table ( Student_Classes )
• row level shared read lock is planned to be implemented using DBMS_LOCK with lockname of the form abc$<indexName>$l$$<id>
  • SX_MODE for shared read
  • X_MODE for DML

QUESTIONS

• is there a better method to achieve the Business Requirements?
• Are there any foreseeable problems with my approach?

Schema for reference:

create table Rooms (
	rooms_id int generated always as identity primary key,
	max_occupancy int check (max_occupancy >= 0) -- null implies infinite
);
create table Classes (
	class_id int generated always as identity primary key,
	room_id  int not null references rooms(room_id)
);
create table Students (
	student_id int generated always as identity primary key,
	sname      varchar2(500 char) not null
);
create table Student_Classes (
  student_id int references Students(student_id),
  class_id   int references Classes(class_id),
  primary key (student_id,class_id)
);

“Index” for (eventual) Assertion is to simulate a MV Fast Refresh On Statement for:

select c.class_id, r.room_id, r.max_occupancy, count(*) N
from Student_Classes sc
  join Classes c on c.class_id = sc.class_id
  join Rooms r on r.room_id = s.room_id
group by c.class_id, r.room_id, r.max_occupancy