Category Archives: SQL

A script to DELETE records recursively from all child tables

Português/English

In the last months I’ve been working for a client that has a very complex ER model, with about a thousand tables and several thousand of relationships, in many levels of depth. And there are a lot of relationships that cycles the model. For example, we have some situations like this: table A is parent of B, B is parent of C, C is parent of D, and D is parent of A. It means that table D, which is at the third level of dependency of table A, is at the same time parent of table A. I know it sounds weird, but the situation exists. By the way, the model is being reviewed.

Another issue they have is a lot of “trash” data that should be deleted. Old products for example, that came from another implementation of the application but were not cleaned properly.

So, for example, there is a need for deleting some products, but they have many child records in many tables, and each of these child records has its own child records in other tables, and so on.

No, the foreign keys are not “ON DELETE CASCADE”, and it could be a security risk to change all them just to delete some old products.

Then I was asked to develop a way to delete the unwanted data from a table, and all child records in all related tables recursively.

I browsed the Internet but couldn’t find any good solution. So I developed the procedure I present here.

It makes use of recursiveness to navigate throughout the model. For each child constraint it finds that has child records to delete, the procedure recursively calls itself passing the new found constraint, and so on until no more child data are found.

The statements created to delete records are built in the form ‘WHERE constraint_columns IN (SELECT … FROM parent table WHERE …)‘. So for a table in a deep level, you will see the final statement with several nested subqueries with this IN construction.

At first, the procedure does not delete any record. It just identifies the tables and records that should be deleted, and stores the statements to delete them in a table. After this step is done, it can then run the statements to really delete the data in the right order for not to have “parent records found” errors.

Below I present all the parameters that the procedure can accept. Please note that some of them are not intended to be passed in the execution, but are used instead for the recursive calls. Also note that only three parameters are mandatory and has no default values: p_owner, p_table and p_where:

  • p_owner: the owner of the main table to have records deleted.
  • p_table: the main table to have records deleted.
  • p_constraint: the PK/UK constraint to use as the first level. If NULL, all the PK/UK will be used. This parameter was created more to test the script. In the normal use, you should let it NULL.
  • p_where: the conditions that identifies the records to be deleted. For example ‘STATUS=1’.
  • p_commit: the commit interval (in rows of the main table). The default is 10000.
  • p_mode: the mode of execution (default is ‘GS’), with any combination of:
    • G – generate the statements.
    • S – show the generated statements (you must execute SET SERVEROUTPUT ON before).
    • X – execute the statements.
    • C – commit the deletes.
    • A – all in batch mode (the equivalent of ‘GXC’).
  • p_limit: use it to limit the depth of the search. Just for testing purposes.
  • p_level: (RECURSIVE PARAMETER) the current level of recursiveness.
  • p_parent_owner(RECURSIVE PARAMETER) the parent owner – will be used to build the recursive DELETE statement.
  • p_parent_table(RECURSIVE PARAMETER) the parent table – will be used to build the recursive DELETE statement.
  • p_parent_cols(RECURSIVE PARAMETER) the parent columns – will be used to build the recursive DELETE statement.
  • p_child_cols(RECURSIVE PARAMETER) the child columns – will be used to build the recursive DELETE statement.

In order for the procedure to work, you need first to create the table that will store the statements, and a sequence that will be used to define IDs for each statement (download it here):

create table tmp_delete_cascade_stmt ( id number primary key, lev number, owner varchar2(30), table_name varchar2(30), parent_constraint varchar2(30),  child_constraint varchar2(30), statement clob, rows_deleted number);
create sequence seq_delete_cascade;

If you call the procedure with the ‘G’ mode, then it first empties the table and then generates and store the new statements generated.

When using ‘S’ mode, the procedure shows all the statements generated. Please note you must issue SET SERVEROUTPUT ON in your SQL*Plus session in order to view the results. Alternatively, you could directly query the table TMP_DELETE_CASCADE_STMT to check the statements generated.

The ‘X’ mode executes all the statements, and stores in the table the number of rows each statement deleted. But it DOES NOT COMMIT any change. It is intentional, for you to have the opportunity to check if the deleted data is what you wanted, and only after confirming that you should issue the COMMIT.

If you use the ‘C’ mode (usually together with ‘X’) then the procedure commits the changes without asking for any confirmation. It makes partial commits for each 10000 rows of the main table, or the number you pass in the p_commit parameter.

You can execute the procedure step by step, first executing with ‘GS’ modes to see what is generated, then ‘X’ to execute and then ‘C’ to commit (or just issue the COMMIT by yourself), or you can do it all at once, by passing ‘GSXC’ or just ‘A’.

The procedure code can be downloaded from here.

CAUTION: please be very careful when using this script. As explained, it will DELETE all the dependant rows in all related tables. So, it is preferable to execute it first in mode ‘G’, then ‘X’ and only commit the changes after checking the results carefully. USE AT YOUR OWN RISK. It was not exhaustively tested in all possible situations and may have bugs!

If you find something wrong or have any comments, please comment the post and let me know.

So, let me build a sample model in order to show how the procedure works. My sample creates the main table T_PRODUCT and some others around that. It has the following child tables that could have records deleted when trying to delete from the main:

  • T_PRODUCT_WAREHOUSE: the warehouses that keep each product in stock.
  • T_INVOICE_ITEM: the items of each invoice.
  • T_INVOICE_ITEM_DELIVERY: the delivery of the items invoice (items could be delivered partially).
  • T_PRODUCT: it is a self-relationship to relate a product with other equivalent one.

You can download the sample from here.

-- Generate, execute and show (but don't COMMIT yet) the DELETE statements
begin
	P_DELETE_CASCADE(
		p_owner=>user,
		p_table=>'T_PRODUCT',
		p_where=>'product_id in (1,6)',
		p_mode => 'GX'
		);
end;
/

PL/SQL procedure successfully completed.
-- Verify the statements and the number of rows affected by each one
col statement for a80 word_wrap
select table_name, statement, rows_deleted from tmp_delete_cascade_stmt order by lev desc, id;

TABLE_NAME                          STATEMENT                                                                        ROWS_DELETED
----------------------------------- -------------------------------------------------------------------------------- ------------
T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_INVOICE_ITEM_DELIVERY             DELETE FROM EDUARDO.T_INVOICE_ITEM_DELIVERY WHERE (INVOICE_ID, PRODUCT_ID) in               4
                                    (SELECT
                                    INVOICE_ID, PRODUCT_ID FROM EDUARDO.T_INVOICE_ITEM WHERE
                                    (PRODUCT_ID) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE (PRODUCT_EQUIVALENT) in (SELECT                         1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            3
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       4
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE product_id in (1,6)                                     2

7 rows selected.
-- Check which records remain
select * from T_PRODUCT;

PRODUCT_ID PRODUCT_NAME         PROVIDER_ID PROVIDER_PRODUCT_ID PRODUCT_EQUIVALENT
---------- -------------------- ----------- ------------------- ------------------
         3 Product 3                      1                9992
         4 Product 4                      1                9993
         5 Product 5                      2                9992

select * from T_PRODUCT_WAREHOUSE;

PRODUCT_ID WAREHOUSE_ID PIECES_IN_STOCK
---------- ------------ ---------------
         3            1              11
         4            2               3
         5            2               1

select * from T_INVOICE;

INVOICE_ID INVOICE_DATE
---------- -----------------
         1 26-MAR-2018 12:06
         2 26-MAR-2018 12:06
         3 26-MAR-2018 12:06
         4 26-MAR-2018 12:06

select * from T_INVOICE_ITEM;

INVOICE_ID PRODUCT_ID  QUANTITY
---------- ---------- ---------
         3          4         3
         4          3         5

select * from T_INVOICE_ITEM_DELIVERY;

INVOICE_ID PRODUCT_ID DELIVERY_DATE     QUANTITY_DELIVERED
---------- ---------- ----------------- ------------------
         3          4 26-MAR-2018 12:56                  3

Please note that the Invoices 1 and 2 still exist in the T_INVOICE table, despite there is no related child record in the T_INVOICE_ITEM. This is normal because this rule is not enforced by any constraint, so I must delete them manually.

Also note that the Product 2 was deleted because it was dependant of the Product 1. This is expected and shows the risks of using the script without the proper caution ;-).

After confirming everything is OK, you can COMMIT the changes.

Enjoy!!!!


Um script para apagar dados recursivamente de todas as tabelas filhas

Português/English

Nos últimos meses estou trabalhando em um cliente que tem um model ER bastante complexo, com cerca de mil tabelas e vários milhares de relacionamentos entre elas, em muitos níveis de profundidade. E há vários relacionamentos com referências cíclicas. Por exemplo, a tabela A é pai da B, a B é pai da C, a C é pai da D, e a D é pai da A. Isto significa que a tabela D, que por um relacionamento é bisneta da tabela A, é ao mesmo por outro relacionamento pai da tabela A. Eu sei que isso soa estranho, mas a situação existe. Para conhecimento, o modelo está sendo redesenhado.

Outro problema é que existem muitos dados “inúteis” que deveriam ser apagados. Produtos antigos por exemplo, que vieram de outra implementação da aplicação mas não foram excluídos propriamente.

Então, por exemplo, precisamos apagar alguns produtos, mas eles têm muitos registros filhos em muitas tabelas, e cada um destes filhos tem seus próprios registros filhos em outras tabelas, e assim por diante.

Não, as foreign keys não são “ON DELETE CASCADE”, e seria um risco de segurança alterar todas elas simplesmente para apagar registros antigos.

Então me pediram pra criar uma maneira de excluir os dados desnecessários de uma tabela, e todos os registros dependentes em todas as tabelas filhas, recursivamente.

Eu pesquisei na Internet e não encontrei nenhuma solução satisfatória. Então eu desenvolvi a procedure que apresento aqui.

Ela faz uso de recursividade para navegar através do modelo. Para cada constraint filha que tem registros filhos para apagar, a procedure recursivamente chama ela mesma passando a nova constraint encontrada, e assim por diante até que não encontre mais dados relacionados para apagar.

Os comandos criados para excluir os registros são construídos na forma ‘WHERE constraint_columns IN (SELECT … FROM parent table WHERE …)‘. Então para uma tabela em um nível profundo de dependência, você verá o comando final com vários subqueries aninhadas com esta construção IN.

Inicialmente, a procedure não apaga nenhum registro. Ela apenas identifica as tabelas e registros que devem ser apagados, e registra em uma tabela os comandos necessários para apagá-los. Em seguida os comandos podem ser executados para efetivamente excluir os dados na ordem correta para evitar erros de “registros pais encontrados”.

Abaixo eu apresento todos os parâmetros que a procedure pode aceitar. Note que alguns deles não são foram criados para serem passados na execução , mas nas chamadas recursicas que a própria procedure fará. Note também que somente três parâmetros são obrigatórios e não têm valores defaultp_owner, p_table e p_where:

  • p_owner: o owner da tabela principal que terá os registros excluídos.
  • p_table:a tabela principal que terá os registros excluídos.
  • p_constraint: a constraint PK/UK que será considerada como primeiro nível. Se NULL, todas as PK/UK serão usadas. Este parâmetros foi criado mais para testar o script. No uso normal, você deve deixar NULL.
  • p_where: as condições que identificam os registros a excluir. Por exemplo ‘STATUS=1’.
  • p_commit: o intervalo de commit (em linhas da tabela principal). O default é 10000.
  • p_mode: o modo de execução (default é ‘GS’), com qualquer combinação de:
    • G – gera os comandos.
    • S – mostra os comandos gerados (antes ligue o SET SERVEROUTPUT ON).
    • X – executa os comandos.
    • C – faz o COMMIT.
    • A – tudo em modo batch (o equivalente a ‘GXC’).
  • p_limit: use para limitar a profundidade da pesquisa. Apenas para testes.
  • p_level: (PARÂMETRO RECURSIVO) o nível atual de recursividade.
  • p_parent_owner(PARÂMETRO RECURSIVO) o owner da tabela pai – será usado para construir o DELETE recursivo.
  • p_parent_table(PARÂMETRO RECURSIVO) a tabela pai – será usado para construir o DELETE recursivo.
  • p_parent_cols(PARÂMETRO RECURSIVO) as colunas da tabela pai – será usado para construir o DELETE recursivo.
  • p_child_cols(PARÂMETRO RECURSIVO) as colunas da tabela filha – será usado para construir o DELETE recursivo.

Para que a procedure funcione, você precisa primeiro criar a tabela que vai guardar os comandos, e a sequence que será utilizada para gerar IDs para cada um deles (baixe aqui):

create table tmp_delete_cascade_stmt ( id number primary key, lev number, owner varchar2(30), table_name varchar2(30), parent_constraint varchar2(30),  child_constraint varchar2(30), statement clob, rows_deleted number);
create sequence seq_delete_cascade;

Se você chamar a procedure com o modo ‘G’, então ela primeiro limpa a tabela e depois gera e armazena os comandos gerados.

Ao usar o modo ‘S’, a procedure mostra todos os comandos gerados. Note que você precisa primeiro executar SET SERVEROUTPUT ON em sua sessão SQL*Plus para conseguir ver os resultados. Alternativamente, você pode consultar diretamente a tabela TMP_DELETE_CASCADE_STMT para ver os comandos gerados.

O modo ‘X’ executa os comandos, e salva na tabela o número de linhas que cada um excluiu. Mas ele NÃO FAZ O COMMIT. Isto é intencional, para que você tenha a oportunidade de checar se os dados apagados são os que você esperava, e só fazer o COMMIT depois de confirmar isto.

Se usar o modo ‘C’ mode (normalmente junto com o ‘X’) então a procedure faz o COMMIT das alterações sem pedir qualquer confirmação. Ela faz COMMIT parcial a cada 10000 linhas excluídas da tabela principal, ou o número passado no parâmetro p_commit.

Você pode executar a procedure passo a passo, primeiro com modos ‘GS’ para ver os comandos gerados, depois ‘X’ para executar e ‘C’ para confirmar (ou manualmente executar COMMIT), ou você pode fazer tudo de uma vez passando modos ‘GSXC’ ou apenas ‘A’.

A procedure pode ser baixada daqui.

CUIDADO: seja muito cuidados ao utilizar este script. Como explicado, ele vai APAGAR todas as linhas dependentes em todas as tabelas relacionadas. Então, é melhor executar primeiro com modo ‘G’, depois ‘X’ e só depois de ter certeza que tudo está correto confirmar as alterações. USE POR SUA CONTA E RISCO. Ele não foi testa exaustivamente em todas as situações possíveis e pode ter bugs!

Se você encontrar algo errado or tiver qualquer comentário, por favor comente o post.

Deixe-me então construir um modelo de exemplo para mostrar como a procedure funciona na prática. Meu exemplo cria a tabela principal T_PRODUCT e algumas outras ao redor dela. Ela tem as seguintes tabelas filhas que podem ter registros excluídos quando eu tentar excluir da principal:

  • T_PRODUCT_WAREHOUSE: os armazéns que mantém cada produto em estoque.
  • T_INVOICE_ITEM: os items dos pedidos.
  • T_INVOICE_ITEM_DELIVERY: as entregas dos items (eles podem ser entregues parcialmente).
  • T_PRODUCT: um auto-relacionamento que liga um produto a outro equivalente.

Você pode baixar o modelo exemplo aqui.

-- Generate, execute and show (but don't COMMIT yet) the DELETE statements
begin
	P_DELETE_CASCADE(
		p_owner=>user,
		p_table=>'T_PRODUCT',
		p_where=>'product_id in (1,6)',
		p_mode => 'GX'
		);
end;
/

PL/SQL procedure successfully completed.
-- Verify the statements and the number of rows affected by each one
col statement for a80 word_wrap
select table_name, statement, rows_deleted from tmp_delete_cascade_stmt order by lev desc, id;

TABLE_NAME                          STATEMENT                                                                        ROWS_DELETED
----------------------------------- -------------------------------------------------------------------------------- ------------
T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    (PRODUCT_EQUIVALENT) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_INVOICE_ITEM_DELIVERY             DELETE FROM EDUARDO.T_INVOICE_ITEM_DELIVERY WHERE (INVOICE_ID, PRODUCT_ID) in               4
                                    (SELECT
                                    INVOICE_ID, PRODUCT_ID FROM EDUARDO.T_INVOICE_ITEM WHERE
                                    (PRODUCT_ID) in (SELECT
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))
                                    )

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE (PRODUCT_EQUIVALENT) in (SELECT                         1
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_INVOICE_ITEM                      DELETE FROM EDUARDO.T_INVOICE_ITEM WHERE (PRODUCT_ID) in (SELECT                            3
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT_WAREHOUSE                 DELETE FROM EDUARDO.T_PRODUCT_WAREHOUSE WHERE (PRODUCT_ID) in (SELECT                       4
                                    PRODUCT_ID FROM EDUARDO.T_PRODUCT WHERE
                                    product_id in (1,6))

T_PRODUCT                           DELETE FROM EDUARDO.T_PRODUCT WHERE product_id in (1,6)                                     2

7 rows selected.
-- Check which records remain
select * from T_PRODUCT;

PRODUCT_ID PRODUCT_NAME         PROVIDER_ID PROVIDER_PRODUCT_ID PRODUCT_EQUIVALENT
---------- -------------------- ----------- ------------------- ------------------
         3 Product 3                      1                9992
         4 Product 4                      1                9993
         5 Product 5                      2                9992

select * from T_PRODUCT_WAREHOUSE;

PRODUCT_ID WAREHOUSE_ID PIECES_IN_STOCK
---------- ------------ ---------------
         3            1              11
         4            2               3
         5            2               1

select * from T_INVOICE;

INVOICE_ID INVOICE_DATE
---------- -----------------
         1 26-MAR-2018 12:06
         2 26-MAR-2018 12:06
         3 26-MAR-2018 12:06
         4 26-MAR-2018 12:06

select * from T_INVOICE_ITEM;

INVOICE_ID PRODUCT_ID  QUANTITY
---------- ---------- ---------
         3          4         3
         4          3         5

select * from T_INVOICE_ITEM_DELIVERY;

INVOICE_ID PRODUCT_ID DELIVERY_DATE     QUANTITY_DELIVERED
---------- ---------- ----------------- ------------------
         3          4 26-MAR-2018 12:56                  3

Perceba que os pedidos 1 e 2 ainda existem na tabela T_INVOICE, mesmo sem nenhum item relacionado na tabela T_INVOICE_ITEM. Isto é normal, já que esta regra não é validada por nenhuma constraint criada no banco de dados. Eu preciso apagar estes registros manualmente.

Perceba também que o Produto 2 foi apagado porque ele era dependente do Produto 1. Isto é um resultado esperado e mostra o risco de usar este script sem os cuidados necessários ;-).

Depois de confirmar que tudo está OK, você pode fazer o COMMIT para confirmar as alterações.

Espero que gostem!!!

Advertisements

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.