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

Откуда: Москва
Сообщений: 4927
Добрый день.

Вкратце. Есть некие "позиции" и журнал транзакций. Каждая транзакция влияет на какую то определенную позицию, меняя ее на некий $ Amount (в долларах). В таблице позиций несколько млн строк (10 partitions), и в таблице транзакций - сколько то там милиардов строк (10 partitions). Почему то у нас при JOIN позиций к этим транзакциям получаются какие то дубликаты. Мы наблюдали за этим делом несколько месяцев и нашли 1 интересный случай. Некая позиция и у нее только одна транзакция. Транзакции никогда не меняются - мы их всегда только INSERT в таблицы. Все таблицы с позициями - у всех PRIMARY KEY, собственно которые используются во всех JOIN аккуратно (даже с некими дополнительными условиями AND). Т.е. все это хозяйство должно возвращать 1 строку. Так и происходит если запустить вот прямо щас. Но! Мы добавили логи, и выяснили, что когда сервер загружен, то строка посчиталась дважды во время GROUP BY.

Подробнее. Вот та одна единственна транзакция на позицию 2332566, и те наши логи которые показывают что когда суммируешь все транзакции по позиции 2332566, то там две строки. Если запустить и посмотреть сколько там транзакций - то видно что она там одна и одна была всегда...
select * from PPSLedger where iLedgerTypeId=23 and  ippspositionid in  (2332566)
select * from ppspositionrecon2 where ippspositionid in  (2332566) 

(рисуночек приложен)
самое интересное что это все происходит во время когда вставляются новые транзакции,
но этот баг проявляется чаще всего именно на тех позициях которые несколько месяцев и не менялись

что это может быть?

		CREATE TABLE dbo.#PositionSystemQuantity( 
			iPPSPositionId	INT NOT NULL
		,	dQuantity		DECIMAL(19,5) NULL
		,	tiPartition		TINYINT NOT NULL 
			PRIMARY KEY CLUSTERED (iPPSPositionId, tiPartition)
		)
		--CREATE UNIQUE CLUSTERED INDEX SysQtyIdx ON dbo.#PositionSystemQuantity(iPPSPositionId, tiPartition)
		
		-- 1 - use flow table; 0 - use ledger entries (better to use 0 for more correct calculations, 1 is introduced for potentially better performance)
		IF @UseFlowTable = 1
		BEGIN
			PRINT '...'
		END ELSE BEGIN
			
			CREATE TABLE dbo.#PosToCalcuate (
				  iPPSPositionId INT NOT NULL PRIMARY KEY ------ <<<<<<<<<<<<<<<<<<
				, tiPartition	TINYINT NOT NULL
				, dtForDate		DATE NOT NULL
				, dQuantity		DECIMAL(19,5)
				, mTotalCost	MONEY 
				, iLedgerIdMax	BIGINT
				, iLedgerIdMin	BIGINT
				, dQuantityNeg		DECIMAL(19,5)
				, dQuantityPos		DECIMAL(19,5)
				, dQuantityAbs		DECIMAL(19,5)
			)
			
			-- only for history:
			INSERT INTO dbo.#PosToCalcuate (iPPSPositionId, dtForDate, tiPartition)
			SELECT R.iPPSPositionId, R.dtForDate, R.tiPartition
			FROM dbo.#PositionsToReconLocal A
			JOIN dbo.PPSPositionRecon R (NOLOCK) ON A.iPPSPositionId = R.iPPSPositionId AND A.tiPartition = R.tiPartition
			
			--EXEC dbo.cfn_API_Report_PositionQuantity;
			BEGIN --- API replacement code 
				DECLARE @LedgerTypeIdAsset INT = 23
			
				SELECT X.iPPSPositionID, X.tiPartition
				 , SUM(Lr.dQuantity) * P.iType as dQuantity
				 , SUM(Lr.mAmount) * P.iType as mTotalCost
				 , MAX(Lr.iPPSLedgerId) AS iLedgerIdMax
				 , MIN(Lr.iPPSLedgerId) AS iLedgerIdMin
				 , SUM(CASE WHEN Lr.dQuantity < 0 THEN Lr.dQuantity ELSE 0 END) AS dQuantityNeg
				 , SUM(CASE WHEN Lr.dQuantity > 0 THEN Lr.dQuantity ELSE 0 END) AS dQuantityPos
				 , COUNT(*) AS dQuantityAbs ------ <<<<<<<<<<<<<<<<<<
				INTO dbo.#PosToCalcuate_LedgersFlow
				FROM dbo.#PosToCalcuate X
				JOIN dbo.PPSLedger Lr (NOLOCK) -- CFN-6091
							ON Lr.iPPSPositionId = X.iPPSPositionID  ------ <<<<<<<<<<<<<<<<<<
							AND Lr.tiPartition = X.tiPartition
							AND Lr.dtTradeDate <= X.dtForDate 
							AND Lr.iLedgerTypeId = @LedgerTypeIdAsset 
				JOIN dbo.PPSPosition P (NOLOCK) ON P.iPPSPositionId = X.iPPSPositionId ------ <<<<<<<<<<<<<<<<<<
                                    AND P.tiPartition = X.tiPartition
				GROUP BY X.iPPSPositionID, X.tiPartition, P.iType
				
				CREATE UNIQUE CLUSTERED INDEX IX_TMP_PosToCalcuate_LedgersFlow_CL ON dbo.#PosToCalcuate_LedgersFlow (iPPSPositionID, tiPartition)

 				UPDATE X SET 
 					dQuantity = U.dQuantity
 				,	mTotalCost = U.mTotalCost
 				,	iLedgerIdMax = U.iLedgerIdMax
 				,	iLedgerIdMin = U.iLedgerIdMin
				,	dQuantityNeg = U.dQuantityNeg
				,	dQuantityPos = U.dQuantityPos
				,	dQuantityAbs = U.dQuantityAbs
				FROM dbo.#PosToCalcuate X
				JOIN dbo.#PosToCalcuate_LedgersFlow U ON U.iPPSPositionId = X.iPPSPositionID AND U.tiPartition = X.tiPartition
 	
			END --- API replacement code
			
			INSERT INTO dbo.#PositionSystemQuantity (iPPSPositionId, dQuantity, tiPartition)
			SELECT X.iPPSPositionId, X.dQuantity, P.tiPartition 
			FROM dbo.#PosToCalcuate X
			JOIN dbo.PPSPosition P (NOLOCK) ON P.iPPSPositionId = X.iPPSPositionId AND P.tiPartition = X.tiPartition

			IF @WorkflowId > 0 EXEC dbo.cfn_Workflow_ProgressSet @WorkflowID=@WorkflowID, @iProgress = 0.28 
		END 


DBCC - пробовал не помогает (хотя может не так запускал, или не то).

Microsoft SQL Server 2008 R2 (SP1) - 10.50.2796.0 (X64) 
	Dec  9 2011 11:27:20 
	Copyright (c) Microsoft Corporation
	Enterprise Edition (64-bit) on Windows NT 6.0 <X64> (Build 6002: Service Pack 2) (VM)

Microsoft Windows NT 6.1 (7601)
10.50.2772.0
Memory - 524279 (MB)
Processors - 64
Is Clustered = True


К сообщению приложен файл. Размер - 12Kb
22 янв 12, 07:05    [11946880]     Ответить | Цитировать Сообщить модератору
 Re: Одна и та же строка посчиталась дважды во время GROUP BY  [new]
ChA
Member

Откуда: Москва
Сообщений: 11319
NOLOCK не гарантирует целостности данных в течении выполнения запроса. Используйте либо более высокий уровень изоляции транзакций, либо используйте снапшоты.
22 янв 12, 10:34    [11946986]     Ответить | Цитировать Сообщить модератору
 Re: Одна и та же строка посчиталась дважды во время GROUP BY  [new]
Павел-П
Guest
BusyMan,

Почитайте вот эту вот статью. Думаю, она вам все объяснит.
https://www.sql.ru/subscribe/2006/323.shtml

"запрос с подсказкой NOLOCK может возвратить ту же самую строку несколько раз, и это должно наводить нас на размышления по поводу противоречивых чтений"
22 янв 12, 20:07    [11948330]     Ответить | Цитировать Сообщить модератору
 Re: Одна и та же строка посчиталась дважды во время GROUP BY  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
Павел-П
"запрос с подсказкой NOLOCK может возвратить ту же самую строку несколько раз, и это должно наводить нас на размышления по поводу противоречивых чтений"

;))) да и READ COMMITTED может вернуть вам одну и туже строку несколько раз из-за несогласованности данных
22 янв 12, 20:17    [11948368]     Ответить | Цитировать Сообщить модератору
 Re: Одна и та же строка посчиталась дважды во время GROUP BY  [new]
BusyMan
Member

Откуда: Москва
Сообщений: 4927
Павел-П
BusyMan,

Почитайте вот эту вот статью. Думаю, она вам все объяснит.
https://www.sql.ru/subscribe/2006/323.shtml

"запрос с подсказкой NOLOCK может возвратить ту же самую строку несколько раз, и это должно наводить нас на размышления по поводу противоречивых чтений"


Спасибо. Это именно то что надо.
Особенное спасибо что статья на английском языке, ибо начальство русского не знает) хоть переводить не придется)
22 янв 12, 21:09    [11948500]     Ответить | Цитировать Сообщить модератору
 Re: Одна и та же строка посчиталась дважды во время GROUP BY  [new]
Павел-П
Guest
Knyazev Alexey,

В статье указанной по ссылке утверждение проверялось только для Read uncommitted.
Вы можете эскпериментально подтвердить те же результыаты для Read uncommitted? Ну или хотя бы дать ссылку, где эти результаты подтверждаются кем-нибудь еще?
24 янв 12, 21:57    [11964216]     Ответить | Цитировать Сообщить модератору
 Re: Одна и та же строка посчиталась дважды во время GROUP BY  [new]
step_ks
Member

Откуда:
Сообщений: 936
Timebomb - Consistency problem with READ COMMITTED; why you still multi-count or no count rows
24 янв 12, 23:01    [11964434]     Ответить | Цитировать Сообщить модератору
 Re: Одна и та же строка посчиталась дважды во время GROUP BY  [new]
invm
Member

Откуда: Москва
Сообщений: 9842
Павел-П
Вы можете эскпериментально подтвердить те же результыаты для Read uncommitted?
Наверное для read committed? Пожалуйста:
+ Создаем объекты
use tempdb;
go
create function dbo.ufnDelay
(
 @Delay int
)
returns int
as
begin
 declare @i int = 0;
 
 while @i < @Delay
  select @i += 1;
   
 return 1;
end;
go
create table dbo.Test
(
 pk int not null primary key clustered,
 grp_id int not null,
 delay int,
 dummy char(8000) default 'a'
);
go
insert into dbo.Test
 (pk, grp_id, delay)
values
 (1, 1, 0),
 (2, 2, 50000000),
 (3, 3, 0);

В первой сесси:
use tempdb;
go
set transaction isolation level read committed;
begin tran;
select grp_id, count(*) from dbo.Test with (rowlock) where dbo.ufnDelay(delay) = 1 group by grp_id;
commit;
Быстренько переключаемся на другую сессию и:
use tempdb;
go
update dbo.Test
 set
  pk = 4,
  grp_id = 3
where
 pk = 1;
Возвращаемся к первой и смотрим на результат.
+ Чистим за собой
drop table dbo.Test;
drop function dbo.ufnDelay;
24 янв 12, 23:05    [11964445]     Ответить | Цитировать Сообщить модератору
 Re: Одна и та же строка посчиталась дважды во время GROUP BY  [new]
Knyazev Alexey
Member

Откуда: Екб -> Мск
Сообщений: 10233
Блог
+ выдержка из БОЛ (http://msdn.microsoft.com/ru-ru/library/ms190805.aspx)

Транзакции, работающие на уровне изоляции READ COMMITTED, используют совмещаемые блокировки, 
однако блокировки строк и страниц снимаются после чтения строки. 
В любом случае, если во время сканирования индекса другой пользователь изменит ключевой столбец индекса для строки,
считывание которой происходит в данный момент, причем строка была перемещена в позицию, 
до которой операция сканирования еще не дошла, 
эта строка может появиться повторно. 
Аналогично, если изменение ключа переместило строку в позицию, считывание которой уже прошло, 
то она может не отобразиться.
25 янв 12, 06:54    [11964937]     Ответить | Цитировать Сообщить модератору
 Re: Одна и та же строка посчиталась дважды во время GROUP BY  [new]
Павел-П
Guest
Knyazev Alexey,


Спасибо за более чем детальную информацию
26 янв 12, 00:22    [11972803]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить