none
Origem de Bloqueio RRS feed

  • Pergunta

  • Olá Temos um banco SQLServer versão 10.50.4000.0

    Em um determinado momento o banco passa a apresentar  Block restringindo a ações com uma determinada tabela.

    Tenho o seguinte cenário:
    * O block é em uma única tabela de um banco
    * Existe uma aplicação que acessa o banco, essa aplicação é uma loja virtual com centenas de usuários utilizando ao mesmo tempo
    * O programador do aplicativo não sabe identificar o que ocasiona o Block
    * Não tenho acesso ao fonte desse aplicativo

    Há como eu descobrir qual comando está rodando e bloqueando os registros?

    sexta-feira, 4 de abril de 2014 19:49

Respostas

  • Boa tarde, Matheus.

    Conforme o @Advaldo citou você pode utilizar o sp_who2 active para descobrir pela coluna BLK qual processo está block.

    E para descobrir a query, status do processo e comando :

    SELECT sqltext.TEXT,
    req.session_id,
    req.status,
    req.command
    FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
    where
    session_id = --Numero do SPID que está bloqueando o seu processo

    Abs!

    • Marcado como Resposta MatheusFernando segunda-feira, 14 de abril de 2014 11:17
    sexta-feira, 4 de abril de 2014 21:11
  • Matheus,

    Se o seu programador utiliza transações para manipular os dados, você pode verificar qual o contexto está sendo bloqueado, utilizando a procedure sp_lock.

    Você também pode visualizar, através do SPID identificado qual é essa consulta através do script disponível na Galeria:

    http://gallery.technet.microsoft.com/Localize-uma-Instruo-T-SQL-5a8197a2

    Talvez está seja a consulta que está sendo mais custosa e você poderá identificar também através deste script.

    Seria interessante orientar seu programador à utilizar o hint adequado para cada uma das consultas ou manipulações nesta tabela(UPDATE/DELETE) e verificar se existe e é executado o COMMIT / ROLLBACK em casos de sucesso ou falha na operação.

    Para maiores informações sobre hints, veja:

    http://technet.microsoft.com/en-us/library/ms187713.aspx

    Para maiores informações sobre Transações no SQL Server, veja:

    http://technet.microsoft.com/pt-br/library/ms174377.aspx

    http://msdn.microsoft.com/pt-br/library/ms188929.aspx

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Giovani Cr segunda-feira, 7 de abril de 2014 19:43
    segunda-feira, 7 de abril de 2014 12:10
    Moderador
  • Tenta colocar with(nolock) em todas as tabelas...

    Existem dois eventos que podem ser utilizados no SQL Profiler para rastreamento de deadlocks:

    • Lock: DeadLock : informa a ocorrência do erro #1205 ;
    • Lock: DeadLockChain : irá apontar os comandos e respectivos spid´s relacionados ao deadlock.

    Para ilustrar o monitoramento do deadlock será criada uma trace no Profiler à partir do template padrão SQLProfilerStandard. Na guia Events foram excluidos os eventos Security Audit e Sessions e adicionados Lock:Deadlock e Lock:DeadLockChain. O primeiro passo, portanto, será criar a trace com essas especificações.

    Para reproduzir um deadlock serão abertas duas sessões (sessao-1 e sessao-2).

    1) Reproduzir um DeadLock

    -- Executar na sessao-1
    use NorthWind
    go
    BEGIN TRAN
    select * from orders (holdlock) where orderid=10249

    -- Executar na sessao-2
    use NorthWind
    go
    BEGIN TRAN
    select * from orders (holdlock) where orderid=10249

    -- Executar na sessao-1 (ficara aguardando o termino da sessao-2)
    update orders set employeeid=4 where orderid=10249

    -- Executar na sessao-2 (essa sessao sera finalizada com DeadLock)
    update orders set employeeid=4 where orderid=10249

    • Marcado como Resposta Giovani Cr segunda-feira, 7 de abril de 2014 19:43
    segunda-feira, 7 de abril de 2014 15:01

Todas as Respostas

  • No banco, pode utilizar a sp_who2 active, onde a coluna BLK mostra o SPID que esta causando o bloqueio.

    Existe também a sys.dm_exec_requests, sys.dm_exec_sessions

    []´s

    sexta-feira, 4 de abril de 2014 20:22
  • Boa tarde, Matheus.

    Conforme o @Advaldo citou você pode utilizar o sp_who2 active para descobrir pela coluna BLK qual processo está block.

    E para descobrir a query, status do processo e comando :

    SELECT sqltext.TEXT,
    req.session_id,
    req.status,
    req.command
    FROM sys.dm_exec_requests req
    CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext 
    where
    session_id = --Numero do SPID que está bloqueando o seu processo

    Abs!

    • Marcado como Resposta MatheusFernando segunda-feira, 14 de abril de 2014 11:17
    sexta-feira, 4 de abril de 2014 21:11
  • Matheus,

    Se o seu programador utiliza transações para manipular os dados, você pode verificar qual o contexto está sendo bloqueado, utilizando a procedure sp_lock.

    Você também pode visualizar, através do SPID identificado qual é essa consulta através do script disponível na Galeria:

    http://gallery.technet.microsoft.com/Localize-uma-Instruo-T-SQL-5a8197a2

    Talvez está seja a consulta que está sendo mais custosa e você poderá identificar também através deste script.

    Seria interessante orientar seu programador à utilizar o hint adequado para cada uma das consultas ou manipulações nesta tabela(UPDATE/DELETE) e verificar se existe e é executado o COMMIT / ROLLBACK em casos de sucesso ou falha na operação.

    Para maiores informações sobre hints, veja:

    http://technet.microsoft.com/en-us/library/ms187713.aspx

    Para maiores informações sobre Transações no SQL Server, veja:

    http://technet.microsoft.com/pt-br/library/ms174377.aspx

    http://msdn.microsoft.com/pt-br/library/ms188929.aspx

    Se ajudou na sua solução, não esqueça de marcar como resposta !

    Abraços,

    Durval Ramos
    Microsoft Partner | MTA | MCSA - SQL Server 2012
    ----------------------------------
    Se foi resolvido clique "Marcar como resposta" e se foi útil "Votar como Útil"
    • Marcado como Resposta Giovani Cr segunda-feira, 7 de abril de 2014 19:43
    segunda-feira, 7 de abril de 2014 12:10
    Moderador
  • Tenta colocar with(nolock) em todas as tabelas...

    Existem dois eventos que podem ser utilizados no SQL Profiler para rastreamento de deadlocks:

    • Lock: DeadLock : informa a ocorrência do erro #1205 ;
    • Lock: DeadLockChain : irá apontar os comandos e respectivos spid´s relacionados ao deadlock.

    Para ilustrar o monitoramento do deadlock será criada uma trace no Profiler à partir do template padrão SQLProfilerStandard. Na guia Events foram excluidos os eventos Security Audit e Sessions e adicionados Lock:Deadlock e Lock:DeadLockChain. O primeiro passo, portanto, será criar a trace com essas especificações.

    Para reproduzir um deadlock serão abertas duas sessões (sessao-1 e sessao-2).

    1) Reproduzir um DeadLock

    -- Executar na sessao-1
    use NorthWind
    go
    BEGIN TRAN
    select * from orders (holdlock) where orderid=10249

    -- Executar na sessao-2
    use NorthWind
    go
    BEGIN TRAN
    select * from orders (holdlock) where orderid=10249

    -- Executar na sessao-1 (ficara aguardando o termino da sessao-2)
    update orders set employeeid=4 where orderid=10249

    -- Executar na sessao-2 (essa sessao sera finalizada com DeadLock)
    update orders set employeeid=4 where orderid=10249

    • Marcado como Resposta Giovani Cr segunda-feira, 7 de abril de 2014 19:43
    segunda-feira, 7 de abril de 2014 15:01