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

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.

F853760c988228c4a153333407e64f09?s=128

Rafael Ponte

July 04, 2019
Tweet

Transcript

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

    LOCKED TechDay 2019
 Rafael Ponte - @rponte
  2. ou…

  3. Como escalar seu sistema usando FILA EM BANCO

  4. nosso problema

  5. None
  6. None
  7. None
  8. None
  9. create package body PDF_Printer_Pkg as procedure print_pdf(p_xml varchar2) as begin

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

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

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

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

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

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

    -- valida XML end; end;
  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;
  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;
  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;
  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;
  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;
  21. db

  22. db servidor

  23. db servidor navegador

  24. db servidor navegador requisição

  25. db servidor navegador requisição

  26. db servidor navegador requisição

  27. db servidor navegador requisição

  28. db servidor requisição

  29. db servidor requisição

  30. db servidor requisição

  31. db servidor requisição servidor servidor

  32. db servidor requisição servidor servidor

  33. None
  34. vamos rodar em 
 BACKGROUND !!!

  35. COMO?

  36. a gente joga numa FILA !

  37. 4 3 2 QUEUE (fila)

  38. 4 3 2 5 QUEUE (fila)

  39. 4 3 2 5 enqueue QUEUE (fila)

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

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

  42. 4 3 2 5 1 enqueue dequeue QUEUE (fila) FIFO:

    First-in, First-out
  43. QUEUE (no mundo enterprise)

  44. 1002 1001 QUEUE (no mundo enterprise)

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

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

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

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

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

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

  51. ONDE?

  52. no BANCO, ué!

  53. tabela

  54. db servidor navegador

  55. db servidor navegador fila

  56. db servidor navegador fila x

  57. db servidor navegador requisição fila

  58. db servidor navegador requisição INSERT fila

  59. db servidor navegador requisição INSERT fila

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

  61. db servidor navegador requisição INSERT fila

  62. db servidor navegador requisição INSERT fila

  63. e a vantagem?

  64. db servidor requisição INSERT

  65. db servidor requisição INSERT

  66. db servidor requisição INSERT

  67. db servidor requisição INSERT x

  68. INSERT

  69. INSERT

  70. POLLING

  71. como implementa?

  72. precisamos de uma tabela…

  73. describe job_queue;

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

  76. select j.* from job_queue j ;

  77. select j.* from job_queue j ;

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

  79. e de um consumer…

  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;
  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;
  82. create package body PDF_Printer_Pkg as procedure polling() as begin end;

    procedure print_pdf(p_xml varchar2) as begin -- ... end; end;
  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;
  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;
  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;
  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;
  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;
  88. e alguém para fazer o polling…

  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;
  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;
  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;
  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;
  93. INSERT

  94. select j.* from job_queue j ;

  95. select j.* from job_queue j ;

  96. select j.* from job_queue j ;

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

  98. Mas e se…

  99. 9630 9631 QUEUE (fila) producer consumer 9629

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

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

    consume:
 250 itens/min
  102. e agora?

  103. Fácil! 
 levanta outro CONSUMER!

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

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

    consumer consume:
 250 itens/min consume:
 250 itens/min
  106. DISTRIBUTED POLLING

  107. 1000 4 / 250 =

  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;
  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;
  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;
  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;
  112. INSERT

  113. select j.* from job_queue j ;

  114. select j.* from job_queue j ;

  115. select j.* from job_queue j ;

  116. select j.* from job_queue j ;

  117. YES!

  118. Calma aí…

  119. …mas está mais LENTO

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

  121. Vamos entender o que aconteceu…

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

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

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

    NEW thread_1 thread_2
  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';
  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}
  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';
  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}
  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}
  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}
  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
  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
  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
  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
  135. NO!

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

    desenho e implementação *
  137. e agora?

  138. Podemos LOCKAR as linhas

  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;
  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;
  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;
  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;
  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;
  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
  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;
  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
  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
  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;
  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;
  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;
  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;
  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;
  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
  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;
  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;
  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;
  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;
  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;
  159. INSERT

  160. select j.* from job_queue j ;

  161. select j.* from job_queue j ;

  162. select j.* from job_queue j ;

  163. select j.* from job_queue j ;

  164. YES!

  165. Calma aí…

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

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

  168. Vamos entender o que aconteceu…

  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
  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
  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
  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
  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
  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;
  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;
  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;
  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;
  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;
  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;
  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;
  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;
  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;
  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;
  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;
  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;
  186. humm…

  187. e agora?

  188. Pensando…

  189. Que tal tentarmos o SKIP LOCKED?

  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;
  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;
  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
  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
  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;
  195. Qual a diferença?

  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  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
  210. Ou seja…

  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
  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
  213. INSERT

  214. INSERT

  215. INSERT

  216. YES!

  217. Concluindo

  218. single-threaded funciona bem…

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

  220. multi-threaded com FOR UPDATE…

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

  222. multi-threaded com SKIP LOCKED…

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

  224. ESCALABILIDADE

  225. ORACLE AQ (Advanced Queue)

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

  227. OBRIGADO!