O perigo do parâmetro COMMIT_WAIT em NOWAIT

O parâmetro COMMIT_WAIT, controla como o Log Writer espera a resposta do I/O a cada COMMIT (na verdade a cada flush do Log Buffer, que também ocorre a cada COMMIT), dos dados que estão no Log Buffer e devem ir para o Redo Log CURRENT.

O padrão deste parâmetro é FORCE_WAIT, que espera a gravação de forma síncrona, a cada COMMIT.

A alteração dele para NOWAIT tornará esta espera assíncrona. Ou seja, quando gravar, gravou. Isto reduz em muito o Wait Event “log file sync”, o que acelerará as gravações do banco de dados.

Mas, assim como está na documentação, isto viola a 4a regra do ACID (Atomicity, Consistency, Isolation, Durability):

Be aware that the NOWAIT option can cause a failure that occurs after the database receives the commit message, but before the redo log records are written. This can falsely indicate to a transaction that its changes are persistent. Also, it can violate the durability of ACID (Atomicity, Consistency, Isolation, Durability) transactions if the database shuts down unexpectedly.

Fonte: http://docs.oracle.com/database/121/REFRN/refrn10266.htm#REFRN10266

Vamos provar isto então.

Eu fiz este script que insere um número simples e sequencial em uma tabela, executando COMMIT a cada linha, e em seguida imprime na tela o número inserido. Reparem que coloco o AutoCommit => 1 (COMMIT a cada linha não é uma boa ideia por si só, a não ser que a regra de negócio exija, como no caso de sistemas bancários e de cartão de crédito).

[ricardo@Melquior ~]$ cat ./CommitBAD_BindsGOOD.pl
#!/usr/bin/perl
use strict;
use warnings;
use DBD::Oracle qw(:ora_session_modes);

### Conexão com o banco de dados.
my $oracle_hostname = 'localhost';
my $oracle_database = 'ORCL';
my $oracle_username = 'SCOTT';
my $oracle_password = 'TIGER';
my $oracle_dbh = DBI->connect("dbi:Oracle:host=$oracle_hostname;sid=$oracle_database", $oracle_username, $oracle_password, {RaiseError => 1, AutoCommit => 1});

### LOOP para teste de carga.
for (my $numero = 1; $numero < 100000; $numero++) 	{ 	my $oracle_sql = "INSERT INTO T3 (NUMERO) VALUES (?)"; 	my $oracle_sth = $oracle_dbh->prepare($oracle_sql) or die $DBI::errstr;
	$oracle_sth->execute($numero) or die $DBI::errstr;
	print "$numeron";
	}

$oracle_dbh->disconnect;
exit;

[ricardo@Melquior ~]$

Coloquei então o script para rodar:

[ricardo@Melquior ~]$ ./CommitBAD_BindsGOOD.pl
1
2
3
4
5
6
7
8
9
10
...

Em seguida, alterei o parâmetro em questão para NOWAIT, e simulei uma falha na instância.

SQL> SHOW PARAMETER COMMIT_WAIT

NAME				     TYPE	 VALUE
------------------------------------ ----------- ------------------------------
commit_wait			     string	 NOWAIT
SQL> !ps aux | grep pmon
ricardo  13243  0.0  0.1 6635984 22572 ?       Ss   16:40   0:00 ora_pmon_ORCL
ricardo  14287  0.0  0.0 106100  1148 pts/1    S+   16:57   0:00 /bin/bash -c ps aux | grep pmon
ricardo  14289  0.0  0.0 103256   836 pts/1    S+   16:57   0:00 grep pmon

SQL> !kill -9 13243

SQL> !ps aux | grep pmon
ricardo  14304  0.0  0.0 106100  1144 pts/1    S+   16:57   0:00 /bin/bash -c ps aux | grep pmon
ricardo  14306  0.0  0.0 103252   832 pts/1    S+   16:57   0:00 grep pmon

SQL> STARTUP
SP2-0642: SQL*Plus internal error state 2133, context 3135:0:0
Unsafe to proceed
ORA-03135: connection lost contact
Process ID: 14286
Session ID: 357 Serial number: 61277


SQL> EXIT
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[ricardo@Melquior ~]$ rlwrap sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Aug 12 16:57:53 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> STARTUP
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size		    2938792 bytes
Variable Size		 1224738904 bytes
Database Buffers	 5200936960 bytes
Redo Buffers		   13836288 bytes
Database mounted.
Database opened.
SQL>

O script terminou sua execução assim, inserindo como último número o 67304 (no script, o print só ocorre depois da inserção em AutoCommit).

67299
67300
67301
67302
67303
67304
DBD::Oracle::st execute failed: ORA-03113: end-of-file on communication channel
Process ID: 14273
Session ID: 247 Serial number: 16238 (DBD ERROR: OCIStmtExecute) [for Statement "INSERT INTO T3 (NUMERO) VALUES (?)" with ParamValues: :p1=67305] at ./CommitBAD_BindsGOOD.pl line 18.
DBD::Oracle::st execute failed: ORA-03113: end-of-file on communication channel
Process ID: 14273
Session ID: 247 Serial number: 16238 (DBD ERROR: OCIStmtExecute) [for Statement "INSERT INTO T3 (NUMERO) VALUES (?)" with ParamValues: :p1=67305] at ./CommitBAD_BindsGOOD.pl line 18.

E qual foi o último número inserido na tabela?

SQL> SELECT MAX(NUMERO) FROM SCOTT.T3;

MAX(NUMERO)
-----------
      67234

SQL>

Em resumo, perdemos dados. Executamos o COMMIT e o dado sumiu, violando um princípio fundamental de banco de dados. É uma boa opção para acelerar as gravações de seu banco de dados. Se você não ligar para seus dados.

Uma opção muito menos agressiva é alterar o parâmetro COMMIT_LOGGING para BATCH, que abordarei em um outro Post.

2 comments

  1. Pra quem fizer o curso de Performance Tuning entenderá muito bem esse conceito e a velocidade de gravação com o parâmetro em NOWAIT.
    Mesmo assim ficou muito claro a explicação da perda dos dados.
    Abraços!

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.