Monthly Archives: May 2016

PL/SQL inside SQL = Read Consistency issue

Português

Today I read a blog post from Martin Widlake’s Yet Another Oracle Blog, where he declare he will be talking in a conference about PL/SQL being called from SQL, and he points the potential issue it could cause in the read consistency model.

Let’s explain: every SQL statement you run against Oracle is read consistent to the point in time it started. If you start a query, say, at 10AM, no matter how much time it takes to run the result will show what you had at 10AM.

But, what if you have PL/SQL function called from this SQL, and inside this function you have another SQL query? Well, in this case, you may have a disruption in the consistency of the main query, because the query inside the function will have a different start time. I don’t want to explain it here in much details, because there are plenty examples in other blog posts. You can follow this All Things Oracle post to read more and reproduce the behaviour.

In this other link from Oracle Base, the writer shows a solution enabling flashback before running the main SQL.

I would like to show another possible solution using the SERIALIZABLE transaction isolation level.

As the documentation stands, Serializable transactions see only those changes that were committed at the time the transaction began. I have to mention here that both solutions, using either SERIALIZABLE or FLASHBACK enabled, must be issued before a transaction begins, i.e., you can’t do this in the middle of a transaction.

I will base use the same code as in the All Things Oracle post, just changing the isolation level to avoid the unwanted behaviour.

So, first I set up the objects for the test:

create table app_users (
  user_id     integer not null primary key,
  given_name  varchar2(100) not null,
  family_name varchar2(100) not null
);
insert into app_users values (1, 'Chris', 'Saxon');
commit;
create or replace function get_full_name ( user_id app_users.user_id%type )
  return varchar2 as
  full_name varchar2(200);
begin
  dbms_lock.sleep(10);
  select given_name || ' ' || family_name
  into   full_name
  from   app_users usr
  where  usr.user_id = get_full_name.user_id;
  return full_name;
end get_full_name; 
/

Then, I will start two connections with the same user that owns the objects I just created.

In the first connection, I issue the main query that used the function:

-- SESSION 1
select get_full_name ( user_id ) name_fn, 
       given_name || ' ' || family_name name_sql
from   app_users;

While this query is “running” (actually it is waiting because of the 10 seconds sleep inside the function), you run in the second session an update and commit (I am inserting an extract from current_timestamp to guarantee that every run would change the name):

-- SESSION 2 (execute while query in session 1 is running)
update app_users
set given_name = 'New name' || EXTRACT(second FROM current_timestamp)
where user_id = 1;
commit;

As the original blog shows, you would end up with the following strange result, due to the different consistent moments of the main query and the query inside the function:

NAME_FN                   NAME_SQL
------------------------- -------------------------
New name43.813276 Saxon   Chris Saxon

But, if you change the isolation level to SERIALIZABLE before running the query, you don’t have this difference:

-- SESSION 1
commit;
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;
select get_full_name ( user_id ) name_fn, 
       given_name || ' ' || family_name name_sql
from   app_users;
NAME_FN                   NAME_SQL
------------------------- -------------------------
New name43.813276 Saxon   New name43.813276 Saxon
-- SESSION 2 (execute while query in session 1 is running)
update app_users
set given_name = 'New name' || EXTRACT(second FROM current_timestamp)
where user_id = 1;
commit;

That’s it :-).

If you use PL/SQL function calls from a SQL query, and inside this function you have SQL queries, then you must be aware that you may have read consistent issues in your results. It’s up to you to decide if this is acceptable in your case, or if you need to do something to overcome this issue.

Regards.


 

PL/SQL dentro de SQL = problema de Leitura Consistente

Hoje eu li um post em Martin Widlake’s Yet Another Oracle Blog, onde ele informa que vai falar em uma conferência sobre chamadas PL/SQL de dentro do SQL, e ele aponta o problema que isto pode causar no modelo de leitura consistente.
Explicando: cada comando SQL que você executa no Oracle é consistente ao momento em que ele começa. Se você executa uma query, digamos, às 10h, o resultado mostrará o que você tinha exatamente às 10h, não importa o tempo que ela demore para executar.

Mas, e se você usar uma função PL/SQL nesta query, e dentro da função você tiver outro comando SQL? Bem, neste caso, você pode ter uma quebra na consistência de leitura da query principal, porque a query dentro da função terá um momento de início diferente. Eu não quer explicar isso aqui em muitos detalhes, porque há muito exemplos em outros blogs. Você pode seguir este post em All Things Oracle para ler mais e reproduzir o comportamento.

Neste outro link do Oracle Base, o escritor mostra uma solução ligando o flashback antes de executar a query principal.

Eu gostaria de mostrar outra possível solução usando o modo SERIALIZABLE de isolamento de transação.

Como a documentação diz, transações serializáveis enxergam somente as alterações que estavam “commitadas” (perdoem-me o neologismo) no momento que a transação começou. Eu tenho que mencionar aqui que ambas as soluções, usando SERIALIZABLE ou FLASHBACK, precisam ser executadas antes do início da transação, isto é, você não pode fazer isso no meio de uma transação.

Vou utilizar o mesmo código em All Things Oracle, somente mudando o modo de isolamento pra evitar o comportamento indesejado.

Então, primeiro vou configurar os objetos para o teste:

create table app_users (
  user_id     integer not null primary key,
  given_name  varchar2(100) not null,
  family_name varchar2(100) not null
);
insert into app_users values (1, 'Chris', 'Saxon');
commit;
create or replace function get_full_name ( user_id app_users.user_id%type )
  return varchar2 as
  full_name varchar2(200);
begin
  dbms_lock.sleep(10);
  select given_name || ' ' || family_name
  into   full_name
  from   app_users usr
  where  usr.user_id = get_full_name.user_id;
  return full_name;
end get_full_name; 
/

Agora, vou iniciar duas conexões com o mesmo usuário dono dos objetos que criei.

Na primeira conexão, executo a query principal que usa a função:

-- SESSION 1
select get_full_name ( user_id ) name_fn, 
       given_name || ' ' || family_name name_sql
from   app_users;

Enquanto a query está “rodando” (na verdade está esperando por causa do sleep de 10 segundos dentro da função), você executa na segunda sessão um update e commit (estou inserindo um extract do current_timestamp pra garantir que cada execução vai mudar o nome):

-- SESSION 2 (execute enquanto a query na sessão 1 está rodando)
update app_users
set given_name = 'New name' || EXTRACT(second FROM current_timestamp)
where user_id = 1;
commit;

Como o blog original diz, você vai receber o seguinte resultado estranho, por causa da diferença de momento de consistência entre a query principal e a query de dentro da função:

NAME_FN                   NAME_SQL
------------------------- -------------------------
New name43.813276 Saxon   Chris Saxon

Mas, se você você mudar o isolamento para SERIALIZABLE antes de executar a query, você não terá esta diferença:

-- SESSION 1
commit;
ALTER SESSION SET ISOLATION_LEVEL = SERIALIZABLE;
select get_full_name ( user_id ) name_fn, 
       given_name || ' ' || family_name name_sql
from   app_users;
NAME_FN                   NAME_SQL
------------------------- -------------------------
New name43.813276 Saxon   New name43.813276 Saxon
-- SESSION 2 (execute enquanto a query na sessão 1 está rodando)
update app_users
set given_name = 'New name' || EXTRACT(second FROM current_timestamp)
where user_id = 1;
commit;

É isso :-).

Se você chama uma função PL/SQL de dentro de uma query SQL, e nesta função você tem outras queries SQL, então precisa saber que pode ter problemas de leitura consistente. Você decide se esta situação é aceitável no seu caso, ou se precisa fazer algo para superar este problema.

Um abraço.

Advertisements

My first blog post

Português

Ok, let’s go.

This is the first time I really started a blog.

It’s a thing that I always thought of, but never came up into life up to now. There are many reasons for it.

Of course, the professional versus personal life balance is one of them. I always spent many time at work, not only the “regular” one, but also teaching classes at night, studying and sometimes doing some other stuff.

I always followed a lot of great bloggers, there are plenty of wonderful content in the Internet for you to browse and learn. I always concerned if I would have something relevant and not already covered by someone else. I didn’t want to just copy-paste things. This is another reason for my “procrastination” to start this blog.

But, I know we always have something to say 🙂 . And even if it’s about something a lot of other people have already talked about, there is always room for another perspective.

The main purposes of this blog are: first, to help community (in special my students and former-students, I would like to encourage them to participate asking questions and posting comments); second, to “put ideas in the paper”, which we all know help to organize thoughts and save them in an organized way; and last but not least, to be known by the Oracle community. If I am able to do the first two, obviously the latter will be accomplished as well.

One last thing: forgive me if you find something wrong with my English. I’m Brazilian, right? 🙂

I hope you enjoy. Please leave a comment with you thoughts.


Meu primeiro post

Ok, vamos lá.
É a primeira vez na vida que eu inicio um blog.

É uma coisa na qual eu sempre pensei, mas por diversas razões nunca aconteceu.

Claro, a preocupação com o equilíbrio entre vida pessoal e profissional é uma delas. Sempre gastei muito tempo no trabalho, não somente o “regular”, mas também dando aulas à noite, estudando e esporadicamente realizando outros trabalhos.

Sempre segui vários grandes blogueiros, há uma enorme quantidade de conteúdo maravilhoso na Internet para você pesquisar e aprender. Eu sempre me preocupei se teria algo relevante e não já coberto por alguém. Eu não queria apenas copiar e colar conteúdo. Este é um outro motivo para minha “procrastinação” para iniciar este blog.

Mas, eu sei que sempre temos algo a falar 🙂 . E mesmo que um seja um assunto já comentado por várias pessoas, sempre há espaço para uma outra perspectiva.

Os principais objetivos deste blog são: primeiro, ajudar a comunidade (em especial meus alunos e ex-alunos, eu quero encorajá-los a participar fazendo perguntas e comentários); segundo, para “colocar as ideias no papel”, o que sabemos que ajuda a organizar os pensamentos e salvá-los de maneira organizada; e por último mas não menos importante, para ser mais conhecido pela comunidade Oracle. Se eu conseguir atingir os dois primeiros, obviamente o terceiro será atingido também.

Uma última coisa: me perdoe se encontrar algum erro no meu inglês. Sou brasileiro, certo? 🙂

Espero que gostem. Por favor deixei um comentário com suas impressões.