$30 off During Our Annual Pro Sale. View Details »

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

Rafael Ponte
February 12, 2020

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

Um dos principais pilares para escalar uma aplicação web é o uso mensageria, não à toa temos diversos Brokers para enfileirar as mensagens a fim de processá-las num ritmo que nossa aplicação consiga atender.

Mas será mesmo que precisamos adotar um Broker? A verdade é que não; por mais controverso que pareça, podemos usar um banco de dados relacional como uma fila de mensagens, aproveitando sua natureza ACID e todo seu controle de locking e concorrência. Com isso, obtemos um alto grau de performance e principalmente escalabilidade sem a necessidade de abrir mão do poder de um banco de dados ou ter que adicionar um novo componente de software (como um JMS Broker) na arquitetura da solução.

Nessa talk, você vai aprender como implementar mensageria utilizando um RDBMS através da feature SKIP LOCKED e entender que escalabilidade não se resume a Kafka ou Brokers MQ.

(GRAVAÇÃO: https://www.youtube.com/watch?v=8DVFc7gXfIQ)

Rafael Ponte

February 12, 2020
Tweet

More Decks by Rafael Ponte

Other Decks in Technology

Transcript

  1. FILA EM BANCO
    escalando sua aplicação com banco relacional

    View Slide

  2. View Slide

  3. View Slide

  4. View Slide

  5. Hype-Driven Development
    https://blog.daftcode.pl/hype-driven-development-3469fc2e9b22

    View Slide

  6. View Slide

  7. CACHE

    View Slide

  8. CACHE
    PROCESSAMENTO
    ASSINCRONO

    View Slide

  9. CACHE
    BALANCEAMENTO
    DE CARGA
    PROCESSAMENTO
    ASSINCRONO

    View Slide

  10. CACHE
    BALANCEAMENTO
    DE CARGA
    PROCESSAMENTO
    ASSINCRONO

    View Slide

  11. a idéia é simples…

    View Slide

  12. servidor

    View Slide

  13. db
    servidor

    View Slide

  14. navegador db
    servidor

    View Slide

  15. navegador db
    servidor
    requisição

    View Slide

  16. navegador db
    servidor
    requisição
    resposta

    View Slide

  17. e funciona muito
    bem…

    View Slide

  18. db
    servidor
    requisição
    resposta

    View Slide

  19. db
    servidor
    requisição
    resposta

    View Slide

  20. db
    servidor
    requisição
    resposta

    View Slide

  21. db
    servidor
    requisição
    resposta

    View Slide

  22. calma!

    View Slide

  23. navegador db
    servidor
    requisição
    resposta

    View Slide

  24. navegador db
    servidor
    requisição
    resposta
    ???

    View Slide

  25. navegador db
    servidor
    requisição
    resposta

    View Slide

  26. navegador db
    servidor
    requisição
    resposta
    fila

    View Slide

  27. navegador db
    servidor
    requisição
    resposta
    fila
    consumidor

    View Slide

  28. navegador db
    servidor
    requisição
    resposta
    fila
    consumidor

    View Slide

  29. navegador db
    servidor
    requisição
    resposta
    fila
    consumidor

    View Slide

  30. mas qual broker?

    View Slide

  31. View Slide

  32. View Slide

  33. View Slide

  34. View Slide

  35. mas qual o custo?

    View Slide

  36. mas qual o custo?
    Segurança
    Backup
    Replicação
    Monitoramento
    Upgrade
    Recovery
    Fail-over
    Alta-disponibilidade
    Hardware

    View Slide

  37. E agora?

    View Slide

  38. Banco de dados

    View Slide

  39. Sua app precisa
    dessa escala
    TODA?

    View Slide

  40. Como escalar seu
    sistema usando
    FILA EM BANCO

    View Slide

  41. Rafael Ponte
    @rponte

    View Slide

  42. View Slide

  43. Fortaleza - Terra do Sol

    View Slide

  44. View Slide

  45. FILA EM BANCO
    escalando sua aplicação com banco relacional

    View Slide

  46. nosso problema

    View Slide

  47. View Slide

  48. View Slide

  49. View Slide

  50. View Slide

  51. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    end;
    end;

    View Slide

  52. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    end;
    end;

    View Slide

  53. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    end;
    end;

    View Slide

  54. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    end;
    end;

    View Slide

  55. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    end;
    end;

    View Slide

  56. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    end;
    end;

    View Slide

  57. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    -- valida XML
    end;
    end;

    View Slide

  58. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    -- valida XML
    -- processa XML e carrega dados
    end;
    end;

    View Slide

  59. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    -- valida XML
    -- processa XML e carrega dados
    -- gera PDF
    end;
    end;

    View Slide

  60. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    -- valida XML
    -- processa XML e carrega dados
    -- gera PDF
    -- faz upload para disco remoto
    end;
    end;

    View Slide

  61. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    -- valida XML
    -- processa XML e carrega dados
    -- gera PDF
    -- faz upload para disco remoto
    -- envia por email
    end;
    end;

    View Slide

  62. create package body PDF_Printer_Pkg as
    procedure print_pdf(p_xml varchar2) as
    begin
    -- valida XML
    -- processa XML e carrega dados
    -- gera PDF
    -- faz upload para disco remoto
    -- envia por email
    end;
    end;

    View Slide

  63. db

    View Slide

  64. db
    servidor

    View Slide

  65. db
    servidor
    navegador

    View Slide

  66. db
    servidor
    navegador
    requisição

    View Slide

  67. db
    servidor
    navegador
    requisição

    View Slide

  68. db
    servidor
    navegador
    requisição

    View Slide

  69. db
    servidor
    navegador
    requisição

    View Slide

  70. db
    servidor
    requisição

    View Slide

  71. db
    servidor
    requisição

    View Slide

  72. db
    servidor
    requisição

    View Slide

  73. db
    servidor
    requisição
    servidor
    servidor

    View Slide

  74. db
    servidor
    requisição
    servidor
    servidor

    View Slide

  75. View Slide

  76. vamos rodar em
    BACKGROUND !!!

    View Slide

  77. COMO?

    View Slide

  78. a gente
    joga numa
    FILA !

    View Slide

  79. 4 3 2
    QUEUE (fila)

    View Slide

  80. 4 3 2
    5
    QUEUE (fila)

    View Slide

  81. 4 3 2
    5
    enqueue
    QUEUE (fila)

    View Slide

  82. 4 3 2
    5
    1
    enqueue
    QUEUE (fila)

    View Slide

  83. 4 3 2
    5
    1
    enqueue
    dequeue
    QUEUE (fila)

    View Slide

  84. 4 3 2
    5
    1
    enqueue
    dequeue
    QUEUE (fila)
    FIFO: First-in, First-out

    View Slide

  85. QUEUE
    (no mundo enterprise)

    View Slide

  86. 1002 1001
    QUEUE
    (no mundo enterprise)

    View Slide

  87. 1002 1001
    (message queue)
    QUEUE
    (no mundo enterprise)

    View Slide

  88. 1002 1001
    3506
    QUEUE
    (no mundo enterprise)
    (message queue)

    View Slide

  89. 1002 1001
    3506
    QUEUE
    (no mundo enterprise)
    (message)
    (message queue)

    View Slide

  90. 1002 1001
    producer
    QUEUE
    (no mundo enterprise)
    (message queue)

    View Slide

  91. 1002 1001
    producer
    1000
    QUEUE
    (no mundo enterprise)
    (message queue)

    View Slide

  92. 1002 1001
    producer
    consumer
    QUEUE
    (no mundo enterprise)
    (message queue)

    View Slide

  93. ONDE?

    View Slide

  94. no BANCO,
    ué!

    View Slide

  95. tabela

    View Slide

  96. db
    servidor
    navegador

    View Slide

  97. db
    servidor
    navegador
    fila

    View Slide

  98. db
    servidor
    navegador
    fila
    x

    View Slide

  99. db
    servidor
    navegador
    requisição
    fila

    View Slide

  100. db
    servidor
    navegador
    requisição
    INSERT
    fila

    View Slide

  101. db
    servidor
    navegador
    requisição
    INSERT
    fila
    resposta

    View Slide

  102. db
    servidor
    navegador
    requisição
    INSERT
    fila

    View Slide

  103. db
    servidor
    navegador
    requisição
    INSERT
    quem executa? fila

    View Slide

  104. db
    servidor
    navegador
    requisição
    INSERT
    fila

    View Slide

  105. db
    servidor
    navegador
    requisição
    INSERT
    fila

    View Slide

  106. e a vantagem?

    View Slide

  107. db
    servidor
    requisição
    INSERT

    View Slide

  108. db
    servidor
    requisição
    INSERT

    View Slide

  109. db
    servidor
    requisição
    INSERT

    View Slide

  110. db
    servidor
    requisição
    INSERT
    x

    View Slide

  111. INSERT

    View Slide

  112. INSERT

    View Slide

  113. POLLING

    View Slide

  114. como implementa?

    View Slide

  115. precisamos de
    uma tabela…

    View Slide

  116. describe job_queue;

    View Slide

  117. 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

  118. select j.*
    from job_queue j
    ;

    View Slide

  119. select j.*
    from job_queue j
    ;

    View Slide

  120. select j.*
    from job_queue j
    ;

    View Slide

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

    View Slide

  122. e de um
    consumer…

    View Slide

  123. create package body PDF_Printer_Pkg as
    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;
    procedure print_pdf(p_xml varchar2) as
    begin
    -- ...
    end;
    end;

    View Slide

  124. create package body PDF_Printer_Pkg as
    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;
    procedure print_pdf(p_xml varchar2) as
    begin
    -- ...
    end;
    end;

    View Slide

  125. create package body PDF_Printer_Pkg as
    procedure polling() as
    begin
    end;
    procedure print_pdf(p_xml varchar2) as
    begin
    -- ...
    end;
    end;

    View Slide

  126. create package body PDF_Printer_Pkg as
    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;
    procedure print_pdf(p_xml varchar2) as
    begin
    -- ...
    end;
    end;

    View Slide

  127. create package body PDF_Printer_Pkg as
    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;
    procedure print_pdf(p_xml varchar2) as
    begin
    -- ...
    end;
    end;

    View Slide

  128. create package body PDF_Printer_Pkg as
    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;
    procedure print_pdf(p_xml varchar2) as
    begin
    -- ...
    end;
    end;

    View Slide

  129. create package body PDF_Printer_Pkg as
    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;
    procedure print_pdf(p_xml varchar2) as
    begin
    -- ...
    end;
    end;

    View Slide

  130. create package body PDF_Printer_Pkg as
    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;
    procedure print_pdf(p_xml varchar2) as
    begin
    -- ...
    end;
    end;

    View Slide

  131. create package body PDF_Printer_Pkg as
    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;
    procedure print_pdf(p_xml varchar2) as
    begin
    -- ...
    end;
    end;

    View Slide

  132. e alguém para
    fazer o polling…

    View Slide

  133. 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

  134. 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

  135. 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

  136. 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

  137. INSERT

    View Slide

  138. select j.*
    from job_queue j
    ;

    View Slide

  139. select j.*
    from job_queue j
    ;

    View Slide

  140. select j.*
    from job_queue j
    ;

    View Slide

  141. select j.*
    from job_queue j
    ;
    Uhuuull!!

    View Slide

  142. Mas e se…

    View Slide

  143. 9630 9631
    QUEUE (fila)
    producer
    consumer
    9629

    View Slide

  144. 9630 9631
    QUEUE (fila)
    producer
    consumer
    9629
    produz:
    1000 itens/min

    View Slide

  145. 9630 9631
    QUEUE (fila)
    producer
    consumer
    9629
    produz:
    1000 itens/min
    consume:
    250 itens/min

    View Slide

  146. e agora?

    View Slide

  147. Fácil!
    levanta
    outro CONSUMER!

    View Slide

  148. 9630 9631
    QUEUE (fila)
    producer
    consumer
    9629
    produz:
    1000 itens/min

    View Slide

  149. 9630 9631
    QUEUE (fila)
    producer
    consumer
    9629
    produz:
    1000 itens/min
    consumer
    consume:
    250 itens/min
    consume:
    250 itens/min

    View Slide

  150. DISTRIBUTED
    POLLING

    View Slide

  151. 1000

    View Slide

  152. 1000
    /
    250

    View Slide

  153. 1000
    4
    /
    250
    =

    View Slide

  154. BEGIN
    DBMS_SCHEDULER.create_job (
    job_name => 'PDF_Printer_1'
    ,job_action => 'BEGIN PDF_Printer_Pkg.polling(''thread_1''); END;'
    -- ...
    );
    DBMS_SCHEDULER.create_job (
    job_name => 'PDF_Printer_2'
    ,job_action => 'BEGIN PDF_Printer_Pkg.polling(''thread_2''); END;'
    -- ...
    );
    DBMS_SCHEDULER.create_job (
    job_name => 'PDF_Printer_3'
    ,job_action => 'BEGIN PDF_Printer_Pkg.polling(''thread_3''); END;'
    -- ...
    );
    DBMS_SCHEDULER.create_job (
    job_name => 'PDF_Printer_4'
    ,job_action => 'BEGIN PDF_Printer_Pkg.polling(‘'thread_4''); END;'
    -- ...
    );
    END;

    View Slide

  155. BEGIN
    DBMS_SCHEDULER.create_job (
    job_name => 'PDF_Printer_1'
    ,job_action => 'BEGIN PDF_Printer_Pkg.polling(''thread_1''); END;'
    -- ...
    );
    DBMS_SCHEDULER.create_job (
    job_name => 'PDF_Printer_2'
    ,job_action => 'BEGIN PDF_Printer_Pkg.polling(''thread_2''); END;'
    -- ...
    );
    DBMS_SCHEDULER.create_job (
    job_name => 'PDF_Printer_3'
    ,job_action => 'BEGIN PDF_Printer_Pkg.polling(''thread_3''); END;'
    -- ...
    );
    DBMS_SCHEDULER.create_job (
    job_name => 'PDF_Printer_4'
    ,job_action => 'BEGIN PDF_Printer_Pkg.polling(''thread_4''); END;'
    -- ...
    );
    END;

    View Slide

  156. 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

  157. procedure polling(p_thread_name varchar2) 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'
    ,j.thread_name = p_thread_name
    where j.id = job.id;
    commit; -- comita a transação
    end loop;
    end;

    View Slide

  158. INSERT

    View Slide

  159. select j.*
    from job_queue j
    ;

    View Slide

  160. select j.*
    from job_queue j
    ;

    View Slide

  161. select j.*
    from job_queue j
    ;

    View Slide

  162. select j.*
    from job_queue j
    ;

    View Slide

  163. YES!

    View Slide

  164. Calma aí…

    View Slide

  165. …mas está mais LENTO

    View Slide

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

    View Slide

  167. Vamos entender o que
    aconteceu…

    View Slide

  168. ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW

    View Slide

  169. ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW
    thread_1

    View Slide

  170. ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW
    thread_1 thread_2

    View Slide

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

    View Slide

  172. ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW
    thread_1 thread_2
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1001}

    View Slide

  173. ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW
    thread_1 thread_2
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1001}
    select j.*
    from job_queue j
    where j.status = 'NEW';

    View Slide

  174. ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW
    thread_1 thread_2
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1001}
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1001}

    View Slide

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

    View Slide

  176. ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 NEW
    1003 NEW
    1004 NEW
    thread_1 thread_2
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1001}
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = 'thread_1'
    where j.id = 1001;
    commit;
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1001}

    View Slide

  177. ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 NEW
    1003 NEW
    1004 NEW
    thread_1 thread_2
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1001}
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1001}
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = 'thread_1'
    where j.id = 1001;
    commit;
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = ‘thread_2'
    where j.id = 1001;
    commit;
    thread_2

    View Slide

  178. ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_1
    1003 NEW
    1004 NEW
    thread_1 thread_2
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1002}
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1002}
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = 'thread_1'
    where j.id = 1002;
    commit;
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = ‘thread_2'
    where j.id = 1002;
    commit;
    thread_2
    thread_2

    View Slide

  179. ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_1
    1003 FINISHED thread_2
    1004 NEW
    thread_1 thread_2
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1003}
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1003}
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = 'thread_1'
    where j.id = 1003;
    commit;
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = ‘thread_2'
    where j.id = 1003;
    commit;
    thread_2
    thread_1
    thread_2

    View Slide

  180. ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_1
    1003 FINISHED thread_2
    1004 FINISHED thread_1
    thread_1 thread_2
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1004}
    select j.*
    from job_queue j
    where j.status = 'NEW';
    {id=1004}
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = 'thread_1'
    where j.id = 1004;
    commit;
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = ‘thread_2'
    where j.id = 1004;
    commit;
    thread_2
    thread_1
    thread_2
    thread_2

    View Slide

  181. NO!

    View Slide

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

    View Slide

  183. e agora?

    View Slide

  184. Podemos
    LOCKAR as
    linhas

    View Slide

  185. procedure polling(p_thread_name varchar2) 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'
    ,j.thread_name = p_thread_name
    where j.id = job.id;
    commit; -- comita a transação
    end loop;
    end;

    View Slide

  186. procedure polling(p_thread_name varchar2) 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'
    ,j.thread_name = p_thread_name
    where j.id = job.id;
    commit; -- comita a transação
    end loop;
    end;

    View Slide

  187. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW';
    begin
    for job in jobs_cursor loop
    -- processa item
    commit;
    end loop;
    end;

    View Slide

  188. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW';
    begin
    for job in jobs_cursor loop
    -- processa item
    commit;
    end loop;
    end;

    View Slide

  189. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW'
    for update;
    begin
    for job in jobs_cursor loop
    -- processa item
    commit;
    end loop;
    end;

    View Slide

  190. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW'
    for update;
    begin
    for job in jobs_cursor loop
    -- processa item
    commit;
    end loop;
    end;
    locka
    TODOS os
    registros

    View Slide

  191. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW'
    and rownum = 1
    for update;
    begin
    for job in jobs_cursor loop
    -- processa item
    commit;
    end loop;
    end;

    View Slide

  192. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW'
    and rownum = 1
    for update;
    begin
    for job in jobs_cursor loop
    -- processa item
    commit;
    end loop;
    end;
    SELECT…FOR UPDATE
    não suporta
    FETCHING-ACROSS-COMMITS

    View Slide

  193. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = ‘NEW’
    and rownum = 1
    for update;
    begin
    for job in jobs_cursor loop
    -- processa item
    commit;
    end loop;
    end;
    Vai sair
    muito cedo
    do loop

    View Slide

  194. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = ‘NEW’
    and rownum = 1
    for update;
    begin
    for job in jobs_cursor loop
    -- processa item
    commit;
    end loop;
    end;

    View Slide

  195. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = ‘NEW’
    and rownum = 1
    for update;
    begin
    for job in jobs_cursor loop
    -- processa item
    commit;
    end loop;
    end;

    View Slide

  196. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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

  197. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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

  198. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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

  199. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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;
    lock ocorre
    no OPENING

    View Slide

  200. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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

  201. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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

  202. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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

  203. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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

  204. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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

  205. INSERT

    View Slide

  206. select j.*
    from job_queue j
    ;

    View Slide

  207. select j.*
    from job_queue j
    ;

    View Slide

  208. select j.*
    from job_queue j
    ;

    View Slide

  209. select j.*
    from job_queue j
    ;

    View Slide

  210. YES!

    View Slide

  211. Calma aí…

    View Slide

  212. …não há duplicação

    View Slide

  213. …não há duplicação
    …mas ainda
    não está rápido
    o suficiente!

    View Slide

  214. Vamos entender o que
    aconteceu…

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  219. update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = 'thread_1'
    where j.id = 1001;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    and rownum = 1
    for update;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 NEW
    1003 NEW
    1004 NEW

    View Slide

  220. select j.*
    from job_queue j
    where j.status = 'NEW'
    and rownum = 1
    for update;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 NEW
    1003 NEW
    1004 NEW
    commit;

    View Slide

  221. select j.*
    from job_queue j
    where j.status = 'NEW'
    and rownum = 1
    for update;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 NEW
    1003 NEW
    1004 NEW
    commit;

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  225. update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = ‘thread_2'
    where j.id = 1002;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW
    select j.*
    from job_queue j
    where j.status = 'NEW'
    and rownum = 1
    for update;

    View Slide

  226. commit;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW
    select j.*
    from job_queue j
    where j.status = 'NEW'
    and rownum = 1
    for update;

    View Slide

  227. commit;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW
    select j.*
    from job_queue j
    where j.status = 'NEW'
    and rownum = 1
    for update;

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  232. humm…

    View Slide

  233. e agora?

    View Slide

  234. Pensando…

    View Slide

  235. Que tal
    tentarmos o
    SKIP LOCKED?

    View Slide

  236. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    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

  237. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW'
    and rownum = 1
    for update skip locked;
    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

  238. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW'
    and rownum = 1
    for update skip locked;
    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;
    lock ocorre
    no FETCHING

    View Slide

  239. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW'
    and rownum = 1
    for update skip locked;
    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;
    lock ocorre
    no FETCHING

    View Slide

  240. procedure polling(p_thread_name varchar2) as
    cursor jobs_cursor is
    select jq.*
    from job_queue jq
    where jq.status = 'NEW'
    for update skip locked;
    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

  241. Qual a
    diferença?

    View Slide

  242. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW

    View Slide

  243. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW

    View Slide

  244. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW

    View Slide

  245. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW

    View Slide

  246. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW

    View Slide

  247. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW

    View Slide

  248. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW

    View Slide

  249. update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = ‘thread_1'
    where j.id = 1001;
    update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = 'thread_2'
    where j.id = 1002;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW

    View Slide

  250. commit; update job_queue j
    set j.status = 'FINISHED'
    ,j.thread_name = 'thread_2'
    where j.id = 1002;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW

    View Slide

  251. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    commit;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW

    View Slide

  252. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW

    View Slide

  253. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW

    View Slide

  254. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW

    View Slide

  255. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 FINISHED thread_1
    1002 FINISHED thread_2
    1003 NEW
    1004 NEW

    View Slide

  256. Ou seja…

    View Slide

  257. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_3 thread_4

    View Slide

  258. select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_1 thread_2
    ID STATUS THREAD_NAME
    1001 NEW
    1002 NEW
    1003 NEW
    1004 NEW
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    select j.*
    from job_queue j
    where j.status = 'NEW'
    for update SKIP LOCKED;
    thread_3 thread_4

    View Slide

  259. INSERT

    View Slide

  260. INSERT

    View Slide

  261. INSERT

    View Slide

  262. YES!

    View Slide

  263. Concluindo

    View Slide

  264. single-threaded funciona bem…

    View Slide

  265. single-threaded funciona bem…
    https://www.pgcon.org/2016/schedule/track/Applications/929.en.html

    View Slide

  266. multi-threaded com FOR UPDATE…

    View Slide

  267. multi-threaded com FOR UPDATE…
    https://www.pgcon.org/2016/schedule/track/Applications/929.en.html

    View Slide

  268. multi-threaded com SKIP LOCKED…

    View Slide

  269. multi-threaded com SKIP LOCKED…
    https://www.pgcon.org/2016/schedule/track/Applications/929.en.html

    View Slide

  270. ESCALABILIDADE

    View Slide

  271. (desde 10g)
    (desde 8.0)
    (desde 2005)
    (desde 9.5)

    View Slide

  272. ORACLE AQ
    (Advanced Queue)

    View Slide

  273. View Slide

  274. OBRIGADO!

    View Slide

  275. @rponte
    Rafael Ponte

    View Slide