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

Tweet

Tweet

Slide 1

Slide 1 text

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

Slide 2

Slide 2 text

No content

Slide 3

Slide 3 text

No content

Slide 4

Slide 4 text

No content

Slide 5

Slide 5 text

Hype-Driven Development https://blog.daftcode.pl/hype-driven-development-3469fc2e9b22

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

CACHE

Slide 8

Slide 8 text

CACHE PROCESSAMENTO ASSINCRONO

Slide 9

Slide 9 text

CACHE BALANCEAMENTO DE CARGA PROCESSAMENTO ASSINCRONO

Slide 10

Slide 10 text

CACHE BALANCEAMENTO DE CARGA PROCESSAMENTO ASSINCRONO

Slide 11

Slide 11 text

a idéia é simples…

Slide 12

Slide 12 text

servidor

Slide 13

Slide 13 text

db servidor

Slide 14

Slide 14 text

navegador db servidor

Slide 15

Slide 15 text

navegador db servidor requisição

Slide 16

Slide 16 text

navegador db servidor requisição resposta

Slide 17

Slide 17 text

e funciona muito bem…

Slide 18

Slide 18 text

db servidor requisição resposta

Slide 19

Slide 19 text

db servidor requisição resposta

Slide 20

Slide 20 text

db servidor requisição resposta

Slide 21

Slide 21 text

db servidor requisição resposta

Slide 22

Slide 22 text

calma!

Slide 23

Slide 23 text

navegador db servidor requisição resposta

Slide 24

Slide 24 text

navegador db servidor requisição resposta ???

Slide 25

Slide 25 text

navegador db servidor requisição resposta

Slide 26

Slide 26 text

navegador db servidor requisição resposta ﬁla

Slide 27

Slide 27 text

navegador db servidor requisição resposta ﬁla consumidor

Slide 28

Slide 28 text

navegador db servidor requisição resposta ﬁla consumidor

Slide 29

Slide 29 text

navegador db servidor requisição resposta ﬁla consumidor

Slide 30

Slide 30 text

mas qual broker?

Slide 31

Slide 31 text

No content

Slide 32

Slide 32 text

No content

Slide 33

Slide 33 text

No content

Slide 34

Slide 34 text

No content

Slide 35

Slide 35 text

mas qual o custo?

Slide 36

Slide 36 text

mas qual o custo? Segurança Backup Replicação Monitoramento Upgrade Recovery Fail-over Alta-disponibilidade Hardware

Slide 37

Slide 37 text

E agora?

Slide 38

Slide 38 text

Banco de dados

Slide 39

Slide 39 text

Sua app precisa dessa escala TODA?

Slide 40

Slide 40 text

Como escalar seu sistema usando FILA EM BANCO

Slide 41

Slide 41 text

Rafael Ponte @rponte

Slide 42

Slide 42 text

No content

Slide 43

Slide 43 text

Fortaleza - Terra do Sol

Slide 44

Slide 44 text

No content

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

nosso problema

Slide 47

Slide 47 text

No content

Slide 48

Slide 48 text

No content

Slide 49

Slide 49 text

No content

Slide 50

Slide 50 text

No content

Slide 51

Slide 51 text

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

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

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

Slide 56

Slide 56 text

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

Slide 57

Slide 57 text

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

Slide 58

Slide 58 text

create package body PDF_Printer_Pkg as procedure print_pdf(p_xml varchar2) as begin -- valida XML -- processa XML e carrega dados end; end;

Slide 59

Slide 59 text

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;

Slide 60

Slide 60 text

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;

Slide 61

Slide 61 text

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;

Slide 62

Slide 62 text

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;

Slide 63

Slide 63 text

db

Slide 64

Slide 64 text

db servidor

Slide 65

Slide 65 text

db servidor navegador

Slide 66

Slide 66 text

db servidor navegador requisição

Slide 67

Slide 67 text

db servidor navegador requisição

Slide 68

Slide 68 text

db servidor navegador requisição

Slide 69

Slide 69 text

db servidor navegador requisição

Slide 70

Slide 70 text

db servidor requisição

Slide 71

Slide 71 text

db servidor requisição

Slide 72

Slide 72 text

db servidor requisição

Slide 73

Slide 73 text

db servidor requisição servidor servidor

Slide 74

Slide 74 text

db servidor requisição servidor servidor

Slide 75

Slide 75 text

No content

Slide 76

Slide 76 text

vamos rodar em BACKGROUND !!!

Slide 77

Slide 77 text

COMO?

Slide 78

Slide 78 text

a gente joga numa FILA !

Slide 79

Slide 79 text

4 3 2 QUEUE (ﬁla)

Slide 80

Slide 80 text

4 3 2 5 QUEUE (ﬁla)

Slide 81

Slide 81 text

4 3 2 5 enqueue QUEUE (ﬁla)

Slide 82

Slide 82 text

4 3 2 5 1 enqueue QUEUE (ﬁla)

Slide 83

Slide 83 text

4 3 2 5 1 enqueue dequeue QUEUE (ﬁla)

Slide 84

Slide 84 text

4 3 2 5 1 enqueue dequeue QUEUE (ﬁla) FIFO: First-in, First-out

Slide 85

Slide 85 text

QUEUE (no mundo enterprise)

Slide 86

Slide 86 text

1002 1001 QUEUE (no mundo enterprise)

Slide 87

Slide 87 text

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

Slide 88

Slide 88 text

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

Slide 89

Slide 89 text

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

Slide 90

Slide 90 text

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

Slide 91

Slide 91 text

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

Slide 92

Slide 92 text

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

Slide 93

Slide 93 text

ONDE?

Slide 94

Slide 94 text

no BANCO, ué!

Slide 95

Slide 95 text

tabela

Slide 96

Slide 96 text

db servidor navegador

Slide 97

Slide 97 text

db servidor navegador ﬁla

Slide 98

Slide 98 text

db servidor navegador ﬁla x

Slide 99

Slide 99 text

db servidor navegador requisição ﬁla

Slide 100

Slide 100 text

db servidor navegador requisição INSERT ﬁla

Slide 101

Slide 101 text

db servidor navegador requisição INSERT ﬁla resposta

Slide 102

Slide 102 text

db servidor navegador requisição INSERT ﬁla

Slide 103

Slide 103 text

db servidor navegador requisição INSERT quem executa? ﬁla

Slide 104

Slide 104 text

db servidor navegador requisição INSERT ﬁla

Slide 105

Slide 105 text

db servidor navegador requisição INSERT ﬁla

Slide 106

Slide 106 text

e a vantagem?

Slide 107

Slide 107 text

db servidor requisição INSERT

Slide 108

Slide 108 text

db servidor requisição INSERT

Slide 109

Slide 109 text

db servidor requisição INSERT

Slide 110

Slide 110 text

db servidor requisição INSERT x

Slide 111

Slide 111 text

INSERT

Slide 112

Slide 112 text

INSERT

Slide 113

Slide 113 text

POLLING

Slide 114

Slide 114 text

como implementa?

Slide 115

Slide 115 text

precisamos de uma tabela…

Slide 116

Slide 116 text

describe job_queue;

Slide 117

Slide 117 text

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;

Slide 118

Slide 118 text

select j.* from job_queue j ;

Slide 119

Slide 119 text

select j.* from job_queue j ;

Slide 120

Slide 120 text

select j.* from job_queue j ;

Slide 121

Slide 121 text

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

Slide 122

Slide 122 text

e de um consumer…

Slide 123

Slide 123 text

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;

Slide 124

Slide 124 text

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;

Slide 125

Slide 125 text

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

Slide 126

Slide 126 text

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;

Slide 127

Slide 127 text

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;

Slide 128

Slide 128 text

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;

Slide 129

Slide 129 text

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;

Slide 130

Slide 130 text

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;

Slide 131

Slide 131 text

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;

Slide 132

Slide 132 text

e alguém para fazer o polling…

Slide 133

Slide 133 text

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;

Slide 134

Slide 134 text

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;

Slide 135

Slide 135 text

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;

Slide 136

Slide 136 text

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;

Slide 137

Slide 137 text

INSERT

Slide 138

Slide 138 text

select j.* from job_queue j ;

Slide 139

Slide 139 text

select j.* from job_queue j ;

Slide 140

Slide 140 text

select j.* from job_queue j ;

Slide 141

Slide 141 text

select j.* from job_queue j ; Uhuuull!!

Slide 142

Slide 142 text

Mas e se…

Slide 143

Slide 143 text

9630 9631 QUEUE (ﬁla) producer consumer 9629

Slide 144

Slide 144 text

9630 9631 QUEUE (ﬁla) producer consumer 9629 produz: 1000 itens/min

Slide 145

Slide 145 text

9630 9631 QUEUE (ﬁla) producer consumer 9629 produz: 1000 itens/min consume: 250 itens/min

Slide 146

Slide 146 text

e agora?

Slide 147

Slide 147 text

Fácil! levanta outro CONSUMER!

Slide 148

Slide 148 text

9630 9631 QUEUE (ﬁla) producer consumer 9629 produz: 1000 itens/min

Slide 149

Slide 149 text

9630 9631 QUEUE (ﬁla) producer consumer 9629 produz: 1000 itens/min consumer consume: 250 itens/min consume: 250 itens/min

Slide 150

Slide 150 text

DISTRIBUTED POLLING

Slide 151

Slide 151 text

1000

Slide 152

Slide 152 text

1000 / 250

Slide 153

Slide 153 text

1000 4 / 250 =

Slide 154

Slide 154 text

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;

Slide 155

Slide 155 text

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;

Slide 156

Slide 156 text

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;

Slide 157

Slide 157 text

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;

Slide 158

Slide 158 text

INSERT

Slide 159

Slide 159 text

select j.* from job_queue j ;

Slide 160

Slide 160 text

select j.* from job_queue j ;

Slide 161

Slide 161 text

select j.* from job_queue j ;

Slide 162

Slide 162 text

select j.* from job_queue j ;

Slide 163

Slide 163 text

YES!

Slide 164

Slide 164 text

Calma aí…

Slide 165

Slide 165 text

…mas está mais LENTO

Slide 166

Slide 166 text

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

Slide 167

Slide 167 text

Vamos entender o que aconteceu…

Slide 168

Slide 168 text

ID STATUS THREAD_NAME 1001 NEW 1002 NEW 1003 NEW 1004 NEW

Slide 169

Slide 169 text

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

Slide 170

Slide 170 text

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

Slide 171

Slide 171 text

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

Slide 172

Slide 172 text

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}

Slide 173

Slide 173 text

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

Slide 174

Slide 174 text

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}

Slide 175

Slide 175 text

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}

Slide 176

Slide 176 text

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}

Slide 177

Slide 177 text

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

Slide 178

Slide 178 text

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

Slide 179

Slide 179 text

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

Slide 180

Slide 180 text

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

Slide 181

Slide 181 text

NO!

Slide 182

Slide 182 text

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

Slide 183

Slide 183 text

e agora?

Slide 184

Slide 184 text

Podemos LOCKAR as linhas

Slide 185

Slide 185 text

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;

Slide 186

Slide 186 text

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;

Slide 187

Slide 187 text

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;

Slide 188

Slide 188 text

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;

Slide 189

Slide 189 text

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;

Slide 190

Slide 190 text

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

Slide 191

Slide 191 text

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;

Slide 192

Slide 192 text

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

Slide 193

Slide 193 text

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

Slide 194

Slide 194 text

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;

Slide 195

Slide 195 text

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;

Slide 196

Slide 196 text

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;

Slide 197

Slide 197 text

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;

Slide 198

Slide 198 text

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;

Slide 199

Slide 199 text

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

Slide 200

Slide 200 text

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;

Slide 201

Slide 201 text

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;

Slide 202

Slide 202 text

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;

Slide 203

Slide 203 text

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;

Slide 204

Slide 204 text

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;

Slide 205

Slide 205 text

INSERT

Slide 206

Slide 206 text

select j.* from job_queue j ;

Slide 207

Slide 207 text

select j.* from job_queue j ;

Slide 208

Slide 208 text

select j.* from job_queue j ;

Slide 209

Slide 209 text

select j.* from job_queue j ;

Slide 210

Slide 210 text

YES!

Slide 211

Slide 211 text

Calma aí…

Slide 212

Slide 212 text

…não há duplicação

Slide 213

Slide 213 text

…não há duplicação …mas ainda não está rápido o suﬁciente!

Slide 214

Slide 214 text

Vamos entender o que aconteceu…

Slide 215

Slide 215 text

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

Slide 216

Slide 216 text

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

Slide 217

Slide 217 text

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

Slide 218

Slide 218 text

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

Slide 219

Slide 219 text

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

Slide 220

Slide 220 text

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;

Slide 221

Slide 221 text

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;

Slide 222

Slide 222 text

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;

Slide 223

Slide 223 text

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;

Slide 224

Slide 224 text

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;

Slide 225

Slide 225 text

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;

Slide 226

Slide 226 text

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;

Slide 227

Slide 227 text

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;

Slide 228

Slide 228 text

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;

Slide 229

Slide 229 text

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;

Slide 230

Slide 230 text

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;

Slide 231

Slide 231 text

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;

Slide 232

Slide 232 text

humm…

Slide 233

Slide 233 text

e agora?

Slide 234

Slide 234 text

Pensando…

Slide 235

Slide 235 text

Que tal tentarmos o SKIP LOCKED?

Slide 236

Slide 236 text

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;

Slide 237

Slide 237 text

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;

Slide 238

Slide 238 text

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

Slide 239

Slide 239 text

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

Slide 240

Slide 240 text

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;

Slide 241

Slide 241 text

Qual a diferença?

Slide 242

Slide 242 text

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

Slide 243

Slide 243 text

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

Slide 244

Slide 244 text

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

Slide 245

Slide 245 text

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

Slide 246

Slide 246 text

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

Slide 247

Slide 247 text

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

Slide 248

Slide 248 text

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

Slide 249

Slide 249 text

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

Slide 250

Slide 250 text

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

Slide 251

Slide 251 text

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

Slide 252

Slide 252 text

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

Slide 253

Slide 253 text

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

Slide 254

Slide 254 text

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

Slide 255

Slide 255 text

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

Slide 256

Slide 256 text

Ou seja…

Slide 257

Slide 257 text

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

Slide 258

Slide 258 text

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

Slide 259

Slide 259 text

INSERT

Slide 260

Slide 260 text

INSERT

Slide 261

Slide 261 text

INSERT

Slide 262

Slide 262 text

YES!

Slide 263

Slide 263 text

Concluindo

Slide 264

Slide 264 text

single-threaded funciona bem…

Slide 265

Slide 265 text

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

Slide 266

Slide 266 text

multi-threaded com FOR UPDATE…

Slide 267

Slide 267 text

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

Slide 268

Slide 268 text

multi-threaded com SKIP LOCKED…

Slide 269

Slide 269 text

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

Slide 270

Slide 270 text

ESCALABILIDADE

Slide 271

Slide 271 text

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

Slide 272

Slide 272 text

ORACLE AQ (Advanced Queue)

Slide 273

Slide 273 text

No content

Slide 274

Slide 274 text

OBRIGADO!

Slide 275

Slide 275 text

@rponte Rafael Ponte