7. Gestão do acesso simultâneo aos dados

Até agora, utilizámos tabelas das quais éramos os únicos utilizadores. Na prática, numa máquina multiutilizador, os dados são, na maioria das vezes, partilhados entre diferentes utilizadores. Isto levanta a questão: quem pode utilizar uma determinada tabela e com que capacidades (consulta, inserção, eliminação, adição, ...)?

7.1. Criação de utilizadores do Firebird

Quando trabalhámos com o IB-Expert, iniciámos sessão como utilizador SYSDBA. Esta informação pode ser encontrada nas propriedades da ligação aberta ao SGBD:

À direita, vemos que o utilizador com sessão iniciada é [SYSDBA]. O que não vemos é a sua palavra-passe [masterkey]. [SYSDBA] é um utilizador especial do Firebird: tem privilégios totais sobre todos os objetos geridos pelo SGBD. Pode criar novos utilizadores no IBExpert utilizando a opção [Ferramentas / Gestor de Utilizadores] ou o seguinte ícone:

Isto abre a janela de gestão de utilizadores:

O botão [Adicionar] permite-lhe criar novos utilizadores:

Vamos criar os seguintes utilizadores:

nome de utilizador	palavra-passe
ADMIN1	admin1
ADMIN2	admin2
SELECT1	select1
SELECT2	select2
ATUALIZAR1	atualizar1
ATUALIZAR2	atualização 2

7.2. Conceder direitos de acesso aos utilizadores

Uma base de dados pertence ao utilizador que a criou. As bases de dados que criámos até agora pertenciam ao utilizador [SYSDBA]. Para ilustrar o conceito de permissões, vamos criar (Base de dados / Criar base de dados) uma nova base de dados sob a identidade [ADMIN1, admin1]:

e registá-la com o alias DBACCES (ADMIN1). A utilização de aliases permite abrir ligações à mesma base de dados com nomes de utilizador diferentes, facilitando a sua identificação no explorador de bases de dados do IBExpert. :

Agora vamos criar as duas tabelas seguintes, TA e TB:

Tabela TA

Tabela TB

Estas tabelas não estão relacionadas entre si.

Utilizando o IB-Expert, vamos criar uma segunda ligação à base de dados [DBACCES], desta vez com o nome [ADMIN2 / admin2]. Para tal, utilizaremos a opção [Base de dados / Registar base de dados]:

Selecione DBACCES(ADMIN2) e abra um editor SQL (Shift + F12):

Teremos a oportunidade de utilizar várias ligações à mesma base de dados [DBACCES]. Para cada uma delas, teremos um editor SQL. Em [1], o editor SQL apresenta o alias da base de dados ligada. Utilize esta informação para determinar em que editor SQL se encontra. Isto é importante porque iremos criar ligações que não têm os mesmos direitos de acesso aos objetos da base de dados.

Vamos consultar o conteúdo da tabela TA:

Recebemos a seguinte mensagem de erro:

O que significa isto? A base de dados [DBACCESS] foi criada pelo utilizador [ADMIN1] e, por isso, é propriedade deste. Apenas ele tem acesso aos vários objetos desta base de dados. Pode conceder direitos de acesso a outros utilizadores utilizando o comando SQL GRANT. Este comando tem várias sintaxes. Uma delas é a seguinte:

`sintaxe`	GRANT privilégio1, privilégio2, ...\| ALL PRIVILEGES ON tabela/visualização A utilizador1, utilizador2, ...\| PÚBLICO [ COM OPÇÃO DE CONCESSÃO ]
`ação`	concede privilégios de acesso específicos ou todos os privilégios (ALL PRIVILEGES) na tabela ou vista a um utilizador específico ou a todos os utilizadores (PUBLIC). A cláusula WITH GRANT OPTION permite que os utilizadores a quem foram concedidos privilégios os concedam, por sua vez, a outros utilizadores.

Entre os privilégios que podem ser concedidos encontram-se os seguintes:

`DELETE`	o direito de utilizar o comando DELETE na tabela ou na vista.
`INSERT`	o direito de utilizar o comando INSERT na tabela ou vista
`SELECT`	Permissão para utilizar o comando SELECT na tabela ou vista
`UPDATE`	Permissão para utilizar o comando UPDATE na tabela ou vista. Esta permissão pode ser restringida a determinadas colunas utilizando a sintaxe: GRANT update (col1, col2, ...) ON tabela/vista TO utilizador1, utilizador2, ...\| PUBLIC [WITH GRANT OPTION]

Vamos conceder ao utilizador [ADMIN2] a permissão SELECT na tabela TA. Apenas o proprietário da tabela pode conceder esta permissão, ou seja, [ADMIN1] neste caso. Mude para a ligação DBACCES(ADMIN1) e abra um novo editor SQL (Shift+F12):

A partir daqui, iremos alternar entre os dois editores SQL. Para navegar entre eles, pode utilizar a opção do menu [Windows]:

Acima, vemos os dois editores SQL, cada um associado a um utilizador específico. Voltemos ao editor SQL (ADMIN1) e introduzamos o seguinte comando:

Em seguida, confirme com um COMMIT:

Agora, vamos mudar para o editor do utilizador ADMIN2 para executar novamente a instrução SELECT que falhou:

Recebemos a seguinte mensagem de erro:

O utilizador [ADMIN2] continua sem ter permissão para visualizar a tabela [TA]. Na verdade, parece que as permissões de um utilizador são carregadas quando este inicia sessão. O [ADMIN2] continuaria, portanto, com as mesmas permissões que tinha quando iniciou sessão pela primeira vez, ou seja, nenhuma. Vamos verificar isto. Encerre a sessão do utilizador [ADMIN2]:

selecione a sua ligação
solicite o logout clicando com o botão direito do rato na ligação e selecionando a opção [Desligar da base de dados] ou (Shift + Ctrl + D)

Se uma caixa de diálogo solicitar um [COMMIT], execute o [COMMIT]. Em seguida, volte a ligar o utilizador [ADMIN2] selecionando a opção [Reconectar] acima. Depois de fazer isto, volte ao editor SQL (ADMIN2) e execute novamente a consulta SELECT que falhou:

Obteremos então o seguinte resultado:

Desta vez, o ADMIN2 consegue visualizar a tabela TA graças ao privilégio SELECT concedido pelo seu proprietário, o ADMIN1. Normalmente, este é o único privilégio que possui. Vamos verificar isso. Ainda no editor SQL (ADMIN2):

O ecrã à direita mostra que o ADMIN2 não tem permissão DELETE na tabela TA.

Voltemos ao editor SQL (ADMIN1) para conceder direitos adicionais ao utilizador ADMIN2. Executamos os dois comandos seguintes em sequência:

O primeiro comando concede ao utilizador ADMIN2 direitos de acesso total à tabela [TA], juntamente com a capacidade de conceder direitos a outros (WITH GRANT OPTION)
O segundo comando valida o anterior

Feito isto, tal como anteriormente, vamos atualizar a ligação do utilizador [ADMIN2] (Desligar / Voltar a ligar) e, em seguida, no editor SQL (ADMIN2), introduzir os seguintes comandos:

O ADMIN2 conseguiu eliminar todas as linhas da tabela TA. Vamos anular esta eliminação com um ROLLBACK:

Vamos verificar se o ADMIN2 consegue, por sua vez, conceder permissões na tabela TA.

Agora, vamos abrir uma ligação à base de dados [DBACCES] (Base de dados / Registar base de dados) com o nome [SELECT1 / select1], um dos utilizadores criados anteriormente, e, em seguida, clicar duas vezes na ligação criada no [Explorador de bases de dados]:

Mude para esta nova ligação e abra um novo editor SQL (Shift + F12) para introduzir os seguintes comandos:

O utilizador SELECT1 tem, de facto, direitos SELECT na tabela TA. Poderá transferir este direito para o utilizador SELECT2?

A operação falhou porque o utilizador SELECT1 não recebeu o direito de conceder o privilégio SELECT que recebeu do utilizador ADMIN2. Para que isso acontecesse, o utilizador ADMIN2 teria de utilizar a cláusula WITH GRANT OPTION na sua instrução SQL GRANT. As regras para a concessão de privilégios são simples:

um utilizador só pode conceder os privilégios que recebeu e nada mais
e só pode transmiti-los se os tiver recebido com o privilégio [WITH GRANT OPTION]

Um privilégio concedido pode ser revogado utilizando a instrução REVOKE:

`sintaxe`	REVOKE privilégio1, privilégio2, ...\| ALL PRIVILEGES ON tabela/visualização FROM user1, user2, ...\| PUBLIC
`ação`	revoga os privilégios de acesso (privilégio1) ou todos os privilégios (ALL PRIVILEGES) na tabela ou vista dos utilizadores (user1, user2, ...) ou de todos os utilizadores (PUBLIC).

Vamos experimentar. Volte ao editor SQL do ADMIN2 para remover o privilégio SELECT que concedemos ao utilizador SELECT1:

Vamos desligar e, em seguida, voltar a ligar a sessão do utilizador SELECT1. Depois, no editor SQL (SELECT1), vamos consultar o conteúdo da tabela TA:

O utilizador SELECT1 perdeu, de facto, o seu privilégio SELECT na tabela TA. Note-se que foi o ADMIN2 quem concedeu este privilégio e foi o ADMIN2 quem o revogou. Se o ADMIN1 tentar revogá-lo, não é reportado qualquer erro, mas podemos então ver que o SELECT1 manteve o seu privilégio SELECT.

Um privilégio pode ser concedido a todos utilizando a sintaxe: GRANT privilege(s) ON table / view TO PUBLIC. Vamos conceder o privilégio SELECT na tabela TA a todos. Podemos utilizar o ADMIN1 ou o ADMIN2 para o fazer. Vamos utilizar o ADMIN2:

Vamos criar uma ligação à base de dados utilizando o utilizador USER1 / user1:

Com a ligação DBACCES(USER1), abra um novo editor SQL (Shift + F12) e introduza os seguintes comandos:

O utilizador USER1 tem, de facto, permissão SELECT na tabela TA.

7.3. Transações

7.3.1. Níveis de isolamento

Passaremos agora da questão dos direitos de acesso aos objetos da base de dados para abordar a questão do acesso simultâneo a esses objetos. Dois utilizadores com direitos de acesso suficientes a um objeto da base de dados — uma tabela, por exemplo — pretendem utilizá-lo ao mesmo tempo. O que acontece?

Cada utilizador trabalha no âmbito de uma transação. Uma transação é uma sequência de instruções SQL que é executada «atomicamente»:

ou todas as operações são bem-sucedidas
ou uma delas falha, caso em que todas as anteriores são revertidas

Em última análise, as operações numa transação são todas aplicadas com sucesso ou nenhuma é aplicada. Quando o utilizador tem controlo sobre a transação (como é o caso ao longo deste documento), ele confirma uma transação com uma instrução COMMIT ou reverte-a com uma instrução ROLLBACK.

Cada utilizador trabalha dentro de uma transação que lhe pertence. Existem normalmente quatro níveis de isolamento entre diferentes utilizadores:

Leitura não confirmada
Leitura confirmada
Leitura repetível
Serializável

Leitura não confirmada

Este nível de isolamento também é conhecido como «Leitura suja». Aqui está um exemplo do que pode acontecer neste modo:

O utilizador U1 inicia uma transação na tabela T
O utilizador U2 inicia uma transação na mesma tabela T
O utilizador U1 modifica linhas na tabela T, mas ainda não as confirmou
O utilizador U2 «vê» estas alterações e toma decisões com base no que vê
O utilizador reverte a sua transação utilizando um ROLLBACK

Podemos ver que, no passo 4, o utilizador U2 tomou uma decisão com base em dados que mais tarde se revelarão incorretos.

Leitura confirmada

Este nível de isolamento evita a armadilha anterior. Neste modo, o utilizador U2, no passo 4, não «verá» as alterações feitas pelo utilizador U1 na tabela T. Só as verá depois de o U1 ter confirmado a sua transação.

Neste modo, também conhecido como «Leitura Irrepetível», podem ocorrer as seguintes situações:

um utilizador U1 inicia uma transação na tabela T
O utilizador U2 inicia uma transação na mesma tabela T
O utilizador U2 executa um SELECT para obter a média da coluna C das linhas de T que satisfazem uma determinada condição
O utilizador U1 modifica (UPDATE) determinados valores na coluna C de T e confirma (COMMIT) as alterações
O utilizador U2 repete o mesmo SELECT da etapa 3. Verá que a média da coluna C mudou devido às modificações feitas por U1.

Agora, o utilizador U2 vê apenas as alterações «confirmadas» pelo U1. No entanto, mesmo permanecendo na mesma transação, duas operações idênticas (passos 3 e 5) produzem resultados diferentes. O termo «Leitura Irrepetível» refere-se a esta situação. Trata-se de uma situação problemática para quem pretende obter uma visão consistente da tabela T.

Leitura Repetível

Neste nível de isolamento, é garantido que um utilizador obtenha os mesmos resultados das suas leituras da base de dados, desde que permaneça na mesma transação. Trabalha com um instantâneo que nunca reflete as alterações feitas por outras transações, mesmo que essas alterações tenham sido confirmadas. Só verá essas alterações quando ele próprio terminar a sua transação com um COMMIT ou ROLLBACK.

No entanto, este nível de isolamento ainda não é perfeito. Após a operação 3 acima, as linhas consultadas pelo utilizador U2 ficam bloqueadas. Durante a operação 4, o utilizador U1 não poderá modificar (UPDATE) os valores na coluna C dessas linhas. No entanto, pode adicionar novas linhas (INSERT). Se algumas das linhas adicionadas satisfizerem a condição testada na operação 3, a operação 5 produzirá uma média diferente daquela encontrada na operação 3, devido às linhas adicionadas.

Para resolver este novo problema, deve mudar para o isolamento «Serializable».

Serializable

Neste modo de isolamento, as transações estão completamente isoladas umas das outras. Isso 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 este resultado, durante a operação 4, quando o utilizador U1 tentar adicionar linhas que alterariam o resultado do SELECT do utilizador U1, será impedido de o fazer. Uma mensagem de erro informará que a inserção não é possível. Isso só será possível depois de o utilizador U2 ter confirmado a sua transação.

Os quatro níveis de isolamento de transações SQL não estão disponíveis em todos os SGBDs. 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

Este nível de isolamento é definido pelo comando SET TRANSACTION:

sintaxe

SET TRANSACTION

[READ WRITE | READ ONLY]

[WAIT|NOWAIT]

NÍVEL DE ISOLAMENTO [INSTANTÂNEO | LIDER CONFIRMADO]

Funcionalidade

As palavras-chave sublinhadas são os valores predefinidos

READ WRITE: A transação pode ler e escrever

READ ONLY: A transação só pode ler

WAIT: Em caso de conflito entre duas transações, aquela que não conseguiu concluir a sua operação aguarda até que a outra transação seja confirmada. Já não pode emitir instruções SQL.

NOWAIT: A transação que não conseguiu concluir a sua operação não é bloqueada. Recebe uma mensagem de erro e pode continuar a trabalhar.

NÍVEL DE ISOLAMENTO [SNAPSHOT | READ COMMITTED]: nível de isolamento

Vamos experimentar. No editor SQL (ADMIN1), introduza o seguinte comando SQL:

Vemos que não foi autorizado. Não sabemos porquê...

O IB-Expert permite definir o modo de isolamento de outra forma. Clique com o botão direito do rato na ligação DBACCES(ADMIN1) para selecionar a opção [Informações de registo da base de dados]:

O ecrã à direita apresenta a opção [Transações]. Isto permite-nos definir o nível de isolamento das transações. Aqui, definimo-lo como [instantâneo]. Fazemos o mesmo com a ligação DBACCES(ADMIN2).

7.3.2. Modo snapshot

Vamos examinar o nível de isolamento de instantâneo, que é o modo de isolamento padrão do Firebird. Quando um utilizador inicia uma transação, é criado um instantâneo da base de dados. O utilizador trabalha então nesse instantâneo. Cada utilizador trabalha, assim, no seu próprio instantâneo da base de dados. Se fizerem alterações nesse instantâneo, os outros utilizadores não as verão. Só as verão depois de o utilizador que fez as alterações as ter confirmado com um COMMIT.

Existem dois cenários possíveis:

um utilizador lê a tabela (SELECT) enquanto outro a está a modificar (INSERT, UPDATE, DELETE)
Ambos os utilizadores pretendem modificar a tabela ao mesmo tempo

7.3.2.1. Princípio da leitura consistente

Considere dois utilizadores, U1 e U2, a trabalhar na mesma tabela TAB:

      --------+----------+--------+-------+----------------------
              T1a       T2a      T1b     T2b

A transação do utilizador U1 começa no momento T1a e termina no momento T1b.

A transação do utilizador U2 começa no momento T2a e termina no momento T2b.

O U1 trabalha numa instantânea do TAB capturada no momento T1a. Entre T1a e T1b, ele modifica o TAB. Os outros utilizadores não terão acesso a estas modificações até ao momento T1b, quando o U1 executa um COMMIT.

O U2 trabalha numa instantânea do TAB capturada no momento T2a, que é a mesma instantânea utilizada pelo U1 (desde que nenhum outro utilizador tenha modificado o original entretanto). Ele não «vê» as alterações que o utilizador U1 possa ter feito no TAB. Só as poderá ver no momento T1b.

Vamos ilustrar este ponto utilizando a nossa base de dados [DBACCES]. Teremos os dois utilizadores [ADMIN1] e [ADMIN2] a trabalhar simultaneamente. Vamos mudar para a ligação DBACCES(ADMIN1) e, no editor SQL do ADMIN1, realizar as seguintes operações:

O ADMIN1 modificou a linha 2 da tabela TA, mas ainda não confirmou (COMMIT) a operação. O utilizador ADMIN2 executa então um SELECT na tabela TA (mudamos para o editor SQL do ADMIN2). Estamos antes do momento T2a no exemplo.

De volta ao editor SQL do ADMIN1, que confirma a atualização:

De volta ao editor SQL do ADMIN2 para executar novamente o SELECT:

O ADMIN2 vê as alterações feitas pelo ADMIN1. No modo de instantâneo, uma transação não vê as alterações feitas por outras transações até que essas transações estejam concluídas.

7.3.2.2. Modificação simultânea do mesmo objeto de base de dados por duas transações

Vejamos um exemplo da contabilidade: U1 e U2 estão a trabalhar nas contas. U1 debita a conta X num montante S e credita a conta Y no mesmo montante. Ele fará isto em várias etapas:

      --------+----------+--------+-------+----------------------
              T1a       T1b     T1c      T1d

U1 inicia uma transação no momento T1a, debita a conta comptex no momento T1b, credita a conta comptey no momento T1c e confirma ambas as operações no momento T1d. Suponha, além disso, que U2 queira fazer o mesmo, inicie a sua transação no momento T2a e a termine no momento T2d, de acordo com o seguinte esquema:

      --------+----------+----+----+-------+------+-----+-------+---------
              T1a       T1b  T2a   T1c     T2b   T1d   T2c    T2d

No momento T2, é obtido um instantâneo da tabela de contas para U2. Este é consistente de acordo com o princípio do instantâneo. U2 vê o estado inicial das contas comptex e comptey porque U1 ainda não confirmou as suas transações.

Suponha que comptex tenha um saldo inicial de 1.000 € e que ambos os utilizadores, U1 e U2, queiram debitar 100 € dessa conta.

No momento T1b, U1 debita 100 € da conta comptex, reduzindo o saldo para 90 €. Esta transação não será confirmada até ao momento T1d.
No momento T2b, U2 vê comptex com 1.000 € (princípio da leitura consistente) e diminui o saldo em 100 €, levando-o para 90 €.
Por fim, no momento T2d, quando tudo tiver sido validado, o comptex terá um saldo de 90 € em vez dos 80 € esperados.

A solução para este problema é impedir que U2 modifique comptex até que U1 tenha concluído a sua transação. U2 ficará, assim, bloqueado até ao momento T1d. O modo de instantâneo fornece este mecanismo.

Vamos ilustrar isto utilizando a base de dados DBACCES. O ADMIN1 inicia uma transação no seu editor SQL (ADMIN1):

Começámos por emitir um COMMIT para garantir que estávamos a iniciar uma nova transação. Em seguida, eliminámos a linha 4. A transação ainda não foi confirmada.

O ADMIN2 inicia então uma transação no seu editor SQL (ADMIN2):

O ecrã à direita mostra que o ADMIN2 tentou modificar a linha 4. Foi-lhe informado que isso não era possível porque outra pessoa já a tinha modificado, mas ainda não tinha confirmado a alteração.

Voltemos ao editor SQL (ADMIN1) para executar o COMMIT:

Voltemos ao editor SQL (ADMIN2) para executar o comando UPDATE novamente:

A operação UPDATE é concluída com sucesso, apesar de a linha n.º 4 já não existir, como demonstrado pela seguinte instrução SELECT. É neste momento que o ADMIN2 descobre que a linha já não existe.

7.3.2.3. Modo de Leitura Repetível

Vamos agora ilustrar o modo «Leitura Repetível». Este nível de isolamento é fornecido pelo modo «snapshot». Ele garante que uma transação obtenha sempre o mesmo resultado ao ler a base de dados.

Vamos começar por trabalhar com o editor SQL do ADMIN2:

Agora vamos passar para o editor SQL do ADMIN1:

O utilizador ADMIN1 adicionou duas linhas e confirmou a transação. Voltemos agora ao editor SQL (ADMIN2) para executar novamente a instrução SELECT SUM:

Podemos ver que o ADMIN2 não vê as linhas adicionadas pelo ADMIN1, apesar de estas terem sido confirmadas com um COMMIT. O SELECT SUM devolve o mesmo resultado que antes das adições. Este é o princípio da Leitura Repetível.

Agora, ainda no editor SQL (ADMIN2), vamos confirmar a transação com um COMMIT e, em seguida, executar o SELECT SUM novamente:

As linhas adicionadas pelo ADMIN1 são agora tidas em conta.

7.3.3. Modo de leitura confirmada

Vamos agora ilustrar o modo «Leitura confirmada». Este nível de isolamento é semelhante ao de um instantâneo, exceto no que diz respeito à «Leitura repetível».

Começamos por alterar o nível de isolamento da transação para ambas as ligações.

Desligamos os dois utilizadores, ADMIN1 e ADMIN2
Alteramos o nível de isolamento das suas transações

Voltamos a ligar os utilizadores ADMIN1 e ADMIN2

Vamos agora revisitar o exemplo anterior que ilustrava a «Leitura Repetível» para mostrar que já não observamos o mesmo comportamento. Comecemos por trabalhar com o editor SQL do ADMIN2:

Agora vamos passar para o editor SQL do ADMIN1:

O utilizador ADMIN1 adicionou duas linhas e confirmou a sua transação. Vamos agora voltar ao editor SQL (ADMIN2) para executar novamente o SELECT SUM:

O SELECT SUM não retorna o mesmo resultado que antes das adições feitas pelo ADMIN1. Esta é a diferença entre os modos snapshot e read committed.