Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Блокировка процессов друг друга.  [new]
Pavel1211
Member

Откуда: Екатеринбург
Сообщений: 202
День добрый.
Столкнулся с непонятным мне поведением сервера.

Два процесса как-бы лочат друг друга, но дедлок не возникает.
  • 201 spid - в курсоре перебирает все базы и делает выборки из DMV sys.database_files
  • 123 spid - пытается удалить файл данных одной из БД

    Примерно через час такого состояния 201 spid был прибит руками и файл успешно удалился.
    Вопрос: кто-нибудь сталкивался с таким поведением и может объяснить "механику" процесса?
    Заранее спасибо!

    +select @@version
    Microsoft SQL Server 2012 (SP2-CU8) (KB3082561) - 11.0.5634.1 (X64)
    Sep 3 2015 18:55:08
    Copyright (c) Microsoft Corporation
    Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)


    К сообщению приложен файл. Размер - 53Kb
  • 25 июл 16, 08:34    [19449481]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31331
    Pavel1211
    Столкнулся с непонятным мне поведением сервера.

    Два процесса как-бы лочат друг друга, но дедлок не возникает.
  • 201 spid - в курсоре перебирает все базы и делает выборки из DMV sys.database_files
  • 123 spid - пытается удалить файл данных одной из БД

    Примерно через час такого состояния 201 spid был прибит руками и файл успешно удалился.
    Вопрос: кто-нибудь сталкивался с таким поведением и может объяснить "механику" процесса?
    Заранее спасибо!
  • Вообще блокировка ресурсов одним процессом, и ожидание конца этой блокировки другими - это как раз нормально, для этого блокировки и придуманы.
    То есть у вас процесс 201 специально заблокировал некий ресурс, что бы 123 (и другие) не могли им воспользоваться, так и задумано.
    Отпустил процесс 201 этот ресурс, и 123 продолжил работу.

    Вот дедлок - это действительно нечто необычное, это взаимоблокировки, когда 2 процесса одновременно блокируют ресурсы друг друга, и никто из них не может продолжить работу. Поэтому сиквекл такие случаи находит, и убивает один из процессов.
    25 июл 16, 09:12    [19449538]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31331
    alexeyvg
    2 процесса одновременно блокируют ресурсы друг друга
    Ой, тут же действительно, дедлок, который сиквел не распознал :-(

    Да, у меня такое бывало, хотя очень редко, считанные разы за много лет...

    Вы версию сиквела напишите, полную...
    25 июл 16, 09:28    [19449574]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6801
    alexeyvg,

    а вроде всё таки обычное ожидание...
    25 июл 16, 09:30    [19449578]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    Pavel1211
    Member

    Откуда: Екатеринбург
    Сообщений: 202
    alexeyvg, Дак я же написал под спойлером.

    ТаРак, Можно поподробнее?) У меня тоже есть подозрение что это не дедлок, но точного объяснения я пока не знаю
    25 июл 16, 09:34    [19449589]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    alexeyvg
    Member

    Откуда: Moscow
    Сообщений: 31331
    TaPaK
    alexeyvg,

    а вроде всё таки обычное ожидание...
    Ну они же друг друга блокируют.

    123 процесс ожидает ресурса, который заблокирован 201 процессом.
    А 201 процесс не может этот ресурс отдать, потому что он ничего не делает, и ожидает ресурса, который заблокирован 123 процесс.
    Что это, как не дедлок?

    cpu и io у них маленькие (тем более для часа работы), то есть они ничего не делают и тупо ждут.
    25 июл 16, 10:21    [19449752]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    o-o
    Guest
    зачем спиду 201 X, если он всего лишь читает?
    на что он его хочет, чей это key?
    25 июл 16, 10:27    [19449773]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    invm
    Member

    Откуда: Москва
    Сообщений: 9279
    Pavel1211
    может объяснить "механику" процесса?
    У вас один из ресурсов - latch, причем не связанный с buffer pool.
    Насколько я помню, латчи не остлеживаются монитором дедлоков.
    25 июл 16, 10:51    [19449848]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    o-o
    Guest
    Delaney & Co, Microsoft SQL Server 2008 Internals
    Latches are not involved in deadlock detection because SQL Server uses deadlock-proof algorithms when it acquires latches.

    чего-то подкачал алгоритм.
    может, стОит это дело кому-то показать?
    на коннекте вряд ли ответят, попробуй такое воспроизведи,
    напишут non-reproducible и закроют.
    но может написать кому-то из этой компашки?
    Kalen Delaney, Conor Cunningham, Adam Machanic, Ben Nevarez, Paul S. Randal, Kimberly L. Tripp
    25 июл 16, 11:45    [19450262]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    Pavel1211
    Member

    Откуда: Екатеринбург
    Сообщений: 202
    Я немного ошибся.
    В 201 spid чтение sys.database_files только одной БД с id = 39.
    Хотя это никакой роли не играет.
    На 99% уверен кроме чтения системной DMV ничего в 201 spid не происходило.
    o-o
    зачем спиду 201 X, если он всего лишь читает?
    на что он его хочет, чей это key?

    Вот этого не знаю, но это key системной таблицы sys.sysprufiles. Там хранятся данные о файлах данных БД.

    За цитату из авторитетного источника спасибо :-)
    Будет время - спрошу Рэндала.
    25 июл 16, 12:07    [19450393]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    Pavel1211
    Member

    Откуда: Екатеринбург
    Сообщений: 202
    Прямо сейчас удалось воспроизвести ситуацию на боевой системе.
    Может кто-нибудь подскажет на что еще можно обратить внимание для более тщательного анализа?
    25 июл 16, 12:44    [19450650]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    o-o
    Guest
    Pavel1211
    Прямо сейчас удалось воспроизвести ситуацию на боевой системе.

    а можно сюда репро выложить, я тоже хочу воспроизвести.
    25 июл 16, 13:39    [19451064]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    Pavel1211
    Member

    Откуда: Екатеринбург
    Сообщений: 202
    o-o,
    К сожалению локально не могу повторить.
    Но в моем понимании на клиенте это выглядит вот так:

    Подготовка:
    +
    create database Test_Remove_Files
    go
    
    declare @i int = 0
          , @cmd nvarchar( max );
    
    while ( @i < 1413 )
    begin 
        
        set @cmd = N'alter database Test_Remove_Files add filegroup FG' + cast( @i as nvarchar );
    
        exec ( @cmd );
        
        set @cmd = N'
        alter database Test_Remove_Files 
        add file 
          ( name = ''FG' + cast( @i as nvarchar ) + '_1'',  filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_1.ndf'', size = 512Kb ) 
        , ( name = ''FG' + cast( @i as nvarchar ) + '_2'',  filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_2.ndf'', size = 512Kb ) 
        , ( name = ''FG' + cast( @i as nvarchar ) + '_3'',  filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_3.ndf'', size = 512Kb ) 
        , ( name = ''FG' + cast( @i as nvarchar ) + '_4'',  filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_4.ndf'', size = 512Kb ) 
        , ( name = ''FG' + cast( @i as nvarchar ) + '_5'',  filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_5.ndf'', size = 512Kb ) 
        , ( name = ''FG' + cast( @i as nvarchar ) + '_6'',  filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_6.ndf'', size = 512Kb ) 
        , ( name = ''FG' + cast( @i as nvarchar ) + '_7'',  filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_7.ndf'', size = 512Kb ) 
        , ( name = ''FG' + cast( @i as nvarchar ) + '_8'',  filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_8.ndf'', size = 512Kb ) 
        , ( name = ''FG' + cast( @i as nvarchar ) + '_9'',  filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_9.ndf'', size = 512Kb ) 
        , ( name = ''FG' + cast( @i as nvarchar ) + '_10'', filename = ''D:\Test_Remove_Files\FG' + cast( @i as nvarchar ) + '_10.ndf'', size = 512Kb ) 
        to filegroup [FG' + cast( @i as nvarchar ) + ']'
    
        exec ( @cmd );
    
        set @i += 1;
    end
    

    Первая команда:
    select type_desc ,sum(size/128)-SUM(CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0) as AvailableSpaceInMB 
    FROM sys.database_files 
    where size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 > 90
    group by type_desc
    

    Вторая команда:
    alter database Test_Remove_files remove file FG6_6
    

    Файл без проблем удаляется.

    Также пробовал ФГ, в которую входит файл FG6_6, заполнить данными, а потому удалить ( чтобы файл удалился без проблем ).
    Ситуация аналогична: команда удаления файла никаких ресурсов не ждет и файл сразу удаляется
    28 июл 16, 07:47    [19465368]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    Pavel1211
    Member

    Откуда: Екатеринбург
    Сообщений: 202
    Pavel1211,

    Спустя почти год воспроизвел эту ситуацию на тестовом сервере:
    + 56 spid

    with cte as
      ( select fg.name as [filegroup_name], df.name, physical_name, fileproperty( df.name, 'SpaceUsed' ) / 128.0 as space_used
             , size / 128.0 as file_size, max_size / 128.0 as max_size, df.type, df.data_space_id, [file_id], df.growth / 128.0 as growth
          from sys.database_files df
          left join sys.filegroups fg
            on df.data_space_id = fg.data_space_id )
    select [filegroup_name], name, physical_name
         , case
             when space_used < 512    then ltrim( str( space_used, 15, 2 ) ) + ' Mb'
             when space_used < 524288 then ltrim( str( space_used / 1024, 15, 2 ) ) + ' Gb'
                                      else ltrim( str( space_used / 1048576, 15, 2 ) ) + ' Tb'
             end as space_used
         , case
             when file_size < 512    then ltrim( str( file_size, 15, 2 ) ) + ' Mb'
             when file_size < 524288 then ltrim( str( file_size / 1024, 15, 2 ) ) + ' Gb'
                                     else ltrim( str( file_size / 1048576, 15, 2 ) ) + ' Tb'
             end as file_size
         , case
             when max_size < 512    then ltrim( str( max_size, 15, 2 ) ) + ' Mb'
             when max_size < 524288 then ltrim( str( max_size / 1024, 15, 2 ) ) + ' Gb'
                                    else ltrim( str( max_size / 1048576, 15, 2 ) ) + ' Tb'
             end as max_size
        , case
             when growth < 512    then ltrim( str( growth, 15, 2 ) ) + ' Mb'
             when growth < 524288 then ltrim( str( growth / 1024, 15, 2 ) ) + ' Gb'
                                  else ltrim( str( growth / 1048576, 15, 2 ) ) + ' Tb'
             end as growth
         , ltrim( str( 100 * space_used / file_size, 15, 2 ) ) + ' %' as fullness
      from cte
      order by [type], data_space_id, [file_id];
    go
    


    + 77 spid

     alter database dbname remove file FG_596570183_2017_03_06
    



    К сообщению приложен файл. Размер - 22Kb
    20 июл 17, 12:06    [20660941]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    o-o
    Guest
    Pavel1211,
    на dba.stackexchange.com спросите, только там щас народу маловато, лучше, когда из отпусков вернутся.
    здесь уже сплошное козлово царство, ибо цирк явно является прерогативным направлением.
    а там естественный отбор: если ты упорно несешь ахинею, причем не только в ответах, но и в вопросах,
    тебя задаунвотяд на раз.
    а без репутации ни спросить, ни ответить
    20 июл 17, 14:30    [20661638]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    Владислав Колосов
    Member

    Откуда:
    Сообщений: 7663
    o-o,

    имелось в виду, что автор просмотрел откат транзакции и подумал, что чудеса происходят и в наше время.
    20 июл 17, 16:05    [20661997]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    TaPaK
    Member

    Откуда: Kiev
    Сообщений: 6801
    Владислав Колосов
    o-o,

    имелось в виду, что автор просмотрел откат транзакции и подумал, что чудеса происходят и в наше время.
    о чём ваш комментарий? взаимно заблокированы, дедлока нет. Единственное что можно предположить что вопрос в системных объектах и не знает кого выбросить :)
    20 июл 17, 16:09    [20662005]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    o-o
    Guest
    TaPaK
    взаимно заблокированы, дедлока нет.
    Единственное что можно предположить что вопрос в системных объектах и не знает кого выбросить :)

    вопрос в том, что эту ситуацию никто даже не пытается разрулить,
    а не то что выбрать не может.
    invm уже писал, один из ресурсов latch,
    и дедлочный монитор его отсеял как "не мое дело"
    при этом пишут,
    автор
    Latches are not involved in deadlock detection because SQL Server uses deadlock-proof algorithms when it acquires latches.

    спрашивается, и где эти "deadlock-proof algorithms"?
    ----
    на dba.stackexchange.com появляются типы из MS.
    правда, баги они признавать не желают.
    когда нечего сказать, просто отмалчиваются
    20 июл 17, 16:36    [20662110]     Ответить | Цитировать Сообщить модератору
     Re: Блокировка процессов друг друга.  [new]
    Pavel1211
    Member

    Откуда: Екатеринбург
    Сообщений: 202
    o-o,

    Да, время будет спрошу наконец-то и Рэндала и м.б. там вопрос задам
    21 июл 17, 15:53    [20665143]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить