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

Откуда:
Сообщений: 517
День добрый!
Есть таблица:
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [Infra].[ContextRecords](
	[crLocalId] [int] IDENTITY(1,1) NOT NULL,
	[crContextID] [varchar](50) NOT NULL,
	[crForeignID] [varchar](50) NULL,
	[crRecord] [varbinary](max) NULL,
 CONSTRAINT [pk_ContextRecords_c_LocalId] PRIMARY KEY CLUSTERED 
(
	[crLocalId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF, FILLFACTOR = 10) ON [PRIMARY],
 CONSTRAINT [uc_ContextRecords_nc_ContextID] UNIQUE NONCLUSTERED 
(
	[crContextID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO


В таблице всего 14 строк.
Переодически необходимо часто обновлять таблицу, типа:
UPDATE [Infra].[ContextRecords] 
SET [crRecord]=@ContextData0 
WHERE [crContextID]=@ContextId0;  


Такого рода апдейты относительно медленные, возникает блокировка типа lck_m_u на KEY: 6:72057594159300608 (e2aec1f664c0), что есть uc_ContextRecords_nc_ContextID.
Как можно ускорить процесс?
5 дек 13, 15:55    [15247328]     Ответить | Цитировать Сообщить модератору
 Re: Помогите плз разобраться с блокировками  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37069
Так в чем проблема-то?

  • апдейт медленный?
  • апдейт медленный, потому что ему мешает блокировка от такого же апдейта?
  • 5 дек 13, 15:58    [15247362]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    abrashka
    Member

    Откуда:
    Сообщений: 517
    Гавриленко Сергей Алексеевич,

    апдейты бегут практически одновременно, можно ли как-то ускорить процесс?
    5 дек 13, 16:01    [15247397]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 37069
    abrashka
    Гавриленко Сергей Алексеевич,

    апдейты бегут практически одновременно, можно ли как-то ускорить процесс?
    Вы на мои вопросы не ответили.
    5 дек 13, 16:02    [15247408]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104760
    abrashka
    апдейты бегут практически одновременно,

    Апдейты одной и той же записи что ли ?
    5 дек 13, 16:02    [15247410]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    abrashka
    Member

    Откуда:
    Сообщений: 517
    Glory,
    Апдейты разных записей

    Гавриленко Сергей Алексеевич,
    Проблема в том, что апдейты занимают много времени(судя по всему из-за блокировок), хотелось бы ускорить процесс.
    5 дек 13, 16:06    [15247449]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 37069
    abrashka
    Проблема в том, что апдейты занимают много времени(судя по всему из-за блокировок), хотелось бы ускорить процесс.
    "Судя по всему" не прокатит. Надо выяснять, из-за блокировок или нет, потому что подходы к решению разные будут.
    5 дек 13, 16:08    [15247468]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104760
    abrashka
    апдейты занимают много времени(судя по всему из-за блокировок), хотелось бы ускорить процесс.

    Ну так и кто, кого и на каком ресурсе блокирует то ?
    5 дек 13, 16:08    [15247472]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    abrashka
    Member

    Откуда:
    Сообщений: 517
    Гавриленко Сергей Алексеевич,

    понятно, а как можно выяснить?
    исходя из sys.dm_exec_requests- wait_time достаточно большой, может быть в разы больше cpu_time
    5 дек 13, 16:13    [15247517]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104760
    abrashka
    понятно, а как можно выяснить?

    https://www.sql.ru/articles/mssql/2004/04110303advancedlocking.shtml
    5 дек 13, 16:15    [15247532]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    abrashka
    Member

    Откуда:
    Сообщений: 517
    Glory,
    один апдейт блокирует другой
    5 дек 13, 16:17    [15247552]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104760
    abrashka
    один апдейт блокирует другой

    вот откуда вы это узнали ?
    5 дек 13, 16:20    [15247589]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    abrashka
    Member

    Откуда:
    Сообщений: 517
    Glory,

    я использую вот такой вот скрипт:
    	SELECT 
    	GETDATE() as RunTime, 
    	 SPID = er.session_id
        , ot.Threads
        , RunningThreads = coalesce(rsp.RunningThreads,0)
        , Pct_Comp = er.percent_complete
        , Est_Comp_Time = CASE er.estimated_completion_time WHEN 0 THEN NULL ELSE dateadd(minute, (er.estimated_completion_time/60000), getdate()) END 
        , er.status as RequestStatus
        , er.command                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       
        , database_name = sd.name
        , BlockedBy = wt.blocking_session_id
        , HeadBlocker = coalesce(hb5.session_id, hb4.session_id, hb3.session_id, hb2.session_id, hb1.session_id)
        , wait_type = coalesce(CASE er.wait_type WHEN 'CXPACKET' THEN 'CXPACKET - ' + sp.lastwaittype1 ELSE sp.lastwaittype1 END, lower(er.last_wait_type)) --Lowercase denotes it's not currently waiting, also noted by a wait time of 0.
        , Wait_Time_Sec = Cast(er.wait_time/1000.0 as DEC(20,3))
        , er.wait_resource
        , Duration_Sec = Cast(DATEDIFF(s, er.start_time, GETDATE()) as DEC(20,0))
        , CPU_Sec = Cast(er.cpu_time/1000.0 as DEC(20,3))
        , LogicalReads_K = Cast(er.logical_reads/1000.0 as DEC(20,0))
        , PhysicalReads_K = Cast(er.reads/1000.0 as DEC(20,3))
        , Writes_K = Cast(er.writes/1000.0 as DEC(20,3))
        , [Statement] = SUBSTRING (st.text, er.statement_start_offset/2+1,
            abs(CASE WHEN er.statement_end_offset = -1
                   THEN LEN(CONVERT(NVARCHAR(MAX), st.text)) * 2 
                ELSE er.statement_end_offset END - er.statement_start_offset)/2+1)
        , st.text as Query
        , es.login_time
        , es.host_name
        , program_name = CASE LEFT(es.program_name, 29)
                        WHEN 'SQLAgent - TSQL JobStep (Job '
                            THEN 'SQLAgent Job: ' + (SELECT name FROM msdb..sysjobs sj WHERE substring(es.program_name,32,32)=(substring(sys.fn_varbintohexstr(sj.job_id),3,100))) + ' - ' + SUBSTRING(es.program_name, 67, len(es.program_name)-67)
                        ELSE es.program_name
                        END  
        , es.client_interface_name
        , es.login_name
        , es.status as Session_Status
        , es.total_scheduled_time
        , es.total_elapsed_time
        , er.start_time
        , es.last_request_start_time
        , es.last_request_end_time
        , cursor_statement = cst.text
        , cursor_create = c.creation_time
        , cursor_dormant = c.dormant_duration
        , Req_memory_mb = mg.requested_memory_kb / 1024 
        , Grant_memory_mb = mg.granted_memory_kb / 1024
        , Memory_wait_sec = cast(mg.wait_time_ms / 1000.0 as DEC(20,1))
        , qp.query_plan 
    FROM sys.dm_exec_requests (nolock) er
        INNER JOIN sys.dm_exec_Sessions (nolock) es on er.session_id = es.session_id
        LEFT JOIN sys.databases (nolock) sd on er.database_id = sd.database_id
        INNER JOIN (SELECT session_id, count(1) Threads FROM sys.dm_os_tasks (nolock) GROUP BY session_id) ot on er.session_id = ot.session_id
        LEFT JOIN (SELECT spid, LastWaitType1 = MIN(lastwaittype), LastWaitType2 = MAX(lastwaittype) FROM master.dbo.sysprocesses (nolock) sp WHERE waittime > 0 AND lastwaittype <> 'cxpacket' GROUP BY spid) sp ON er.session_id = sp.spid
        LEFT JOIN (SELECT spid, RunningThreads = COUNT(1) FROM master.dbo.sysprocesses (nolock) sp WHERE waittime = 0 GROUP BY spid) rsp ON er.session_id = rsp.spid
        LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt WHERE wt.blocking_session_id <> wt.session_id GROUP BY session_id) wt ON er.session_id = wt.session_id 
        LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb1 ON wt.blocking_session_id = hb1.session_id 
        LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb2 ON hb1.blocking_session_id = hb2.session_id 
        LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb3 ON hb2.blocking_session_id = hb3.session_id 
        LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb4 ON hb3.blocking_session_id = hb4.session_id 
        LEFT JOIN (SELECT session_id, max(blocking_session_id) blocking_session_id FROM sys.dm_os_waiting_tasks (nolock) wt GROUP BY session_id) hb5 ON hb4.blocking_session_id = hb5.session_id 
        LEFT JOIN sys.dm_exec_cursors(0) c ON er.session_id = c.session_id
        LEFT JOIN sys.dm_exec_query_memory_grants (nolock) mg ON er.session_id = mg.session_id
        CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) AS st  
        OUTER APPLY sys.dm_exec_sql_text(c.sql_handle) AS cst
        CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) qp
    WHERE er.session_id <> @@SPID
        --AND es.host_name like '%%'
        --AND er.session_id = 63
    ORDER BY /*35 desc,*/ er.percent_complete DESC, er.cpu_time DESC, er.session_id
    

    в BlockedBy находится SPID аналогичного апдейта
    5 дек 13, 16:25    [15247637]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Glory
    Member

    Откуда:
    Сообщений: 104760
    abrashka
    в BlockedBy находится SPID аналогичного апдейта

    И за какой же ресурс они спорят ?
    5 дек 13, 16:26    [15247652]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 37069
    План апдейта покажите.

    FILLFACTOR = 10? O_o
    5 дек 13, 16:43    [15247822]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    abrashka
    Member

    Откуда:
    Сообщений: 517
    Гавриленко Сергей Алексеевич,

    Картинка с другого сайта.
    5 дек 13, 16:56    [15247952]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    abrashka
    Member

    Откуда:
    Сообщений: 517
    abrashka,

    FILLFACTOR вроде специально понизили, чтоб каждая строка занимала отдельную страницу, чтоб избежать лишних блокировок, говорят, что частично помогло
    5 дек 13, 16:58    [15247961]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Гавриленко Сергей Алексеевич
    Member

    Откуда: Moscow
    Сообщений: 37069
    Ваша проблема в скане. FF имеет смысл понижать, если у вас апдейт может привести к сплиту страницы. Проблему с блокировками это не решит вообще никак.

    Сообщение было отредактировано: 5 дек 13, 17:16
    5 дек 13, 17:15    [15248106]     Ответить | Цитировать Сообщить модератору
     Re: Помогите плз разобраться с блокировками  [new]
    Crimean
    Member

    Откуда:
    Сообщений: 13148
    делаем пример с данными, все те же 14 строк

    create schema [Infra]
    go
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [Infra].[ContextRecords](
    	[crLocalId] [int] IDENTITY(1,1) NOT NULL,
    	[crContextID] [varchar](50) NOT NULL,
    	[crForeignID] [varchar](50) NULL,
    	[crRecord] [varbinary](max) NULL,
     CONSTRAINT [pk_ContextRecords_c_LocalId] PRIMARY KEY CLUSTERED 
    (
    	[crLocalId] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = OFF, FILLFACTOR = 10) ON [PRIMARY],
     CONSTRAINT [uc_ContextRecords_nc_ContextID] UNIQUE NONCLUSTERED 
    (
    	[crContextID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING ON
    GO
    
    
    
    
    insert into [Infra].[ContextRecords](	[crContextID] ,	[crForeignID] ,	[crRecord]  )
    select '01', '01', 0x01	union all
    select '02', '02', 0x02	union all
    select '03', '03', 0x03	union all
    select '04', '04', 0x04	union all
    select '05', '05', 0x05	union all
    select '06', '06', 0x06	union all
    select '07', '07', 0x07	union all
    select '08', '08', 0x08	union all
    select '09', '09', 0x09 union all
    select '10', '10', 0x10	union all
    select '11', '11', 0x11	union all
    select '12', '12', 0x12	union all
    select '13', '13', 0x13	union all
    select '14', '14', 0x14	
    go
    


    в одной квере

    -- select top 20 * from [Infra].[ContextRecords] 
    begin tran
    -- select @@trancount
    
    declare @ContextId0 [varchar](50) 
    set @ContextId0 = '01'
    
    declare @ContextData0 [varbinary](max) 
    set @ContextData0 = 0x01
    
    UPDATE [Infra].[ContextRecords] 
    SET [crRecord]=@ContextData0 
    WHERE [crContextID]=@ContextId0;  
    
    commit
    


    во второй квере

    -- select top 20 * from [Infra].[ContextRecords] 
    begin tran
    -- select @@trancount
    
    declare @ContextId0 [varchar](50) 
    set @ContextId0 = '02'
    
    declare @ContextData0 [varbinary](max) 
    set @ContextData0 = 0x02
    
    UPDATE [Infra].[ContextRecords] 
    SET [crRecord]=@ContextData0 
    WHERE [crContextID]=@ContextId0;  
    
    commit
    


    разумеется, выполняем по очереди (разумеется, "построчно"). и - о ужас - оно выполняется без ожидания чего либо
    так что вы меняете одну и ту же запись конкурентно. попробуйте на примере - все ясно станет
    5 дек 13, 17:17    [15248119]     Ответить | Цитировать Сообщить модератору
    Все форумы / Microsoft SQL Server Ответить