conn sys/foobar as sysdba

grant execute on dbms_lock to system;

conn system/foobar

create table sticks (s_id number,owner number);
insert into sticks select rownum-1,-1 from dual connect by level <= 5;
create table philosophers (p_id number, status varchar(32), last_update timestamp,last_eat timestamp);
insert into philosophers select rownum-1,'START',systimestamp,null from dual connect by level <=5;
commit;

create or replace procedure set_status(in_p_id number,in_status varchar2) as
pragma autonomous_transaction;
begin
update philosophers set status=in_status,last_update=systimestamp,last_eat=(case when in_status='EAT' then systimestamp else last_eat end) where p_id=in_p_id;
commit;
end;


/* deadlocking */
create or replace procedure eat (p_id number,eat_time number) as
n number;
l_s number;
r_s number;
begin
select count(*) into n from sticks ;
update sticks set owner=p_id where s_id=p_id;
update sticks set owner=p_id where s_id=mod(p_id+1,n);
set_status(p_id,'EAT');
commit;
end;

/* starvation */
create or replace procedure eat (in_p_id number,eat_time number) as
n number;
l_s number;
r_s number;
lag_time INTERVAL DAY(3) TO SECOND(3);
resource_busy exception;
pragma exception_init (resource_busy,-54);
begin
select systimestamp-last_eat into lag_time from philosophers where p_id=in_p_id;
if (lag_time > interval '1' second) then
raise_application_error(-20101, 'Philosopher ' || in_p_id || ' starved to death!');
end if;
select count(*) into n from sticks ;
update sticks set owner=in_p_id where s_id=in_p_id;
select s_id into r_s from sticks where s_id=mod(in_p_id+1,n) for update nowait;
update sticks set owner=in_p_id where s_id=mod(in_p_id+1,n);
set_status(in_p_id,'EAT');
dbms_lock.sleep(eat_time);
commit;
exception
when resource_busy then
rollback;
end;


/* partially ordered hierarchy */
create or replace procedure eat (in_p_id number,eat_time number) as
n number;
lag_time INTERVAL DAY(3) TO SECOND(3);
begin
select systimestamp-last_eat into lag_time from philosophers where p_id=in_p_id;
if (lag_time > interval '1' second) then
raise_application_error(-20101, 'Philosopher ' || in_p_id || ' starved to death!');
end if;
select count(*) into n from sticks ;
update sticks set owner=in_p_id where s_id=least(in_p_id,mod(in_p_id+1,n));
update sticks set owner=in_p_id where s_id=greatest(in_p_id,mod(in_p_id+1,n));
set_status(in_p_id,'EAT');
dbms_lock.sleep(eat_time);
commit;
end;

create or replace procedure add_philosopher(in_p_id number) as
max_think_time number := 0.1;
max_eat_time number := 0.1;
duration number := 60;
start_time date := sysdate;
n1 number;
m_handle varchar2(60);
begin
set_status(in_p_id,'THINK');
update philosophers set last_eat=null where p_id=in_p_id;
commit;
dbms_lock.allocate_unique('Synchronize',m_handle);
n1 := dbms_lock.request(m_handle,dbms_lock.s_mode,dbms_lock.maxwait,true);
loop
set_status(in_p_id,'THINK');
exit when sysdate > start_time + (duration / (24*60*60));
--dbms_lock.sleep(round(dbms_random.value*max_think_time,2));
set_status(in_p_id,'HUNGRY');
eat(in_p_id,round(dbms_random.value*max_eat_time,2));
end loop;
end;

create or replace procedure add_thinking_philosopher(p_id number) as
max_think_time number := 0.1;
max_eat_time number := 0.1;
duration number := 60;
start_time date := sysdate;
n1 number;
m_handle varchar2(60);
begin
set_status(p_id,'THINK');

dbms_lock.allocate_unique('Synchronize',m_handle);
n1 := dbms_lock.request(m_handle,dbms_lock.s_mode,dbms_lock.maxwait,true);
loop

set_status(p_id,'THINK');
exit when sysdate > start_time + (duration / (24*60*60));
dbms_lock.sleep(round(dbms_random.value*max_think_time,2));
set_status(p_id,'HUNGRY');
eat(p_id,round(dbms_random.value*max_eat_time,2));
end loop;
end;

create or replace procedure sync as
n1 number(38);
m_handle varchar2(60);
begin

dbms_lock.allocate_unique(
lockname => 'Synchronize',
lockhandle => m_handle
);

n1 := dbms_lock.request(
lockhandle => m_handle,
lockmode => dbms_lock.x_mode,
timeout => dbms_lock.maxwait,
release_on_commit => true -- the default is false !!
);

dbms_output.put_line(n1);

end;