set echo on
create table philosophers
( id number primary key
, status varchar(32)
, last_update timestamp
, last_eat timestamp
)
/
create table sticks
( id number primary key
, philosopher_id number references philosophers(id)
)
/
create table log
( id number
, philosopher_id number references philosophers(id)
, event varchar2(100)
, log_time timestamp
)
/
create sequence log_seq start with 1 increment by 1 cache 20
/
insert into philosophers
select rownum-1
, 'START'
, systimestamp
, null
from dual
connect by level <=5
/
insert into sticks
select rownum-1
, null from dual
connect by level <= 5
/
create package dining_philosophers
as
procedure sync
;
procedure add_philosopher
( p_philosopher_id in number
, p_thinking in boolean
, p_eat_type in number
, p_max_duration in number default 2
);
end dining_philosophers;
/
show err
create package body dining_philosophers
as
g_philosopher_id philosophers.id%type;
N number;
g_last_ate timestamp;

procedure initialisation
is
begin
select count(*) into N from sticks;
g_last_ate:=systimestamp;
end initialisation
;
procedure log (p_event in varchar2)
as
pragma autonomous_transaction;
begin
insert into log values (log_seq.nextval,g_philosopher_id,p_event,systimestamp);
commit;
end log
;
procedure grab_left_chopstick
as
begin
log('tries to pick up left chopstick.');
update sticks
set philosopher_id = g_philosopher_id
where id = mod(g_philosopher_id+1,N)
;
log('has picked up left chopstick.');
end grab_left_chopstick
;
procedure grab_right_chopstick
as
begin
log('tries to pick up right chopstick.');
update sticks
set philosopher_id = g_philosopher_id
where id = g_philosopher_id
;
log('has picked up right chopstick.');
end grab_right_chopstick
;
function left_chopstick_is_free return boolean
as
l_stick_id sticks.id%type;
e_resource_busy exception;
pragma exception_init (e_resource_busy,-54);
begin
select id
into l_stick_id
from sticks
where id = mod(g_philosopher_id+1,N)
for update nowait
;
return true;
exception
when e_resource_busy then
return false;
end left_chopstick_is_free
;
procedure set_status (p_status in varchar2)
as
begin
update philosophers
set status = p_status
, last_update = systimestamp
, last_eat = case when p_status='EAT' then systimestamp else last_eat end
where id = g_philosopher_id
;
log
( case p_status
when 'EAT' then 'eating'
when 'HUNGRY' then 'hungry'
when 'THINK' then 'thinking'
end
);
end set_status
;
procedure start_eating (p_duration in number)
as
begin
set_status('EAT');
log('last ate: ' || extract(second from (systimestamp-g_last_ate)));
dbms_lock.sleep(p_duration);
g_last_ate:=systimestamp;
end start_eating
;
procedure start_thinking (p_duration in number)
as
begin
set_status('THINK');
commit;
dbms_lock.sleep(p_duration);
end start_thinking
;
procedure start_being_hungry
as
begin
set_status('HUNGRY');
commit;
end start_being_hungry
;
function previous_eat_time return interval day to second
is
l_previous_eat_time interval day(0) to second(3);
begin
select systimestamp - last_eat
into l_previous_eat_time
from philosophers
where id = g_philosopher_id
;
return l_previous_eat_time
;
end previous_eat_time
;
procedure start_trying_to_eat_1 (p_duration in number)
-- the deadlock scenario
as
e_deadlock exception;
pragma exception_init(e_deadlock,-60);
begin
grab_right_chopstick;
grab_left_chopstick;
start_eating(p_duration);
commit; -- drop the chopsticks
exception
when e_deadlock then
log('Philosopher ' || to_char(g_philosopher_id) || ' deadlocked.');
raise;
end start_trying_to_eat_1
;
procedure start_trying_to_eat_2 (p_duration in number)
-- the starvation scenario
as
begin
-- uncomment the code below if you want to see philosophers starve to death
-- if previous_eat_time > interval '1' second
-- then
-- log('Philosopher ' || to_char(g_philosopher_id) || ' starved to death!');
-- raise_application_error(-20101, 'Philosopher ' || to_char(g_philosopher_id) || ' starved to death!');
-- end if
-- ;
grab_right_chopstick;
if left_chopstick_is_free
then
grab_left_chopstick;
start_eating(p_duration);
commit; -- drop the chopsticks
else
rollback; -- drop right chopstick
end if;
end start_trying_to_eat_2
;
procedure start_trying_to_eat_3 (p_duration in number)
-- the partial hierarchy scenario
as
begin
-- uncomment the code below if you want to make sure philosophers don't starve to death
-- if previous_eat_time > interval '1' second
-- then
-- log('Philosopher ' || to_char(g_philosopher_id) || ' starved to death!');
-- raise_application_error(-20101, 'Philosopher ' || to_char(g_philosopher_id) || ' starved to death!');
-- end if
-- ;
if g_philosopher_id = N-1
then
grab_left_chopstick;
grab_right_chopstick;
else
grab_right_chopstick;
grab_left_chopstick;
end if
;
start_eating(p_duration);
commit; -- drop the chopsticks
end start_trying_to_eat_3
;
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 !!
);
end sync
;
procedure wait_for_synchronisation
as
n1 number;
m_handle varchar2(60);
begin
dbms_lock.allocate_unique('Synchronize',m_handle);
n1 := dbms_lock.request(m_handle,dbms_lock.s_mode,dbms_lock.maxwait,true);
end wait_for_synchronisation
;
procedure add_philosopher
( p_philosopher_id in number
, p_thinking in boolean
, p_eat_type in number
, p_max_duration in number default 2
)
as
cn_max_think_time constant number := 0.2;
cn_max_eat_time constant number := 0.2;
l_start_time date;
begin
g_philosopher_id := p_philosopher_id;
wait_for_synchronisation; -- to let all processes start at the exact same time
l_start_time := sysdate;
loop
exit when sysdate > l_start_time + numtodsinterval(p_max_duration,'second');
start_thinking (p_duration => case p_thinking when true then round(dbms_random.value * cn_max_think_time,2) else 0 end);
start_being_hungry;
case p_eat_type
when 1 then
start_trying_to_eat_1 (p_duration => round(dbms_random.value * cn_max_eat_time,2));
when 2 then
start_trying_to_eat_2 (p_duration => round(dbms_random.value * cn_max_eat_time,2));
when 3 then
start_trying_to_eat_3 (p_duration => round(dbms_random.value * cn_max_eat_time,2));
else
start_trying_to_eat_3 (p_duration => round(dbms_random.value * cn_max_eat_time,2));
end case;
end loop;
end add_philosopher
;
begin
initialisation;
end dining_philosophers;
/
show err
set echo off
/*
drop table sticks purge
/
drop table log purge
/
drop table philosophers purge
/

drop package dining_philosophers
/
drop sequence log_seq
/
*/