Tweet
Tweet

Slide 1

Slide 1 text

10 features do Oracle que você não conhecia #2 SKIP LOCKED TechDay 2019
 Rafael Ponte - @rponte

Slide 2

Slide 2 text

ou…

Slide 3

Slide 3 text

Como escalar seu sistema usando FILA EM BANCO

Slide 4

Slide 4 text

nosso problema

Slide 5

Slide 5 text

No content

Slide 6

Slide 6 text

No content

Slide 7

Slide 7 text

No content

Slide 8

Slide 8 text

No content

Slide 9

Slide 9 text

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

Slide 10

Slide 10 text

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

Slide 11

Slide 11 text

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

Slide 12

Slide 12 text

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

Slide 13

Slide 13 text

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

Slide 14

Slide 14 text

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

Slide 15

Slide 15 text

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

Slide 16

Slide 16 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 17

Slide 17 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 18

Slide 18 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 19

Slide 19 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 20

Slide 20 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 21

Slide 21 text

db

Slide 22

Slide 22 text

db servidor

Slide 23

Slide 23 text

db servidor navegador

Slide 24

Slide 24 text

db servidor navegador requisição

Slide 25

Slide 25 text

db servidor navegador requisição

Slide 26

Slide 26 text

db servidor navegador requisição

Slide 27

Slide 27 text

db servidor navegador requisição

Slide 28

Slide 28 text

db servidor requisição

Slide 29

Slide 29 text

db servidor requisição

Slide 30

Slide 30 text

db servidor requisição

Slide 31

Slide 31 text

db servidor requisição servidor servidor

Slide 32

Slide 32 text

db servidor requisição servidor servidor

Slide 33

Slide 33 text

No content

Slide 34

Slide 34 text

vamos rodar em 
 BACKGROUND !!!

Slide 35

Slide 35 text

COMO?

Slide 36

Slide 36 text

a gente joga numa FILA !

Slide 37

Slide 37 text

4 3 2 QUEUE (fila)

Slide 38

Slide 38 text

4 3 2 5 QUEUE (fila)

Slide 39

Slide 39 text

4 3 2 5 enqueue QUEUE (fila)

Slide 40

Slide 40 text

4 3 2 5 1 enqueue QUEUE (fila)

Slide 41

Slide 41 text

4 3 2 5 1 enqueue dequeue QUEUE (fila)

Slide 42

Slide 42 text

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

Slide 43

Slide 43 text

QUEUE (no mundo enterprise)

Slide 44

Slide 44 text

1002 1001 QUEUE (no mundo enterprise)

Slide 45

Slide 45 text

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

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

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

Slide 48

Slide 48 text

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

Slide 49

Slide 49 text

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

Slide 50

Slide 50 text

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

Slide 51

Slide 51 text

ONDE?

Slide 52

Slide 52 text

no BANCO, ué!

Slide 53

Slide 53 text

tabela

Slide 54

Slide 54 text

db servidor navegador

Slide 55

Slide 55 text

db servidor navegador fila

Slide 56

Slide 56 text

db servidor navegador fila x

Slide 57

Slide 57 text

db servidor navegador requisição fila

Slide 58

Slide 58 text

db servidor navegador requisição INSERT fila

Slide 59

Slide 59 text

db servidor navegador requisição INSERT fila

Slide 60

Slide 60 text

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

Slide 61

Slide 61 text

db servidor navegador requisição INSERT fila

Slide 62

Slide 62 text

db servidor navegador requisição INSERT fila

Slide 63

Slide 63 text

e a vantagem?

Slide 64

Slide 64 text

db servidor requisição INSERT

Slide 65

Slide 65 text

db servidor requisição INSERT

Slide 66

Slide 66 text

db servidor requisição INSERT

Slide 67

Slide 67 text

db servidor requisição INSERT x

Slide 68

Slide 68 text

INSERT

Slide 69

Slide 69 text

INSERT

Slide 70

Slide 70 text

POLLING

Slide 71

Slide 71 text

como implementa?

Slide 72

Slide 72 text

precisamos de uma tabela…

Slide 73

Slide 73 text

describe job_queue;

Slide 74

Slide 74 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 75

Slide 75 text

select j.* from job_queue j ;

Slide 76

Slide 76 text

select j.* from job_queue j ;

Slide 77

Slide 77 text

select j.* from job_queue j ;

Slide 78

Slide 78 text

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

Slide 79

Slide 79 text

e de um consumer…

Slide 80

Slide 80 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 81

Slide 81 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 82

Slide 82 text

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

Slide 83

Slide 83 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 84

Slide 84 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 85

Slide 85 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 86

Slide 86 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 87

Slide 87 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 88

Slide 88 text

e alguém para fazer o polling…

Slide 89

Slide 89 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 90

Slide 90 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 91

Slide 91 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 92

Slide 92 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 93

Slide 93 text

INSERT

Slide 94

Slide 94 text

select j.* from job_queue j ;

Slide 95

Slide 95 text

select j.* from job_queue j ;

Slide 96

Slide 96 text

select j.* from job_queue j ;

Slide 97

Slide 97 text

select j.* from job_queue j ; Uhuuull!!

Slide 98

Slide 98 text

Mas e se…

Slide 99

Slide 99 text

9630 9631 QUEUE (fila) producer consumer 9629

Slide 100

Slide 100 text

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

Slide 101

Slide 101 text

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

Slide 102

Slide 102 text

e agora?

Slide 103

Slide 103 text

Fácil! 
 levanta outro CONSUMER!

Slide 104

Slide 104 text

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

Slide 105

Slide 105 text

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

Slide 106

Slide 106 text

DISTRIBUTED POLLING

Slide 107

Slide 107 text

1000 4 / 250 =

Slide 108

Slide 108 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 109

Slide 109 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 110

Slide 110 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 111

Slide 111 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 112

Slide 112 text

INSERT

Slide 113

Slide 113 text

select j.* from job_queue j ;

Slide 114

Slide 114 text

select j.* from job_queue j ;

Slide 115

Slide 115 text

select j.* from job_queue j ;

Slide 116

Slide 116 text

select j.* from job_queue j ;

Slide 117

Slide 117 text

YES!

Slide 118

Slide 118 text

Calma aí…

Slide 119

Slide 119 text

…mas está mais LENTO

Slide 120

Slide 120 text

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

Slide 121

Slide 121 text

Vamos entender o que aconteceu…

Slide 122

Slide 122 text

ID STATUS THREAD_NAME 1001 NEW 1002 NEW 1003 NEW 1004 NEW

Slide 123

Slide 123 text

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

Slide 124

Slide 124 text

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

Slide 125

Slide 125 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 126

Slide 126 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 127

Slide 127 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 128

Slide 128 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 129

Slide 129 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 130

Slide 130 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 131

Slide 131 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 132

Slide 132 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 133

Slide 133 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 134

Slide 134 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=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

Slide 135

Slide 135 text

NO!

Slide 136

Slide 136 text

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

Slide 137

Slide 137 text

e agora?

Slide 138

Slide 138 text

Podemos LOCKAR as linhas

Slide 139

Slide 139 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 140

Slide 140 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 141

Slide 141 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 142

Slide 142 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 143

Slide 143 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 144

Slide 144 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 145

Slide 145 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 146

Slide 146 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 147

Slide 147 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 148

Slide 148 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 149

Slide 149 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 150

Slide 150 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 151

Slide 151 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 152

Slide 152 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 153

Slide 153 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 154

Slide 154 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 155

Slide 155 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 156

Slide 156 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 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' 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 158

Slide 158 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 159

Slide 159 text

INSERT

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

select j.* from job_queue j ;

Slide 164

Slide 164 text

YES!

Slide 165

Slide 165 text

Calma aí…

Slide 166

Slide 166 text

…não há duplicação

Slide 167

Slide 167 text

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

Slide 168

Slide 168 text

Vamos entender o que aconteceu…

Slide 169

Slide 169 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 170

Slide 170 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 171

Slide 171 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 172

Slide 172 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 173

Slide 173 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 174

Slide 174 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 175

Slide 175 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 176

Slide 176 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 177

Slide 177 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 178

Slide 178 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 179

Slide 179 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 180

Slide 180 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 181

Slide 181 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 182

Slide 182 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 183

Slide 183 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 184

Slide 184 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 185

Slide 185 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 186

Slide 186 text

humm…

Slide 187

Slide 187 text

e agora?

Slide 188

Slide 188 text

Pensando…

Slide 189

Slide 189 text

Que tal tentarmos o SKIP LOCKED?

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

Slide 195 text

Qual a diferença?

Slide 196

Slide 196 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 197

Slide 197 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 198

Slide 198 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 199

Slide 199 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 200

Slide 200 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 201

Slide 201 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 202

Slide 202 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 203

Slide 203 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 204

Slide 204 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 205

Slide 205 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 206

Slide 206 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 207

Slide 207 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 208

Slide 208 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 209

Slide 209 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 210

Slide 210 text

Ou seja…

Slide 211

Slide 211 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 212

Slide 212 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 213

Slide 213 text

INSERT

Slide 214

Slide 214 text

INSERT

Slide 215

Slide 215 text

INSERT

Slide 216

Slide 216 text

YES!

Slide 217

Slide 217 text

Concluindo

Slide 218

Slide 218 text

single-threaded funciona bem…

Slide 219

Slide 219 text

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

Slide 220

Slide 220 text

multi-threaded com FOR UPDATE…

Slide 221

Slide 221 text

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

Slide 222

Slide 222 text

multi-threaded com SKIP LOCKED…

Slide 223

Slide 223 text

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

Slide 224

Slide 224 text

ESCALABILIDADE

Slide 225

Slide 225 text

ORACLE AQ (Advanced Queue)

Slide 226

Slide 226 text

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

Slide 227

Slide 227 text

OBRIGADO!