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

F853760c988228c4a153333407e64f09?s=47 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.

F853760c988228c4a153333407e64f09?s=128

Rafael Ponte

February 12, 2020
Tweet

Transcript

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

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

  6. None
  7. CACHE

  8. CACHE PROCESSAMENTO ASSINCRONO

  9. CACHE BALANCEAMENTO DE CARGA PROCESSAMENTO ASSINCRONO

  10. CACHE BALANCEAMENTO DE CARGA PROCESSAMENTO ASSINCRONO

  11. a idéia é simples…

  12. servidor

  13. db servidor

  14. navegador db servidor

  15. navegador db servidor requisição

  16. navegador db servidor requisição resposta

  17. e funciona muito bem…

  18. db servidor requisição resposta

  19. db servidor requisição resposta

  20. db servidor requisição resposta

  21. db servidor requisição resposta

  22. calma!

  23. navegador db servidor requisição resposta

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

  25. navegador db servidor requisição resposta

  26. navegador db servidor requisição resposta fila

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

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

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

  30. mas qual broker?

  31. None
  32. None
  33. None
  34. None
  35. mas qual o custo?

  36. mas qual o custo? Segurança Backup Replicação Monitoramento Upgrade Recovery

    Fail-over Alta-disponibilidade Hardware
  37. E agora?

  38. Banco de dados

  39. Sua app precisa dessa escala TODA?

  40. Como escalar seu sistema usando FILA EM BANCO

  41. Rafael Ponte @rponte

  42. Fortaleza - Terra do Sol

  43. None
  44. FILA EM BANCO escalando sua aplicação com banco relacional

  45. nosso problema

  46. None
  47. None
  48. None
  49. None
  50. create package body PDF_Printer_Pkg as procedure print_pdf(p_xml varchar2) as begin

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

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

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

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

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

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

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

    -- valida XML -- processa XML e carrega dados end; end;
  58. 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;
  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 -- faz upload para disco remoto end; end;
  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 -- envia por email end; end;
  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;
  62. db

  63. db servidor

  64. db servidor navegador

  65. db servidor navegador requisição

  66. db servidor navegador requisição

  67. db servidor navegador requisição

  68. db servidor navegador requisição

  69. db servidor requisição

  70. db servidor requisição

  71. db servidor requisição

  72. db servidor requisição servidor servidor

  73. db servidor requisição servidor servidor

  74. None
  75. vamos rodar em BACKGROUND !!!

  76. COMO?

  77. a gente joga numa FILA !

  78. 4 3 2 QUEUE (fila)

  79. 4 3 2 5 QUEUE (fila)

  80. 4 3 2 5 enqueue QUEUE (fila)

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

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

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

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

  85. 1002 1001 QUEUE (no mundo enterprise)

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

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

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

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

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

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

  92. ONDE?

  93. no BANCO, ué!

  94. tabela

  95. db servidor navegador

  96. db servidor navegador fila

  97. db servidor navegador fila x

  98. db servidor navegador requisição fila

  99. db servidor navegador requisição INSERT fila

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

  101. db servidor navegador requisição INSERT fila

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

  103. db servidor navegador requisição INSERT fila

  104. db servidor navegador requisição INSERT fila

  105. e a vantagem?

  106. db servidor requisição INSERT

  107. db servidor requisição INSERT

  108. db servidor requisição INSERT

  109. db servidor requisição INSERT x

  110. INSERT

  111. INSERT

  112. POLLING

  113. como implementa?

  114. precisamos de uma tabela…

  115. describe job_queue;

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

  118. select j.* from job_queue j ;

  119. select j.* from job_queue j ;

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

  121. e de um consumer…

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

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

  132. 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;
  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;
  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;
  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;
  136. INSERT

  137. select j.* from job_queue j ;

  138. select j.* from job_queue j ;

  139. select j.* from job_queue j ;

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

  141. Mas e se…

  142. 9630 9631 QUEUE (fila) producer consumer 9629

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

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

    consume: 250 itens/min
  145. e agora?

  146. Fácil! levanta outro CONSUMER!

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

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

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

  150. 1000

  151. 1000 / 250

  152. 1000 4 / 250 =

  153. 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;
  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;
  155. 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;
  156. 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;
  157. INSERT

  158. select j.* from job_queue j ;

  159. select j.* from job_queue j ;

  160. select j.* from job_queue j ;

  161. select j.* from job_queue j ;

  162. YES!

  163. Calma aí…

  164. …mas está mais LENTO

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

  166. Vamos entender o que aconteceu…

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

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

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

    NEW thread_1 thread_2
  170. 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';
  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'; {id=1001}
  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} select j.* from job_queue j where j.status = 'NEW';
  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'; {id=1001}
  174. 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}
  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; commit; select j.* from job_queue j where j.status = 'NEW'; {id=1001}
  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} 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
  177. 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
  178. 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
  179. 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
  180. NO!

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

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

  183. Podemos LOCKAR as linhas

  184. 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;
  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;
  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 commit; end loop; end;
  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;
  188. 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;
  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; locka TODOS os registros
  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; begin for job in jobs_cursor loop -- processa item commit; 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; begin for job in jobs_cursor loop -- processa item commit; end loop; end; SELECT…FOR UPDATE não suporta FETCHING-ACROSS-COMMITS
  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; Vai sair muito cedo do loop
  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;
  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;
  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; 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;
  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;
  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;
  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; lock ocorre no OPENING
  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;
  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;
  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;
  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;
  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;
  204. INSERT

  205. select j.* from job_queue j ;

  206. select j.* from job_queue j ;

  207. select j.* from job_queue j ;

  208. select j.* from job_queue j ;

  209. YES!

  210. Calma aí…

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

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

  213. Vamos entender o que aconteceu…

  214. 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
  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
  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
  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
  218. 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
  219. 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;
  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;
  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 select j.* from job_queue j where j.status = 'NEW' and rownum = 1 for update;
  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;
  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;
  224. 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;
  225. 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;
  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;
  227. 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;
  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 FINISHED thread_2 1004 NEW select j.* from job_queue j where j.status = 'NEW' and rownum = 1 for update;
  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 FINISHED thread_1 select j.* from job_queue j where j.status = 'NEW' and rownum = 1 for update;
  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;
  231. humm…

  232. e agora?

  233. Pensando…

  234. Que tal tentarmos o SKIP LOCKED?

  235. 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;
  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 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;
  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; lock ocorre no FETCHING
  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
  239. 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;
  240. Qual a diferença?

  241. 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
  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
  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
  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
  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
  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
  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
  248. 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
  249. 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
  250. 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
  251. 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
  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
  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
  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
  255. Ou seja…

  256. 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
  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
  258. INSERT

  259. INSERT

  260. INSERT

  261. YES!

  262. Concluindo

  263. single-threaded funciona bem…

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

  265. multi-threaded com FOR UPDATE…

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

  267. multi-threaded com SKIP LOCKED…

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

  269. ESCALABILIDADE

  270. ORACLE AQ (Advanced Queue)

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

  272. OBRIGADO!