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
2. ou…
3. Como escalar seu sistema usando FILA EM BANCO
4. nosso problema
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
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!