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

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!