Bloqueio de cursor
No SQL Server, a instrução SELECT de definição de cursor está sujeita às mesmas regras de bloqueio de transação que se aplicam a qualquer outra instrução SELECT. Nos cursores, porém, um conjunto adicional de bloqueio de rolagem pode ser adquirido com base na especificação do nível de simultaneidade do cursor.
Os bloqueios de transação adquiridos pela instrução SELECT, inclusive a instrução SELECT em uma definição de cursor, são controlados por:
Configuração do nível de isolamento da transação da conexão.
Todas as dicas de bloqueio especificadas na cláusula FROM.
Esses bloqueios são mantidos até o término da transação atual em cursores e instruções SELECT independentes. Enquanto o SQL Server executar em modo de confirmação automática, toda instrução SQL individual será uma transação e os bloqueios serão liberados quando a instrução terminar. Se o SQL Server executar em modo de transação explícito ou implícito, os bloqueios serão mantidos até que a transação seja confirmada ou revertida.
Por exemplo, os bloqueios efetuados nesses dois exemplos Transact-SQL são essencialmente os mesmos:
/* Example 1 */
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
GO
SELECT * FROM AdventureWorks2008R2.Sales.Store;
GO
/* Example 2 */
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
GO
BEGIN TRANSACTION
GO
DECLARE abc CURSOR STATIC FOR
SELECT * FROM AdventureWorks2008R2.Sales.Store;
GO
OPEN abc
GO
Definir o nível de isolamento da transação como leitura repetível significa que a instrução SELECT independente do Exemplo 1 e a instrução SELECT contida na DECLARE CURSOR do Exemplo 2 geram bloqueios de compartilhamento em todas as linhas que leem, e que os bloqueios de compartilhamento são mantidos até que a transação seja confirmada ou revertida.
Adquirindo bloqueios
Embora os cursores obedeçam às mesmas regras das instruções SELECT independentes, com relação ao tipo de bloqueios de transação adquirido, os bloqueios são adquiridos em momentos diferentes. Os bloqueios gerados por uma SELECT independente, ou um cursor, são sempre adquiridos quando uma linha é recuperada. Com relação à SELECT independente, são recuperadas todas as linhas quando a instrução é executada. No entanto, os cursores recuperam as linhas em momentos diferentes, dependendo do tipo de cursor:
Cursores estáticos recuperam o conjunto de resultados inteiro no momento em que o cursor é aberto. Isso bloqueia todas as linhas do conjunto de resultados no momento da abertura.
Cursores controlados por conjuntos de chaves recuperam as chaves de todas as linhas do conjunto de resultados no momento em que o cursor é aberto. Isso bloqueia todas as linhas do conjunto de resultados no momento da abertura.
Cursores dinâmicos (inclusive normais de somente avanço) não recuperam linhas até que elas sejam buscadas. Os bloqueios não são adquiridos nas linhas até que elas tenham sido buscadas.
Cursores rápidos de somente avanço variam quando adquirem bloqueios, dependendo do plano de execução escolhido pelo Otimizador de consulta. Se um plano dinâmico for escolhido, nenhum bloqueio será adquirido até que as linhas sejam buscadas. Se forem geradas tabelas de trabalho, as linhas serão lidas dentro da tabela de trabalho e bloqueadas no momento da abertura.
Os cursores também dão suporte às suas próprias especificações de simultaneidade, algumas das quais geram bloqueios adicionais em linhas de todas as buscas. Esses bloqueios de rolagem são mantidos até a próxima operação de busca ou até que o cursor seja fechado, o que ocorrer primeiro. Se a opção de conexão que mantém os cursores abertos em uma confirmação for definida como ON, os bloqueios serão mantidos por toda a operação de confirmação ou de reversão.