7. Gerenciamento do acesso simultâneo aos dados
Até agora, utilizamos tabelas das quais éramos os únicos usuários. Na prática, em uma máquina com múltiplos usuários, os dados são, na maioria das vezes, compartilhados entre diferentes usuários. Surge então a questão: quem pode utilizar determinada tabela e de que forma (consulta, inserção, exclusão, adição, ...)?
7.1. Criação de usuários no Firebird
Quando trabalhamos com o IB-Expert, nos conectamos como usuário SYSDBA. É possível encontrar essa informação nas propriedades da conexão aberta para SGBD:
 |  |
À direita, vemos que o usuário conectado é [SYSDBA]. O que não aparece é sua senha, [masterkey]. [SYSDBA] é um usuário especial do Firebird: ele possui todos os direitos sobre todos os objetos gerenciados pelo SGBD. É possível criar novos usuários com o IBExpert usando a opção [Tools / User Manager] ou o seguinte ícone:
Aparece a janela de gerenciamento de usuários:
O botão [Add] permite criar novos usuários:
Vamos, então, criar os seguintes usuários:
nome | senha |
ADMIN1 | admin1 |
ADMIN2 | admin2 |
SELECT1 | select1 |
SELECT2 | select2 |
UPDATE1 | atualizar1 |
UPDATE2 | update2 |
7.2. Conceder direitos de acesso aos usuários
Um banco de dados pertence à pessoa que o criou. Os bancos de dados que criamos até agora pertenciam ao usuário [SYSDBA]. Para ilustrar o conceito de direitos, vamos criar (Database / Create Database) um novo banco de dados com a identidade [ADMIN1, admin1]:
e vamos registrá-la com o alias DBACCES (ADMIN1). O uso de aliases permite abrir conexões em um mesmo banco de dados atribuindo-lhes identificadores diferentes, o que facilita sua identificação no explorador de bancos de dados de IBExpert:
 |  |
Vamos agora criar as duas tabelas TA e TB a seguir:
Tabela TA
 |  |
Tabela TB
 |  |
Essas tabelas não têm nenhuma relação entre si.
Com o IB-Expert, vamos criar uma segunda conexão com o banco de dados [DBACCES], desta vez com o nome [ADMIN2 / admin2]. Para isso, utilizaremos a opção [Database / Register Database]:
 |  |
Vamos selecionar o arquivo DBACCES (ADMIN2) e abrir um editor SQL (Shift + F12):
 |
Teremos a oportunidade de utilizar diversas conexões na mesma base de dados [DBACCES]. Para cada uma delas, teremos um editor SQL. No [1], o editor SQL indica o alias do banco de dados conectado. Use essa indicação para saber em qual editor SQL você está. Isso será importante, pois vamos criar conexões que não terão os mesmos direitos de acesso aos objetos do banco de dados.
Vamos consultar o conteúdo da tabela TA:
Recebemos a seguinte mensagem de erro:
O que isso significa? O banco de dados [DBACCESS] foi criado pelo usuário [ADMIN1] e, portanto, é de sua propriedade. Somente ele tem acesso aos diversos objetos desse banco de dados. Ele pode conceder direitos de acesso a outros usuários com o comando SQL GRANT. Esse comando possui diversas sintaxes. Uma delas é a seguinte:
GRANT privilégio1, privilégio2, ...| ALL PRIVILEGES ON table/vue TO usuário1, usuário2, ...| PUBLIC [ WITH GRANT OPTION ] | |
concede privilégios de acesso privilègei ou todos os privilégios (ALL PRIVILEGES) na table ou vue aos usuários utilisateuri ou a todos os usuários (PUBLIC). A cláusula WITH GRANT OPTION permite que os usuários que receberam os privilégios os repassem, por sua vez, a outros usuários. |
Entre os privilégios privilègei que podem ser concedidos estão os seguintes:
direito de utilizar o comando DELETE na tabela ou na visualização. | |
direito de usar o comando INSERT na tabela ou na visualização | |
direito de usar o comando SELECT na tabela ou na visualização | |
direito de usar o comando UPDATE na tabela ou na visualização. Esse direito pode ser restrito a determinadas colunas por meio da sintaxe: GRANT atualizar (col1, col2, ...) ON tabela/visão TO usuário1, usuário2, ...| PUBLIC [ WITH GRANT OPTION ] |
Concedamos ao usuário [ADMIN2] o direito SELECT sobre a tabela TA. Somente o proprietário da tabela pode conceder esse direito, c.a.d. Aqui, [ADMIN1]. Vamos acessar a conexão DBACCES (ADMIN1) e abrir um novo editor SQL (Shift+F12):
Em seguida, vamos alternar entre os editores SQL. Para se orientar, é possível usar a opção [Windows] do menu:
Acima, vemos os dois editores SQL, cada um associado a um usuário específico. Voltemos ao editor SQL (ADMIN1) e executemos o seguinte comando:
Em seguida, vamos validá-lo com um COMMIT:
Feito isso, vamos ao editor do usuário ADMIN2 para refazer o SELECT que falhou:
Recebemos a seguinte mensagem de erro:
O usuário [ADMIN2] ainda não tem permissão para consultar a tabela [TA]. Na verdade, parece que as permissões de um usuário são carregadas no momento em que ele faz login. O usuário [ADMIN2] continuaria, então, com as mesmas permissões que tinha no início do login, ou seja, nenhuma. Vamos verificar isso. Vamos desconectar o usuário [ADMIN2]:
- selecione a conexão dele
- solicitar o logout clicando com o botão direito do mouse na conexão e selecionando a opção [Deconnect from database] ou (Shift + Ctrl + D)
Se um painel solicitar um [COMMIT], insira o [COMMIT]. Em seguida, reconectemos o usuário [ADMIN2] selecionando a opção [Reconnect] acima. Feito isso, voltemos ao editor SQL (ADMIN2) e reexecutemos a solicitação SELECT que falhou:
Obtemos então o seguinte resultado:
Desta vez, ADMIN2 pode consultar a tabela TA graças ao direito SELECT que lhe foi concedido por seu proprietário, ADMIN1. Normalmente, esse é o único direito que ele possui. Vamos verificar isso. Ainda no editor SQL (ADMIN2):
 |  |
A tela à direita mostra que ADMIN2 não possui o direito DELETE na tabela TA.
Voltemos ao editor SQL (ADMIN1) para conceder mais direitos ao usuário ADMIN2. Executamos sucessivamente os dois comandos a seguir:
 |  |
- o primeiro comando concede ao usuário ADMIN2 todos os direitos de acesso à tabela [TA], além da possibilidade de conceder direitos a outros usuários (WITH GRANT OPTION)
- o segundo comando confirma o anterior
Feito isso, assim como anteriormente, renovemos a conexão do usuário [ADMIN2] (Desconectar / Reconectar) e, em seguida, no editor SQL (ADMIN2), digitemos os seguintes comandos:
 |  |  |
O comando ADMIN2 excluiu todas as linhas da tabela TA. Vamos reverter essa exclusão com o comando ROLLBACK:
 | |  |
Vamos verificar se o ADMIN2, por sua vez, pode conceder direitos sobre a tabela TA.
 |  |
Agora, vamos abrir uma conexão no banco de dados [DBACCES] (Banco de dados / Registrar banco de dados) com o nome [SELECT1 / select1], um dos usuários criados anteriormente, e clicar duas vezes no link assim criado em [Database Explorer]:
 |  |
Vamos selecionar essa nova conexão e abrir um novo editor SQL (Shift + F12) para digitar os seguintes comandos:
 |  |
O usuário SELECT1 possui o direito SELECT na tabela TA. Ele tem a possibilidade de transferir esse direito para o usuário SELECT2?
 |
A operação falhou porque o usuário SELECT1 não recebeu a permissão para transferir a permissão SELECT que havia recebido do usuário ADMIN2. Para isso, seria necessário que ousuário ADMIN2 utilizasse a cláusula WITH GRANT OPTION em sua ordem SQL GRANT. As regras de transmissão são simples:
- um usuário só pode transmitir os direitos que recebeu e nada além disso
- e só pode transmiti-los se os tiver recebido com o privilégio [WITH GRANT OPTION]
Um direito concedido pode ser revogado com o comando REVOKE:
REVOKE privilégio1, privilégio2, ...| ALL PRIVILEGES ON table/vue FROM usuário1, usuário2, ...| PUBLIC | |
revoga os privilégios de acesso privilègei ou todos os privilégios (ALL PRIVILEGES) na table ou vue para os usuários utilisateuri ou para todos os usuários (PUBLIC). |
Vamos tentar. Voltemos ao editor SQL do ADMIN2 para remover o direito SELECT que concedemos ao usuário SELECT1:
 |  |
Vamos desconectar e, em seguida, reconectar a conexão do usuário SELECT1. Em seguida, no editor SQL (SELECT1), vamos consultar o conteúdo da tabela TA:
 |  |
O usuário SELECT1 realmente perdeu seu direito de leitura da tabela TA. Observe-se que foi ADMIN2 quem lhe concedeu esse direito e foi ADMIN2 quem o retirou. Se ADMIN1 tentar retirá-lo, nenhum erro será sinalizado, mas é possível constatar posteriormente que SELECT1 manteve seu direito SELECT.
Um direito pode ser concedido a todos com a sintaxe: GRANT direito(s) ON tabela / visualização TO PUBLIC. Vamos, então, conceder a todos o direito SELECT sobre a tabela TA. Para isso, podemos usar ADMIN1 ou ADMIN2. Usamos ADMIN2:
 |  |
Vamos criar uma conexão no banco de dados com o usuário USER1 / user1:
 |  |
Com a conexão DBACCES (USER1), vamos abrir um novo editor SQL (Shift + F12) e digitar os seguintes comandos:
 |  |
O usuário USER1 possui, de fato, o direito SELECT na tabela TA.
7.3. As transações
7.3.1. Níveis de isolamento
Deixamos agora de lado a questão dos direitos de acesso aos objetos de um banco de dados para abordar a questão dos acessos simultâneos a esses objetos. Dois usuários com direitos de acesso suficientes a um objeto do banco de dados, uma tabela, por exemplo, desejam utilizá-lo ao mesmo tempo. O que acontece?
Cada usuário trabalha dentro de uma transação. Uma transação é uma sequência de ordens SQL que é executada de forma “atômica”:
- ou todas as operações são bem-sucedidas
- ou uma delas falha e, nesse caso, todas as anteriores são canceladas
No final, as operações de uma transação ou foram todas aplicadas com sucesso ou nenhuma foi aplicada. Quando o próprio usuário controla a transação (como é o caso em todo este documento), ele valida uma transação por meio de um comando COMMIT ou a cancela por meio de um comando ROLLBACK.
Cada usuário trabalha em uma transação que lhe pertence. Normalmente, distingue-se quatro níveis de isolamento entre os diferentes usuários:
- Leitura não confirmada
- Leitura confirmada
- Leitura repetível
- Serializable
Leitura não confirmada
Esse modo de isolamento também é chamado de “Leitura suja”. Veja um exemplo do que pode acontecer nesse modo:
- um usuário U1 inicia uma transação na tabela T
- um usuário U2 inicia uma transação na mesma tabela T
- o usuário U1 altera linhas da tabela T, mas ainda não as confirma
- o usuário U2 “vê” essas modificações e toma decisões com base no que vê
- o usuário cancela sua transação por meio de um ROLLBACK
Vemos que, no passo 4, o usuário U2 tomou uma decisão com base em dados que se revelarão incorretos posteriormente.
Leitura confirmada
Esse modo de isolamento evita a armadilha anterior. Nesse modo, o usuário U2, na etapa 4, não “verá” as alterações feitas pelo usuário U1 na tabela T. Ele só as verá depois que U1 tiver concluído sua transação.
Nesse modo, também chamado de “Unrepeatable Read”, podem ocorrer as seguintes situações:
- um usuário U1 inicia uma transação na tabela T
- um usuário U2 inicia uma transação na mesma tabela T
- O usuário U2 executa um SELECT para obter a média da coluna C das linhas de T que atendem a uma determinada condição
- o usuário U1 altera (UPDATE) alguns valores da coluna C de T e os valida (COMMIT)
- o usuário U2 repete a mesma operação SELECT descrita no item 3. Ele perceberá que a média da coluna C mudou devido às alterações feitas por U1.
Agora, o usuário U2 vê apenas as alterações “validadas” por U1. Mas, embora ele permaneça na mesma transação, duas operações idênticas (3 e 5) produzem resultados diferentes. O termo “Unrepeatable Read” se refere a essa situação. É uma situação incômoda para quem deseja ter uma visão estável da tabela T.
Leitura Repetível
Nesse modo de isolamento, um usuário tem a garantia de obter os mesmos resultados em suas leituras do banco de dados, desde que permaneça na mesma transação. Ele trabalha com uma imagem na qual as alterações feitas por outras transações, mesmo que validadas, nunca são refletidas. Ele só verá essas alterações quando ele próprio encerrar sua transação com um COMMIT ou ROLLBACK.
Esse modo de isolamento, no entanto, ainda não é perfeito. Após a operação 3 acima, as linhas consultadas pelo usuário U2 ficam bloqueadas. Durante a operação 4, o usuário U1 não poderá modificar (UPDATE) os valores da coluna C dessas linhas. Ele pode, no entanto, adicionar linhas (INSERT). Se algumas das linhas adicionadas atenderem à condição testada na etapa 3, a operação 5 resultará em uma média diferente daquela encontrada na etapa 3 devido às linhas adicionadas.
Para resolver esse novo problema, é necessário mudar para o nível de isolamento “Serializable”.
Serializable
Nesse modo de isolamento, as transações são completamente independentes umas das outras. Ele garante que o resultado de duas transações realizadas simultaneamente será o mesmo que se fossem realizadas uma após a outra. Para alcançar esse resultado, durante a operação 4, em que o usuário U1 deseja adicionar linhas que alterariam o resultado da transação SELECT do usuário U1, ele será impedido de fazê-lo. Uma mensagem de erro informará que a inserção não é possível. Ela só será possível quando o usuário U2 tiver validado sua transação.
Os quatro níveis de isolamento de transações SQL não estão disponíveis em todos os SGBD. O Firebird oferece os seguintes níveis de isolamento:
- snapshot: modo de isolamento padrão. Corresponde ao modo “Repeatable Read” do padrão SQL.
- committed read: corresponde ao modo “committed read” do padrão SQL
Esse nível de isolamento é definido pelo comando SET TRANSACTION:
SET TRANSACTION [READ WRITE | READ ONLY] [WAIT|NOWAIT] ISOLATION LEVEL [SNAPSHOT | READ COMMITTED] | |
as palavras-chave sublinhadas são os valores padrão READ WRITE: a transação pode ler e gravar READ ONLY: a transação só pode ler WAIT: em caso de conflito entre duas transações, aquela que não conseguiu realizar sua operação aguarda até que a outra transação seja validada. Ela não pode mais emitir ordens SQL. NOWAIT: a transação que não conseguiu realizar sua operação não fica bloqueada. Ela recebe uma mensagem de erro e pode continuar operando. ISOLATION LEVEL [SNAPSHOT | READ COMMITTED]: nível de isolamento |
Vamos tentar. No editor SQL (ADMIN1), digitamos o seguinte comando SQL:
Vemos que ela não foi autorizada. Não sabemos por quê...
O IB-Expert permite definir o modo de isolamento de outra maneira. Clique com o botão direito do mouse na conexão DBACCES(ADMIN1) para selecionar a opção [Database Registration Info]:
 |  |
A tela à direita mostra a presença de uma opção [Transactions]. Ela nos permitirá definir o nível de isolamento das transações. Vamos defini-lo aqui como [snapshot]. Fazemos o mesmo com a conexão DBACCES (ADMIN2).
7.3.2. O modo snapshot
Vamos examinar o nível de isolamento snapshot, que é o modo de isolamento padrão do Firebird. Quando o usuário inicia uma transação, é feita uma “foto” do banco de dados. O usuário passa então a trabalhar nessa “foto”. Cada usuário trabalha, assim, em um instantâneo do banco de dados que lhe é próprio. Se ele fizer alterações nesse instantâneo, os outros usuários não as verão. Eles só as verão quando o usuário que as fez as tiver validado por meio de um COMMIT.
Podemos considerar dois casos:
- um usuário consulta a tabela (select) enquanto outro está modificando-a (insert, update, delete)
- os dois usuários desejam modificar a tabela ao mesmo tempo
7.3.2.1. Princípio da leitura coerente
Sejam dois usuários, U1 e U2, trabalhando na mesma tabela TAB:
A transação do usuário U1 começa no momento T1a e termina no momento T1b.
A transação do usuário U2 começa no momento T2a e termina no momento T2b.
U1 está trabalhando em uma foto de TAB tirada no momento T1a. Entre T1a e T1b, ele modifica TAB. Os demais usuários só terão acesso a essas alterações no momento T1b, quando U1 criar um COMMIT.
U2 está trabalhando em uma foto de TAB tirada no momento T2a, portanto, a mesma foto usada por U1 (desde que outros usuários não tenham alterado o original nesse intervalo). Ele não “vê” as alterações que o usuário U1 possa ter feito em TAB. Ele só poderá vê-las no momento T1b.
Vamos ilustrar esse ponto em nossa base [DBACCES]. Faremos com que os dois usuários, [ADMIN1] e [ADMIN2], trabalhem simultaneamente. Vamos acessar a conexão DBACCES (ADMIN1) e, no editor SQL de ADMIN1, realizar as seguintes operações:
 |  |  |
ADMIN1 modificou a linha nº 2 da tabela TA, mas ainda não validou (COMMIT) sua operação. O usuário ADMIN2 então executa um SELECT na tabela TA (passamos do editor SQL para o ADMIN2). Estamos antes do momento T2a do exemplo.
 |  |
Retorno ao editor SQL a partir de ADMIN1, que valida sua adição:
 |
Retorno ao editor SQL de ADMIN2 para refazer o SELECT:
|  |
O ADMIN2 visualiza as alterações feitas pelo ADMIN1. No modo snapshot, uma transação não visualiza as alterações feitas por outras transações enquanto estas não estiverem concluídas.
7.3.2.2. Alteração simultânea de um mesmo objeto do banco de dados por duas transações
Vejamos um exemplo na contabilidade: U1 e U2 estão trabalhando em contas. U1 debita comptex de um valor S e credita comptey do mesmo valor. Ele fará isso em várias etapas:
U1 inicia uma transação no momento T1a, debita comptex no momento T1b, credita comptey no momento T1c e valida as duas operações no momento T1d. Suponhamos, além disso, que U2 queira fazer o mesmo, inicie sua transação no momento T2a e a conclua no momento T2d, de acordo com o esquema a seguir:
--------+----------+----+----+-------+------+-----+-------+---------
T1a T1b T2a T1c T2b T1d T2c T2d
No momento T2, é feito um instantâneo da tabela de contas para U2. Ele é coerente de acordo com o princípio de snapshot. U2 visualiza o estado inicial das contas comptex e comptey, pois U1 ainda não validou suas operações.
Suponhamos que comptex tenha um saldo inicial de 1.000 € e que cada um dos usuários U1 e U2 queira debitar 100 € dessa conta.
- No momento T1b, U1 reduz o saldo de comptex em 100 € e, portanto, o deixa em 90 €. Essa operação só será validada no momento T1d.
- No momento T2b, U2 vê comptex com 1.000 € (princípio de leitura coerente) e o diminui em 100 €, passando-o, portanto, para 90 €.
- No final, no momento T2d, quando tudo tiver sido validado, comptex terá um saldo de 90 €, em vez dos 80 € esperados.
A solução para esse problema é impedir que U2 altere comptex enquanto U1 não tiver concluído sua transação. U2 ficará, assim, bloqueado até o momento T1d. O modo snapshot fornece esse mecanismo.
Vamos ilustrar isso com a base DBACCES. ADMIN1 inicia uma transação em seu editor SQL (ADMIN1):
 |  |  |  |
Começamos executando um COMMIT para garantir o início de uma nova transação. Em seguida, excluímos a linha nº 4. A transação ainda não foi validada.
O ADMIN2, por sua vez, inicia uma transação em seu editor SQL (ADMIN2):
 |  |
A tela à direita mostra que ADMIN2 tentou modificar a linha nº 4. Recebeu a resposta de que isso não era possível porque outra pessoa já havia modificado essa linha, mas ainda não havia validado a alteração.
Voltemos ao editor SQL (ADMIN1) para criar o COMMIT:
Voltemos ao editor SQL (ADMIN2) para executar novamente o comando UPDATE:
 |  |
 |  |
A operação UPDATE ocorre normalmente, mesmo que a linha nº 4 já não exista, conforme mostra o SELECT a seguir. É nesse momento que o ADMIN2 detecta que a linha não existe mais.
7.3.2.3. O modo Repeatable Read
Vamos agora ilustrar o modo “Repeatable Read”. Esse nível de isolamento é fornecido pelo modo “snapshot”. Ele garante que uma transação obtenha sempre o mesmo resultado ao ler o banco de dados.
Vamos começar trabalhando com o editor SQL de ADMIN2:
 |  |  |
 |  |
Vamos agora para o editor SQL do ADMIN1:
 |  |  |
 |  |  |
 |  |
O usuário ADMIN1 adicionou duas linhas e confirmou sua transação. Voltemos agora ao editor SQL (ADMIN2) para reproduzir o SELECT SUM:
 |  |
Percebe-se que o ADMIN2 não reconhece as linhas adicionadas pelo ADMIN1, embora elas tenham sido validadas por um COMMIT. O SELECT SUM apresenta o mesmo resultado de antes das adições. Esse é o princípio da leitura repetível (Repeatable Read).
Agora, ainda no editor SQL (ADMIN2), vamos validar a transação com um COMMIT e, em seguida, executar novamente o SELECT SUM:
 |  |  |
As linhas adicionadas pelo ADMIN1 agora são consideradas.
7.3.3. O modo Committed Read
Vamos agora ilustrar o modo “Committed Read”. Esse nível de isolamento é análogo ao do snapshot, exceto no que diz respeito ao “Repeatable Read”.
Começamos alterando o nível de isolamento das transações das duas conexões.
- Desconectamos os dois usuários ADMIN1 e ADMIN2
- alteramos o nível de isolamento de suas transações
- reconectamos os usuários ADMIN1 e ADMIN2
Retomamos agora o exemplo anterior, que ilustrava a “Repeatable Read”, para mostrar que não temos mais o mesmo comportamento. Comecemos trabalhando com o editor SQL de ADMIN2:
| | |
| |
Vamos agora para o editor SQL do ADMIN1:
| | |
| | |
| |
O usuário ADMIN1 adicionou duas linhas e confirmou sua transação. Voltemos agora ao editor SQL (ADMIN2) para reproduzir o SELECT SUM:
|  |
O SELECT SUM não apresenta o mesmo resultado que antes das alterações feitas pelo ADMIN1. Essa é a diferença entre os modos snapshot e read committed.