Fila em Banco: Escalando sua Aplicação com Banco Relacional

Name Null? Type
----------- -------- -------------------
ID NOT NULL NUMBER
CONTENT NOT NULL VARCHAR2(4000 CHAR)
STATUS NOT NULL VARCHAR2(20 CHAR)
THREAD_NAME VARCHAR2(60 CHAR)
describe job_queue;

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;
['NEW',
'FINISHED',
'ERROR']

View Slide

e de um
consumer…

View Slide

procedure polling() as
cursor jobs_cursor is
select jq.*
from job_queue jq
where jq.status = 'NEW';
begin
for job in jobs_cursor loop
-- processa item da fila
print_pdf(job.content);
-- atualiza status
update job_queue j
set j.status = 'FINISHED'
where j.id = job.id;
commit; -- comita a transação
end loop;
end;
begin
-- ...
end;
end;

View Slide

select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
end loop;
end;
begin
-- ...
end;
end;

View Slide

begin
end;
begin
-- ...
end;
end;

View Slide

select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
end loop;
end;
begin
-- ...
end;
end;

View Slide

select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
end loop;
end;
begin
-- ...
end;
end;

View Slide

select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
end loop;
end;
begin
-- ...
end;
end;

View Slide

select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
end loop;
end;
begin
-- ...
end;
end;

View Slide

select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
end loop;
end;
begin
-- ...
end;
end;

View Slide

select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
end loop;
end;
begin
-- ...
end;
end;

View Slide

e alguém para
fazer o polling…

View Slide

BEGIN
DBMS_SCHEDULER.create_job (
job_name => 'PDF_Printer'
,job_type => 'PLSQL_BLOCK'
,job_action =>
'BEGIN
PDF_Printer_Pkg.polling();
END;'
,start_date => systimestamp
,repeat_interval => 'freq=minutely;'
,enabled => true
);
END;

View Slide

BEGIN
,job_action =>
'BEGIN
END;'
,enabled => true
);
END;

View Slide

BEGIN
,job_action =>
'BEGIN
END;'
,enabled => true
);
END;

View Slide

BEGIN
,job_action =>
'BEGIN
END;'
,enabled => true
);
END;

View Slide

INSERT

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;
Uhuuull!!

View Slide

Mas e se…

View Slide

9630 9631
QUEUE (ﬁla)
producer
consumer
9629

View Slide

9630 9631
QUEUE (ﬁla)
producer
consumer
9629
produz:
1000 itens/min

View Slide

9630 9631
QUEUE (ﬁla)
producer
consumer
9629
produz:
1000 itens/min
consume:
250 itens/min

View Slide

e agora?

View Slide

Fácil!
levanta
outro CONSUMER!

View Slide

9630 9631
QUEUE (ﬁla)
producer
consumer
9629
produz:
1000 itens/min

View Slide

9630 9631
QUEUE (ﬁla)
producer
consumer
9629
produz:
1000 itens/min
consumer
consume:
250 itens/min
consume:
250 itens/min

DISTRIBUTED
POLLING

1000

1000
/
250

1000
4
/
250
=

BEGIN
job_name => 'PDF_Printer_1'
,job_action => 'BEGIN PDF_Printer_Pkg.polling(''thread_1''); END;'
-- ...
);
-- ...
);
-- ...
);
,job_action => 'BEGIN PDF_Printer_Pkg.polling(‘'thread_4''); END;'
-- ...
);
END;

View Slide

BEGIN
-- ...
);
-- ...
);
-- ...
);
-- ...
);
END;

View Slide

Name Null? Type
----------- -------- -------------------
ID NOT NULL NUMBER
CONTENT NOT NULL VARCHAR2(4000 CHAR)
STATUS NOT NULL VARCHAR2(20 CHAR)
THREAD_NAME VARCHAR2(60 CHAR)
describe job_queue;

View Slide

procedure polling(p_thread_name varchar2) as
select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
,j.thread_name = p_thread_name
end loop;
end;

View Slide

INSERT

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

YES!

Calma aí…

…mas está mais LENTO

…e tem clientes
recebendo VÁRIOS emails!
…mas está mais LENTO

View Slide

Vamos entender o que
aconteceu…

View Slide

ID STATUS THREAD_NAME
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

1001 NEW
1002 NEW
1003 NEW
1004 NEW
thread_1

View Slide

1001 NEW
1002 NEW
1003 NEW
1004 NEW
thread_1 thread_2

View Slide

1001 NEW
1002 NEW
1003 NEW
1004 NEW
thread_1 thread_2
select j.*
from job_queue j
where j.status = 'NEW';

View Slide

1001 NEW
1002 NEW
1003 NEW
1004 NEW
thread_1 thread_2
select j.*
from job_queue j
{id=1001}

View Slide

1001 NEW
1002 NEW
1003 NEW
1004 NEW
thread_1 thread_2
select j.*
from job_queue j
{id=1001}
select j.*
from job_queue j

View Slide

1001 NEW
1002 NEW
1003 NEW
1004 NEW
thread_1 thread_2
select j.*
from job_queue j
{id=1001}
select j.*
from job_queue j
{id=1001}

View Slide

1001 FINISHED thread_1
1002 NEW
1003 NEW
1004 NEW
thread_1 thread_2
select j.*
from job_queue j
{id=1001}
update job_queue j
,j.thread_name = 'thread_1'
where j.id = 1001;
select j.*
from job_queue j
{id=1001}

View Slide

1002 NEW
1003 NEW
1004 NEW
thread_1 thread_2
select j.*
from job_queue j
{id=1001}
update job_queue j
where j.id = 1001;
commit;
select j.*
from job_queue j
{id=1001}

View Slide

1002 NEW
1003 NEW
1004 NEW
thread_1 thread_2
select j.*
from job_queue j
{id=1001}
select j.*
from job_queue j
{id=1001}
update job_queue j
where j.id = 1001;
commit;
update job_queue j
,j.thread_name = ‘thread_2'
where j.id = 1001;
commit;
thread_2

View Slide

1003 NEW
1004 NEW
thread_1 thread_2
select j.*
from job_queue j
{id=1002}
select j.*
from job_queue j
{id=1002}
update job_queue j
where j.id = 1002;
commit;
update job_queue j
where j.id = 1002;
commit;
thread_2
thread_2

View Slide

1004 NEW
thread_1 thread_2
select j.*
from job_queue j
{id=1003}
select j.*
from job_queue j
{id=1003}
update job_queue j
where j.id = 1003;
commit;
update job_queue j
where j.id = 1003;
commit;
thread_2
thread_1
thread_2

View Slide

thread_1 thread_2
select j.*
from job_queue j
{id=1004}
select j.*
from job_queue j
{id=1004}
update job_queue j
where j.id = 1004;
commit;
update job_queue j
where j.id = 1004;
commit;
thread_2
thread_1
thread_2
thread_2

View Slide

NO!

View Slide

o desenho da solução
deve ser pensado para
multi-threaded
* desenho e implementação
*

View Slide

e agora?

View Slide

Podemos
LOCKAR as
linhas

View Slide

select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
set j.status = ‘FINISHED'
end loop;
end;

View Slide

select jq.*
from job_queue jq
begin
-- atualiza status
update job_queue j
end loop;
end;

View Slide

select jq.*
from job_queue jq
begin
-- processa item
commit;
end loop;
end;

View Slide

select jq.*
from job_queue jq
begin
-- processa item
commit;
end loop;
end;

View Slide

select jq.*
from job_queue jq
where jq.status = 'NEW'
for update;
begin
-- processa item
commit;
end loop;
end;

View Slide

select jq.*
from job_queue jq
for update;
begin
-- processa item
commit;
end loop;
end;
locka
TODOS os
registros

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
-- processa item
commit;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
-- processa item
commit;
end loop;
end;
SELECT…FOR UPDATE
não suporta
FETCHING-ACROSS-COMMITS

View Slide

select jq.*
from job_queue jq
where jq.status = ‘NEW’
and rownum = 1
for update;
begin
-- processa item
commit;
end loop;
end;
Vai sair
muito cedo
do loop

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
-- processa item
commit;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
-- processa item
commit;
end loop;
end;

View Slide

select jq.*
from job_queue jq
where jq.status = ‘NEW'
and rownum = 1
for update;
job jobs_cursor%rowtype;
begin
loop
open jobs_cursor;
fetch jobs_cursor into job;
exit when jobs_cursor%notfound;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;
lock ocorre
no OPENING

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

INSERT

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

View Slide

select j.*
from job_queue j
;

YES!

Calma aí…

…não há duplicação

…não há duplicação
…mas ainda
não está rápido
o suﬁciente!

View Slide

Vamos entender o que
aconteceu…

View Slide

select j.*
from job_queue j
where j.status = 'NEW'
and rownum = 1
for update;
select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

update job_queue j
where j.id = 1001;
select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1002 NEW
1003 NEW
1004 NEW
commit;

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1002 NEW
1003 NEW
1004 NEW
commit;

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1002 NEW
1003 NEW
1004 NEW
select j.*
from job_queue j
and rownum = 1
for update;

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1002 NEW
1003 NEW
1004 NEW
select j.*
from job_queue j
and rownum = 1
for update;

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1002 NEW
1003 NEW
1004 NEW
select j.*
from job_queue j
and rownum = 1
for update;

View Slide

update job_queue j
where j.id = 1002;
thread_1 thread_2
1003 NEW
1004 NEW
select j.*
from job_queue j
and rownum = 1
for update;

View Slide

commit;
thread_1 thread_2
1003 NEW
1004 NEW
select j.*
from job_queue j
and rownum = 1
for update;

View Slide

commit;
thread_1 thread_2
1003 NEW
1004 NEW
select j.*
from job_queue j
and rownum = 1
for update;

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1003 NEW
1004 NEW
select j.*
from job_queue j
and rownum = 1
for update;

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
1004 NEW
select j.*
from job_queue j
and rownum = 1
for update;

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
select j.*
from job_queue j
and rownum = 1
for update;

View Slide

select j.*
from job_queue j
and rownum = 1
for update;
thread_1 thread_2
select j.*
from job_queue j
and rownum = 1
for update;

humm…

e agora?

Pensando…

Que tal
tentarmos o
SKIP LOCKED?

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
for update skip locked;
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

select jq.*
from job_queue jq
and rownum = 1
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;
lock ocorre
no FETCHING

View Slide

select jq.*
from job_queue jq
and rownum = 1
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;
lock ocorre
no FETCHING

View Slide

select jq.*
from job_queue jq
begin
loop
open jobs_cursor;
-- processa item
commit;
close jobs_cursor;
end loop;
end;

View Slide

Qual a
diferença?

View Slide

select j.*
from job_queue j
for update SKIP LOCKED;
select j.*
from job_queue j
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW

View Slide

update job_queue j
where j.id = 1001;
update job_queue j
where j.id = 1002;
thread_1 thread_2
1003 NEW
1004 NEW

View Slide

commit; update job_queue j
where j.id = 1002;
thread_1 thread_2
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
commit;
thread_1 thread_2
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1003 NEW
1004 NEW

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1003 NEW
1004 NEW

View Slide

Ou seja…

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW
select j.*
from job_queue j
select j.*
from job_queue j
thread_3 thread_4

View Slide

select j.*
from job_queue j
select j.*
from job_queue j
thread_1 thread_2
1001 NEW
1002 NEW
1003 NEW
1004 NEW
select j.*
from job_queue j
select j.*
from job_queue j
thread_3 thread_4