Bloqueio de tabela MySQL

Resumo : neste tutorial, você aprenderá como usar o bloqueio do MySQL para acessos cooperativos a tabelas entre sessões.

Um bloqueio é um sinalizador associado a uma tabela. O MySQL permite que uma sessão cliente adquira explicitamente um bloqueio de tabela para evitar que outras sessões acessem a mesma tabela durante um período específico.

Uma sessão de cliente pode adquirir ou liberar bloqueios de tabela somente para si mesma. Uma sessão de cliente não pode adquirir ou liberar bloqueios de tabela para outras sessões de cliente.

Antes de prosseguirmos, vamos criar uma tabela chamada messagespara praticar com as instruções de bloqueio de tabela.

CREATE TABLE messages ( 
    id INT AUTO_INCREMENT PRIMARY KEY, 
    message VARCHAR(100) NOT NULL
);Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Instrução MySQL LOCK TABLES

A LOCK TABLESinstrução a seguir adquire explicitamente um bloqueio de tabela:

LOCK TABLES table_name [READ | WRITE]Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Nesta sintaxe, você especifica o nome da tabela que deseja bloquear após as LOCK TABLESpalavras-chave. Além disso, você especifica o tipo de bloqueio,   READou  WRITE.

O MySQL permite bloquear múltiplas tabelas especificando uma lista de nomes de tabelas separados por vírgula com tipos de bloqueio que você deseja bloquear após as LOCK TABLESpalavras-chave:

LOCK TABLES table_name1 [READ | WRITE], 
            table_name2 [READ | WRITE],
             ... ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Instrução MySQL UNLOCK TABLES

A instrução UNLOCK TABLES libera quaisquer bloqueios de tabela mantidos pela sessão atual:

UNLOCK TABLES;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

READFechaduras

Uma READfechadura possui os seguintes recursos:

• Um READbloqueio para uma tabela pode ser adquirido por múltiplas sessões ao mesmo tempo. Além disso, outras sessões podem ler dados da tabela sem adquirir o bloqueio.
• A sessão que mantém o READbloqueio só pode ler dados da tabela, mas não pode gravar. E outras sessões não podem gravar dados na tabela até que o READbloqueio seja liberado. As operações de gravação de outra sessão serão colocadas em estado de espera até que o READbloqueio seja liberado.
• Se a sessão for encerrada, normalmente ou anormalmente, o MySQL irá liberar todos os bloqueios implicitamente. Este recurso também é relevante para o WRITEbloqueio.

Vamos dar uma olhada em como o READbloqueio funciona no cenário a seguir.

Primeiro, conecte-se ao banco de dados na primeira sessão e use a CONNECTION_ID()função para obter o ID de conexão atual da seguinte forma:

SELECT CONNECTION_ID();Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Em seguida, insira uma nova linha na messagestabela.

INSERT INTO messages(message) 
VALUES('Hello');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Em seguida, consulte os dados da messagestabela.

SELECT * FROM messages;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Depois disso, adquira um bloqueio usando o LOCK TABLEextrato.

LOCK TABLE messages READ;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Finalmente, tente inserir uma nova linha na messagestabela:

INSERT INTO messages(message) 
VALUES('Hi');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

MySQL emitiu o seguinte erro:

Error Code: 1099. Table 'messages' was locked with a READ lock and can't be updated.Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Assim, uma vez READadquirido o bloqueio, você não poderá gravar dados na tabela na mesma sessão.

Vamos verificar o READbloqueio de uma sessão diferente.

Primeiro, conecte-se ao banco de dados e verifique o ID da conexão:

SELECT CONNECTION_ID();Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Em seguida, consulte os dados da messages  tabela:

SELECT * FROM messages;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Em seguida, insira uma nova linha na messagestabela:

INSERT INTO messages(message) 
VALUES('Bye');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Aqui está a saída:

A operação de inserção da segunda sessão está em estado de espera porque um bloqueio READ já foi adquirido na tabela de mensagens pela primeira sessão e ainda não foi liberado.

Desde a primeira sessão, use a SHOW PROCESSLISTdeclaração para mostrar informações detalhadas:

SHOW PROCESSLIST;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Depois disso, volte para a primeira sessão e libere o bloqueio usando a UNLOCK TABLESinstrução. Depois de liberar o READbloqueio da primeira sessão, a INSERToperação da segunda sessão será executada.

Por fim, verifique os dados da messagestabela para ver se a INSERToperação da segunda sessão foi executada.

SELECT * FROM messages;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Gravar bloqueios

Uma WRITE fechadura possui os seguintes recursos:

• A única sessão que mantém o bloqueio de uma tabela pode ler e gravar dados da tabela.
• Outras sessões não podem ler e gravar dados na tabela até que o WRITEbloqueio seja liberado.

Vamos entrar em detalhes para ver como WRITEfunciona o bloqueio.

Primeiro, adquira um WRITEbloqueio desde a primeira sessão.

LOCK TABLE messages WRITE;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Em seguida, insira uma nova linha na messagestabela.

INSERT INTO messages(message) 
VALUES('Good Morning');Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Funcionou.

Em seguida, consulte os dados da messagestabela.

SELECT * FROM messages;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Também funciona.

Depois disso, a partir da segunda sessão, tente escrever e ler os dados:

INSERT INTO messages(message) 
VALUES('Bye Bye');

SELECT * FROM messages;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

O MySQL coloca essas operações em estado de espera. Você pode verificar isso usando a SHOW PROCESSLISTdeclaração:

SHOW PROCESSLIST;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Por fim, libere o bloqueio da primeira sessão.

UNLOCK TABLES;Linguagem de código:  SQL (linguagem de consulta estruturada)  ( sql )

Você verá todas as operações pendentes da segunda sessão executada e a imagem a seguir ilustra o resultado:

Bloqueios de leitura versus gravação

• Os bloqueios de leitura são bloqueios “compartilhados” que evitam que um bloqueio de gravação seja adquirido, mas não outros bloqueios de leitura.
• Os bloqueios de gravação são bloqueios “exclusivos” que impedem qualquer outro bloqueio de qualquer tipo.

Neste tutorial, você aprendeu como bloquear e desbloquear tabelas para cooperar com os acessos às tabelas entre sessões.