XOOPS Brasil

 

6.7. Comandos Transacionais e de Lock do MySQL

6.7.1. Sintaxe de START TRANSACTION, COMMIT e ROLLBACK

Por padrão, MySQL é executado em modo autocommit. Isto significa que assim que você executa uma instrução que atualiza (modifica) uma tabela, o MySQL armaena a atualização no disco.

Se você estiver usando tabelas com segurança a transação (como InnoDB \ ou BDB), você pode colocar o MySQL em modo não autocommit com o seguinte comando:

SET AUTOCOMMIT=0

Depois de disabilitar o modo autocommit configurando a variável AUTOCOMMIT com zero, você deve utilizar COMMIT para armazenar suas alterações em disco ou ROLLBACK se você deseja ignorar as alterações que você fez desde o início da sua transação.

Se você quiser disabilitar o modo autocommit para uma única série de instruções, você pode utiliar a instrução START TRANSACTION:

START TRANSACTION;
SELECT @A:=SUM(salary) FROM table1 WHERE type=1;
UPDATE table2 SET summmary=@A WHERE type=1;
COMMIT;

BEGIN e BEGIN WORK podem ser usados em vez de START TRANSACTION para iniciar uma transação. START TRANSACTION foi adicionado no MySQL 4.0.11; ele é uma sintaxe do SQL-99 e é o modo recomendado de iniciar umaa transação an ad-hoc. BEGIN e BEGIN WORK estão disponíveis a partir do MySQL 3.23.17 e 3.23.19, respectivamente.

Note que se você estiver usando tabelas sem segurança a transação, quaisquer alterações serão armazenadas de uma vez, se considerar o status do modo autocommit.

Se você executar uma instrução ROLLBACK depois de atualizar uma tabela não-transacional, você obterá um erro (ER_WARNING_NOT_COMPLETE_ROLLBACK), como um aviso. Todas as tabelas seguras a transação serão restauradas mas qualquer tabela se segurança a transação não sofrerão alterações.

Se você estiver usando START TRANSACTION ou SET AUTOCOMMIT=0, você deve usar o log binário do MySQL para backup no lugar do antigo log de atualização. Transações são armazenadas no log binário em um bloco, sobre COMMIT, para assegurar que transações nas quais foram feitas rolled back não foram armazenadas. Veja mais informações sobre isto na Seção 4.10.4, “O Log Binário”.

Você pode alterar o nível isolação para transações com SET TRANSACTION ISOLATION LEVEL. Veja mais informações sobre isto na Seção 6.7.6, “Sintaxe SET TRANSACTION.

6.7.2. Instruções que Não Podem Ser Desfeitas

Não se pode fazer o roll back de algumas instruções. Em geral, elas incluem instruções DDL (data definition language), como aquelas que criam ou removem banco de dados, ou aquelas que criam, apagam ou alteram tabelas.

Você pode desejar projetar as suas transações para não incluir estas instruções. Se você executar uma instrução da quale não se pode fazer roll back em uma transação, e então outra intruções falhar posteriormente, o efeito total da transação não pode ser desfeito usando uma instrução ROLLBACK.

6.7.3. Instruções que Fazem um Commit Implicito

Os seguintes comandos finalizam uma transação implicitamente (como se você tivesse feito um COMMIT antes de executar o comando):

ComandoComandoComando
ALTER TABLEBEGINCREATE INDEX
DROP DATABASEDROP INDEXDROP TABLE
LOAD MASTER DATALOCK TABLESRENAME TABLE
SET AUTOCOMMIT=1START TRANSACTIONTRUNCATE

UNLOCK TABLES também finaliza uma transação se qualquer tabela estiver atualmente bloqueada. Antes do MySQL 4.0.13, CREATE TABLE finaliza uma transação se o log binário está habilitado.

Transações não podem ser aninhadas. Isto é uma consequência do COMMIT implícito realizado por qualquer transação atual quando você envia uma instrução START TRANSACTION ou um de seus sinônimos.

6.7.4. Sintaxe de SAVEPOINT e ROLLBACK TO SAVEPOINT

A partir do MySQL 4.0.14 e 4.1.1. o InnoDB suporta os comando SQL SAVEPOINT e ROLLBACK TO SAVEPOINT.

SAVEPOINT identificador

Esta instrução configura um savepoint de uma transação cujo nome é identificador. Se a transação atual já tiver um savepoint com o mesmo nome, o savepointy antigo é deletado é o novo é definido.

ROLLBACK TO SAVEPOINT identificador

Esta instrução faz o roll back de uma transação até o savepoint indicado. Modificações feitas nesta transação após o savepoint foram definidas como desfeitas no roll back, mas o InnoDB não libera o lock de linha que forma arnmazenados na memória depois do savepoint. (Note que para uma nova linha inserida, a informação do lock é carregada pala ID da transação armazenada na linha; o lock não é armazenado separadamente na memória. Neste caso, o lock de linha é liberado no undo.) Sevapoints que foram definidos após o sevepoint indicado são deletados.

Se o comando retorna o seguinte erro, significa que não existem savepoints como o nome especificado.

ERROR 1181: Got error 153 during ROLLBACK

Todos os savepoints da transação atual são deletados se você executar um COMMIT ou um ROLLBACK que não chamou um savepoint.

6.7.5. Sintaxe LOCK TABLES e UNLOCK TABLES

LOCK TABLES nome_tabela [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE}
[, nome_tabela [AS alias] {READ [LOCAL] | [LOW_PRIORITY] WRITE} ...]
...
UNLOCK TABLES

LOCK TABLES bloqueia tabelas para a thread atual. UNLOCK TABLES libera qualquer trava existente para a thread atual. Todas as tabela que estão bloqueadas pela thread atual são implicitamente desbloquadas quando a thread executa um outro LOCK TABLES, ou quando a conexão ao servidor é fechada.

Para usar LOCK TABLES no MySQL 4.0.2 você precisa do privilégio global LOCK TABLES e um privilégio SELECT nas tabelas envolvidas No MySQL 3.23 você precisa ter os privilégios SELECT, insert, DELETE e UPDATE para as tabelas.

A razão principal para utilizar LOCK TABLES é para emular transações ou obter mais velocidade ao atualizar tabelas. Isto é explicado em mais detalhes posteriormente.

Se uma thread obtem uma trava de leitura (READ) em uma tabela, aquela thread (e todas as outras threads) só poderão ler da tabela. Se uma thread obter uma trava de escrita (WRITE) na tabela, apenas a thread que bloqueou poderá ler ou escrever na tabela. Outras threads serão bloqueadas.

A diferença entre READ LOCAL e READ é que READ LOCAL permite que instruções INSERT não conflitantes sejam executadas enquanto a trava está ativa. Isto, no entatnto, não pode ser usado se você for manipular o arquivo de banco de dados fora do MySQL enquanto a trava estiver ativa.

Quando você usa LOCK TABLES, você deve travar todas as tabelas que você for usar e utilizar o mesmo alias que estiver utilizando em suas consultas! Se você estiver usando uma tabela várias vezes em uma consulta (com aliases), você deve obter um trava para cada alias.

Bloqueio de escrita (WRITE) normalmente têm maior prioridade que bloqueio de leitura (READ), para assegurar que atualizações são processadas assim que possível. Isto significa que se uma thread obtida um bloqueio de leitura (READ) e outra thread requisitar um bloqueio de escrita (WRITE), bloqueios de leitura (READ) subsequentes irão esperar até a thread de escrita (WRITE) tiver obtido a trava e a liberado. Você pode usar travas LOW_PRIORITY WRITE para permitir que outras threads obtenham bloqueios de leitura (READ) enquanto a thread estiver esperando pela trava de escrita (WRITE). Você só deve utilizar bloqueios LOW_PRIORITY WRITE se você estiver certo que haverá um momento onde nenhuma thread terá bloqueio de leitura (READ).

LOCK TABLES funciona da seguinte maneira:

  1. Ordene todas as tabelas a serem travadas em uma ordem definida internamente (do ponto do utilizador a ordem é indefinida).

  2. Se uma tabela é bloqueada com uma trava de leitura e de escrita, coloque a trava de escrita antes da trava de leitura.

  3. Bloqueie uma tabela por vez até que a thread obtenha todas as travas.

Esta política assegura que as tabelas sejam bloqueadas sem deadlock. Há no entanto outra coisa da qual é preciso estar ciente neste esquema:

Se cocê estiver usando uma trava de escita LOW_PRIORITY WRITE em uma tabela, significa apenas que o MySQL irá esperar por esta trava particular até que não haja mais treads fazendo um bloqueio de leitura (READ). Quando a thread tiver obtido a trava de escrita (WRITE) e está esperando ppo obter o trava para a próxima tabela na lista de tabelas bloqueadas, todas as outras threads irão esperar que a trva de escrita (WRITE) seja liberada. Se isto tornar um sério problema com sua aplicação, você deve converter algumas de suas tabellas para tabelas com segurança em transações.

Você pode matar com segurança um thread que está esperando por um bloqueio de tabela com KILL. Veja mais informações sobre isto na Seção 4.6.7, “Sintaxe de KILL.

Note que você não deve travar nenhuma tabela que você esteja usando com INSERT DELAYED. Isto é porque este é o caso que o INSERT é feito por uma thread separada.

Normalmente, você não tem que travar tabelas, já que todas as instruções UPDATE são atomicas; nenhuma outra thread pode interferir com qualquer outra executando uma instrução SQL. Existem poucos casos em que você gostaria de travar as tabelas de qualquer forma:

  • Se você for executar operações em um grupo de tabelas, é muito mais rápido travar as tabelas que você for utilizar. O lado ruim é que nenhuma outra thread pode atualizar uma tabela travada para leitura (READ) (incluindo aquela que guarda o lock) e nenhuma outra thread pode ler uma tabela bloqueada para escrita (WRITE) além daquele que guarda o lock.

    A razão de algumas coisas serem rápidas sob LOCK TABLES é que o MySQL não irá descarregar a cache de tabelas bloqueadas até que UNLOCK TABLES seja chamado (normalmente a cache de chaves é descarregada a cada instrução SQL). Isto aumenta a velocidade de inserção, atualização e deleção) em tabelas MyISAM.

  • Se você estiver usando um mecanismo de armazenamento no MySQL que não suporte transações, você deve usar LOCK TABLES se você quiser se assegurar que nenhuma outra thread venha entre um SELECT e um UPDATE. O exemplo mostrado aqui exige LOCK TABLES para ser executado com segurança:

    mysql> LOCK TABLES trans READ, customer WRITE;
    mysql> SELECT SUM(value) FROM trans WHERE customer_id=some_id;
    mysql> UPDATE customer SET total_value=sum_from_previous_statement
    -> WHERE customer_id=some_id;
    mysql> UNLOCK TABLES;
    

    Sem LOCK TABLES, existe uma chance que outra thread possa inserir uma nova linha na tabela trans entre a execução das instruções SELECT e UPDATE.

Utilizando atualizações incrementais (UPDATE customer SET value=value+new_value) ou a função LAST_INSERT_ID()i, você pode evitar o uso de LOCK TABLES em muitos casos.

Você também pode resolver alguns casos usando as funções de bloqueio a nível de utilizador GET_LOCK() e RELEASE_LOCK(). Estas travas são salvas em uma tabela hash no servidor e implementado com pthread_mutex_lock() e pthread_mutex_unlock() para alta velocidade. Veja mais informações sobre isto na Seção 6.3.6.2, “Funções Diversas”.

Veja Seção 5.3.1, “Como o MySQL Trava as Tabelas”, para mais informações sobre política de bloqueios.

Você pode trocar todas as tabelas em todos os banco de dados com trava de leitura com o comando FLUSH TABLES WITH READ LOCK. Veja mais informações sobre isto na Seção 4.6.4, “Sintaxe de FLUSH. Este é um modo muito conveiente de tirar backups se você tiver um sistema de arquivos, como Veritas, que pode tirar snapshots.

NOTE: LOCK TABLES mão é seguro com transações e fará um commit implicitamente em qualquer transação ativa antes de tentar travar as tabelas.

6.7.6. Sintaxe SET TRANSACTION

SET [GLOBAL | SESSION] TRANSACTION ISOLATION LEVEL
{ READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE }

Define o nível de isolação da transação para global, toda a sessão ou a próxima transação.

O comportamento padrão é definir o nível de isolação para a próxima (não iniciada) transação. Se você usa a palavra-chave GLOBAL, a instrução define o nivel de transação padrão globalmente para todas as novas conexões criadas a partir deste ponto (mas não existe conexão). Você precisa do privilégio SUPER para fazer isto. Usar a palavra-chave SESSION define o nível de transação padrão para todas a transações futuras relaizadas na conexão atual.

Para a descrição de cada nível de isolação da transação do InnoDB, veja Seção 7.5.9.1, “InnoDB e SET ... TRANSACTION ISOLATION LEVEL .... O InnoDB suporta cada um destes níveis a partir do MySQL 4.0.5. O nível padrão é REPEATABLE READ.

Você pode definir o nível de isolação global padrão para o mysqld com --transaction-isolation=.... Veja mais informações sobre isto na Seção 4.1.1, “Opções de Linha de Comando do mysqld.