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

Откуда: Moscow
Сообщений: 1432
Добрый день коллеги!

Возникла ситуация которую лично мне сложно логически объяснить:

Начнем с репро:
IF OBJECT_ID('[dbo].[test_tbl]', 'U') IS NOT NULL DROP TABLE [dbo].[test_tbl]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[test_tbl](
	[field1] int NULL,
	[field2] [varchar](20) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO


select @@VERSION

Microsoft SQL Server 2012 - 11.0.5582.0 (X64)
Feb 27 2015 18:10:15
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


SELECT
      CAST(@@OPTIONS AS VARCHAR(20)) AS "@@OPTIONS",
      CAST(SIGN(@@OPTIONS & 1) AS CHAR) AS "DISABLE_DEF_CNST_CHK",
      CAST(SIGN(@@OPTIONS & 2) AS CHAR) AS "IMPLICIT_TRANSACTIONS" ,
      CAST(SIGN(@@OPTIONS & 4) AS CHAR) AS "CURSOR_CLOSE_ON_COMMIT" ,
      CAST(SIGN(@@OPTIONS & 8) AS CHAR) AS "ANSI_WARNINGS" ,
      CAST(SIGN(@@OPTIONS & 16) AS CHAR) AS "ANSI_PADDING" ,
      CAST(SIGN(@@OPTIONS & 32) AS CHAR) AS "ANSI_NULLS" ,
      CAST(SIGN(@@OPTIONS & 64) AS CHAR) AS "ARITHABORT" ,
      CAST(SIGN(@@OPTIONS & 128) AS CHAR) AS "ARITHIGNORE" ,
      CAST(SIGN(@@OPTIONS & 256) AS CHAR) AS "QUOTED_IDENTIFIER" ,
      CAST(SIGN(@@OPTIONS & 512) AS CHAR) AS "NOCOUNT" ,
      CAST(SIGN(@@OPTIONS & 1024) AS CHAR) AS "ANSI_NULL_DFLT_ON" ,
      CAST(SIGN(@@OPTIONS & 2048) AS CHAR) AS "ANSI_NULL_DFLT_OFF" ,
      CAST(SIGN(@@OPTIONS & 4096) AS CHAR) AS "CONCAT_NULL_YIELDS_NULL" ,
      CAST(SIGN(@@OPTIONS & 8192) AS CHAR) AS "NUMERIC_ROUNDABORT" ,
      CAST(SIGN(@@OPTIONS & 16384) AS CHAR) AS "XACT_ABORT",
      CONVERT(VARCHAR(255), CONNECTIONPROPERTY('net_transport')) AS "net_transport",
      CONVERT(VARCHAR(255), CONNECTIONPROPERTY('protocol_type')) AS "protocol_type",
      CONVERT(VARCHAR(255), CONNECTIONPROPERTY('auth_scheme')) AS "auth_scheme",
      CONVERT(VARCHAR(255), CONNECTIONPROPERTY('physical_net_transport')) AS "physical_net_transport"


@@OPTIONS DISABLE_DEF_CNST_CHK IMPLICIT_TRANSACTIONS CURSOR_CLOSE_ON_COMMIT ANSI_WARNINGS ANSI_PADDING ANSI_NULLS ARITHABORT ARITHIGNORE QUOTED_IDENTIFIER NOCOUNT ANSI_NULL_DFLT_ON ANSI_NULL_DFLT_OFF CONCAT_NULL_YIELDS_NULL NUMERIC_ROUNDABORT XACT_ABORT net_transport protocol_type auth_scheme physical_net_transport
22392 0 0 0 1 1 1 1 0 1 1 1 0 1 0 1 TCP TSQL SQL TCP


запрос 1
declare @id int = 1
while @id < 100000 begin
     insert into [dbo].[test_tbl] values (@id, cast(@id as varchar(20)))
     set @id += 1
end
go


время выполнения 00:01:17



запрос 2
declare @id int = 1
begin tran
while @id < 100000 begin
     insert into [dbo].[test_tbl] values (@id, cast(@id as varchar(20)))
     set @id += 1
end

commit
go

время выполнения 00:00:02


В целом видно что при выполнении первого запроса сессия получает блокировку IX на таблицу, IX на страницы данных и X на строки при вставке. Все непотребство происходит в цикле соответственно блокировки с намерением на объект и станицы запрашиваются множество раз.

В случае с вторым запросом сессия удерживает блокировки до фиксации транзакции что в целом логично.

Первый вопрос: почему настолько существенная такая разница в скорости работы запроов?

Ситуация кардинально меняется если запрос выполняется со стороны Oracle сервера, первый запрос отрабатывает быстро, в отличие от второго когда транзакция начинает удерживать блокировки до фиксации.

Да вот еще данные:
запрос со стороны sql server
--сессия тестового запроса
select client_version, client_interface_name, is_user_process, text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, transaction_isolation_level, lock_timeout, deadlock_priority from sys.dm_exec_sessions where session_id in (select @@SPID)


client_version client_interface_name is_user_process text_size language date_format date_first quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null transaction_isolation_level lock_timeout deadlock_priority
7 .Net SqlClient Data Provider 1 2147483647 us_english mdy 7 1 1 1 0 1 1 1 1 2 -1 0


--сессия oracle

select client_version, client_interface_name, is_user_process, text_size, language, date_format, date_first, quoted_identifier, arithabort, ansi_null_dflt_on, ansi_defaults, ansi_warnings, ansi_padding, ansi_nulls, concat_null_yields_null, transaction_isolation_level, lock_timeout, deadlock_priority from sys.dm_exec_sessions where session_id in (select 76) 


client_version client_interface_name is_user_process text_size language date_format date_first quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null transaction_isolation_level lock_timeout deadlock_priority
4 ODBC 1 2147483647 us_english mdy 7 1 0 1 0 1 1 1 1 2 -1 0



Оракловый сервер находится далеко, предполагаю что причина в затратах на маршрутизацию и сам оракловый сервер посылает запросы с задержкой

из sys.dm_tran_active_transaction в ходе выполнения запроса 1 с ораклового сервера видно что он использует неявные транзакции

Вопрос два: Есть ли у кого идеи в какую сторону можно посмотреть для понимания ситуации почему в случае запросов с удаленного сервера через ODBC драйвер
12 май 16, 11:41    [19162673]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1432
это чисто тестовая среда, в реале все намного сложнее в плане вставки в таблицу и выполнения запросов с ораклового сервера.

Просто для чистоты эксперимента если сейчас вставка 100000 строк в маленькую табличку занимает существенное время, не представляю что будет происходить на продуктиве когда в цикле необходимо будет дергать процедурку которая выполняет более сложную логику
12 май 16, 11:44    [19162694]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
o-o
Guest
felix_ff
почему настолько существенная такая разница в скорости работы запроов

а вы замерьте, сколько ушло в лог в первом случае и во втором.
в одной транзакции все добро пойдет в лог *на диск* только по причине заполнения буфера лога,
был бы он неограничен, и вовсе 1 раз бы по коммиту на диск все сбросилось.
а в цикле вы открывете 100000 транзакций,
на каждую идет минимум 3 записи в лог: начало транзакции, содержимое, коммит.
и все это 100000 раз сбрасывается на диск.
12 май 16, 11:52    [19162771]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
Владислав Колосов
Member

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

вы забыли модель восстановления указать.
12 май 16, 14:26    [19163858]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
o-o
Guest
Владислав Колосов
felix_ff,
вы забыли модель восстановления указать.

это вы в порядке издевательства спрашиваете
или считаете, что модель восстановления как-то повлияет на скорость
вот этого самого построчного INSERT INTO .. VALUES, который в любой модели fully logged?
12 май 16, 15:41    [19164496]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
felix_ff
Member

Откуда: Moscow
Сообщений: 1432
Владислав Колосов
felix_ff,

вы забыли модель восстановления указать.


модель восстановления SIMPLE

но как и сказал o-o не вижу причин скорости обработки запросов именно в ней.

В принципе ситуация с большим числом открытия/фиксации транзакций в цикле была бы понятна, что расходы ресурсов на запись в журнал транзакций имеют место быть,
если бы ситуация которую я описал в случае вызова запросов с удаленного oracle сервера не была бы обратной.

Запрос который по идеи порождает огромное число транзакций для каждого insert values со стороны Oracle выполняется быстрее, нежели открытие явной транзакции и удержание блокировок до момента фиксации.
Складывается впечатление что расходы но удержание блокировок в памяти (и их постепенное увеличение) получаются дорогостоящими. Ну или у меня лыжи не едут совсем.
12 май 16, 16:58    [19165111]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
invm
Member

Откуда: Москва
Сообщений: 9396
felix_ff
если бы ситуация которую я описал в случае вызова запросов с удаленного oracle сервера не была бы обратной.
Oracle (или ODBC по собственной инициативе) вполне может работать в режиме impicit_transactions on. Проверьте.
12 май 16, 17:03    [19165158]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
Владислав Колосов
Member

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

автор
Запрос который по идеи порождает огромное число транзакций для каждого insert values со стороны Oracle

Почему Вы решили, что он создает огромное число транзакций? Оракл действительно может открывать неявно или явно общую транзакцию.
12 май 16, 17:24    [19165295]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
Konst_One
Member

Откуда:
Сообщений: 11538
оракл без commit ничего не делает всегда!
13 май 16, 11:51    [19168141]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
\0_|_
Guest
Konst_One,

а какая СУБД делает без commit?
13 май 16, 17:44    [19170333]     Ответить | Цитировать Сообщить модератору
 Re: блокировки и атомарность  [new]
o-o
Guest
\0_|_
Konst_One,

а какая СУБД делает без commit?

Имелось в виду, что в Оракле нет autocommit.
Т. е. пока явно его не потребуешь сделать, коммитить не будет
13 май 16, 19:09    [19170597]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить