10 Features do Oracle que voce não conhecia - SKIP LOCKED

(Palestra ministrada no TechDay da MDIAS em JUL-2019)

Todo banco de dados possui features na qual desconhecemos, o que acaba nos levando a soluções mais complicadas ou menos eficientes, com o Oracle não é diferente. Por esse motivo, pretendo apresentar de forma bastante didática uma série com 10 features do Oracle 11g que eu desconhecia e que tem facilitado bastante minha vida desde meados de 2016.

Para segunda feature nesta série, apresento o SKIP LOCKED, na qual nos possibilita usar uma tabela no banco de dados como uma FILA (QUEUE) de forma eficiente e simples. Graças a ela conseguimos delegar todo o controle de locking e concorrência para o banco de dados, facilitando assim a implementação dos clientes (producers e consumers) das mensagens na fila. Com isso, obtemos um alto grau de performance e principalmente escalabilidade sem a necessidade de abrir mão do poder do banco de dados ou ter que adicionar um novo componente de software (como um JMS Broker) na arquitetura da solução.

Transcript

1. 10 features do Oracle
   que você não conhecia
   #2 SKIP LOCKED
   TechDay 2019

   Rafael Ponte - @rponte
   

   View Slide

2. ou…
   

   View Slide

3. Como escalar seu
   sistema usando
   FILA EM BANCO
   

   View Slide

4. nosso problema
   

   View Slide

5. View Slide

6. View Slide

7. View Slide

8. View Slide

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

   View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

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

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

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

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

21. db
    

    View Slide

22. db
    servidor
    

    View Slide

23. db
    servidor
    navegador
    

    View Slide

24. db
    servidor
    navegador
    requisição
    

    View Slide

25. db
    servidor
    navegador
    requisição
    

    View Slide

26. db
    servidor
    navegador
    requisição
    

    View Slide

27. db
    servidor
    navegador
    requisição
    

    View Slide

28. db
    servidor
    requisição
    

    View Slide

29. db
    servidor
    requisição
    

    View Slide

30. db
    servidor
    requisição
    

    View Slide

31. db
    servidor
    requisição
    servidor
    servidor
    

    View Slide

32. db
    servidor
    requisição
    servidor
    servidor
    

    View Slide

33. View Slide

34. vamos rodar em 

    BACKGROUND !!!
    

    View Slide

35. COMO?
    

    View Slide

36. a gente
    joga numa
    FILA !
    

    View Slide

37. 4 3 2
    QUEUE (fila)
    

    View Slide

38. 4 3 2
    5
    QUEUE (fila)
    

    View Slide

39. 4 3 2
    5
    enqueue
    QUEUE (fila)
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

43. QUEUE
    (no mundo enterprise)
    

    View Slide

44. 1002 1001
    QUEUE
    (no mundo enterprise)
    

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

51. ONDE?
    

    View Slide

52. no BANCO,
    ué!
    

    View Slide

53. tabela
    

    View Slide

54. db
    servidor
    navegador
    

    View Slide

55. db
    servidor
    navegador
    fila
    

    View Slide

56. db
    servidor
    navegador
    fila
    x
    

    View Slide

57. db
    servidor
    navegador
    requisição
    fila
    

    View Slide

58. db
    servidor
    navegador
    requisição
    INSERT
    fila
    

    View Slide

59. db
    servidor
    navegador
    requisição
    INSERT
    fila
    

    View Slide

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

    View Slide

61. db
    servidor
    navegador
    requisição
    INSERT
    fila
    

    View Slide

62. db
    servidor
    navegador
    requisição
    INSERT
    fila
    

    View Slide

63. e a vantagem?
    

    View Slide

64. db
    servidor
    requisição
    INSERT
    

    View Slide

65. db
    servidor
    requisição
    INSERT
    

    View Slide

66. db
    servidor
    requisição
    INSERT
    

    View Slide

67. db
    servidor
    requisição
    INSERT
    x
    

    View Slide

68. INSERT
    

    View Slide

69. INSERT
    

    View Slide

70. POLLING
    

    View Slide

71. como implementa?
    

    View Slide

72. precisamos de
    uma tabela…
    

    View Slide

73. describe job_queue;
    

    View Slide

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

75. select j.*
    from job_queue j
    ;
    

    View Slide

76. select j.*
    from job_queue j
    ;
    

    View Slide

77. select j.*
    from job_queue j
    ;
    

    View Slide

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

    View Slide

79. e de um
    consumer…
    

    View Slide

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

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

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

    View Slide

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

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

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

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

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

88. e alguém para
    fazer o polling…
    

    View Slide

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

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

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

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

93. INSERT
    

    View Slide

94. select j.*
    from job_queue j
    ;
    

    View Slide

95. select j.*
    from job_queue j
    ;
    

    View Slide

96. select j.*
    from job_queue j
    ;
    

    View Slide

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

    View Slide

98. Mas e se…
    

    View Slide

99. 9630 9631
    QUEUE (fila)
    producer
    consumer
    9629
    

    View Slide

100. 9630 9631
     QUEUE (fila)
     producer
     consumer
     9629
     produz:

     1000 itens/min
     

     View Slide

101. 9630 9631
     QUEUE (fila)
     producer
     consumer
     9629
     produz:

     1000 itens/min
     consume:

     250 itens/min
     

     View Slide

102. e agora?
     

     View Slide

103. Fácil! 

     levanta
     outro CONSUMER!
     

     View Slide

104. 9630 9631
     QUEUE (fila)
     producer
     consumer
     9629
     produz:

     1000 itens/min
     

     View Slide

105. 9630 9631
     QUEUE (fila)
     producer
     consumer
     9629
     produz:

     1000 itens/min
     consumer
     consume:

     250 itens/min
     consume:

     250 itens/min
     

     View Slide

106. DISTRIBUTED
     POLLING
     

     View Slide

107. 1000
     4
     /
     250
     =
     

     View Slide

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

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

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

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

112. INSERT
     

     View Slide

113. select j.*
     from job_queue j
     ;
     

     View Slide

114. select j.*
     from job_queue j
     ;
     

     View Slide

115. select j.*
     from job_queue j
     ;
     

     View Slide

116. select j.*
     from job_queue j
     ;
     

     View Slide

117. YES!
     

     View Slide

118. Calma aí…
     

     View Slide

119. …mas está mais LENTO
     

     View Slide

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

     View Slide

121. Vamos entender o que
     aconteceu…
     

     View Slide

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

     View Slide

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

     View Slide

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

     View Slide

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

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

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

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

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

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

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

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

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

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

     View Slide

135. NO!
     

     View Slide

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

     View Slide

137. e agora?
     

     View Slide

138. Podemos
     LOCKAR as
     linhas
     

     View Slide

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

159. INSERT
     

     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. select j.*
     from job_queue j
     ;
     

     View Slide

164. YES!
     

     View Slide

165. Calma aí…
     

     View Slide

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

     View Slide

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

     View Slide

168. Vamos entender o que
     aconteceu…
     

     View Slide

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

186. humm…
     

     View Slide

187. e agora?
     

     View Slide

188. Pensando…
     

     View Slide

189. Que tal
     tentarmos o
     SKIP LOCKED?
     

     View Slide

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

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

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

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

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

195. Qual a
     diferença?
     

     View Slide

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

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

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

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

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

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

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

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

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

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

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

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

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

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

210. Ou seja…
     

     View Slide

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

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

213. INSERT
     

     View Slide

214. INSERT
     

     View Slide

215. INSERT
     

     View Slide

216. YES!
     

     View Slide

217. Concluindo
     

     View Slide

218. single-threaded funciona bem…
     

     View Slide

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

     View Slide

220. multi-threaded com FOR UPDATE…
     

     View Slide

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

     View Slide

222. multi-threaded com SKIP LOCKED…
     

     View Slide

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

     View Slide

224. ESCALABILIDADE
     

     View Slide

225. ORACLE AQ
     (Advanced Queue)
     

     View Slide

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

     View Slide

227. OBRIGADO!
     

     View Slide