Am o problema ciudata, pe o baza de date care se afla in spatele unei aplicatii accesata de multi utilizatori, rulez un select care trebuie sa intoarca niste date, scriptul rulat ieri omite citeva inregistari peste care am dat complet intimplator. Inregistrarile nu lipsesc din datele intoarse in celelalte zile ale saptaminii.

------------------------------------------------------------------------------------------------------

declare

@dataini datetime,

@datafin datetime,

@SiteId int,

@filtruarticol int,

@clasificare int,

@lunacurenta int

 

set @dataini = '20080901'

set @datafin = '20080930'

set @SiteId = 0

set @filtruarticol = 0

set @clasificare = 471

set @lunacurenta =2

DECLARE @filtre int

select @filtre=0

declare @Hierarchy TABLE

(CategoryId int,

ParentId int,CategName varchar (255))

 

 

 

 

 

delete from ___vinzari_costuri

--------------Vanzari

insert into ___vinzari_costuri (

SiteCode, Divizie, Echipa, Agent, DataFact, Scadenta, Partener, Factura, ValFactura, StareDoc,

CodProdus, Produs, UM, Cantit, Greutate, ConversionRate, Paleti, PretIntrare, pretlista, ValidFrom,

PretListaStandart, PretFTVA, TVA, Valoare, DiscLinie, CantitateCompusa, CodUMCompusa, tip_produs,

level1, level2, level3, SyntheticName, ValLstStndrt, ValDiscStd, DiscountDat, MovementSign)

(

select a.SiteCode,

E_Mail as Divizie ,

a.WorkPlace as Echipa,

a.Agent,

a.DocumentDate as DataFact,

a.Scadenta,

a.PartnerName as Partener,

a.Factura,

a.ValFactura,

a.StareDoc,

a.ItemCode as CodProdus,

a.ItemName as Produs,

a.MeasuringUnitCode as UM,

a.Qtty as Cantit,

dbo.GetKG2(a.ItemId, a.Qtty) as Greutate,

d.ConversionRate,

(a.Qtty/d.ConversionRate) as Paleti,

cast(a.pretintrare as decimal(10,2)) as PretIntrare,

a.pretlista,

pdl.ValidFrom,

pdl.Price as PretListaStandart ,

a.UnitPrice as PretFTVA,

a.VAT as TVA,

a.Amount as Valoare,

a.DiscountPercentInvDet as DiscLinie,

a.CantitateCompusa,

a.CodUMCompusa,

a.ItemTypeId as tip_produs ,

b.level1,

b.level2,

b.level3,

c.SyntheticName,

isnull(cast((pdl.Price * a.Qtty) as decimal(10,2)),0) as ValLstStndrt,

isnull(cast(((pdl.Price * a.Qtty) - a.Amount) as decimal(10,2)),0)as ValDiscStd,

isnull(cast ((((pdl.Price * a.Qtty) - a.Amount)/(pdl.Price * a.Qtty)) as decimal(10,4)),0) as DiscountDat,

'Vinzari' as MovementSign

from __total_vanzari a left outer join __ierarhii_articole b on a.ItemId=b.ItemId and b.ItemClassificationId=@clasificare

left join PartnerAddress c on a.DeliveryAddressId = c.PartnerAddressId and a.SiteId = c.SiteId

left join (select * from PriceListDetail where PriceListId = 179) pdl on a.ItemId = pdl.ItemId and a.MeasuringUnitId = pdl.MeasuringUnitId and (ValidFrom <= a.DocumentDate) AND ( (ValidTo IS NULL) OR (ValidTo > a.DocumentDate))

left join __produse_paleti d on a.ItemId = d.ItemId and a.MeasuringUnitId = d.BaseMeasuringUnitId

where a.DocumentDate>=@dataini and a.DocumentDate<=@datafin and a.StareDoc<>'A'

and (isnull(@SiteId,-1)=-1 or a.SiteId = @SiteId)

and (@filtre=0 or a.ItemId in (select ItemId from @Hierarchy H left join repItemXCategory IC on H.CategoryId = IC.ItemCategoryId and IC.ItemId = a.ItemId))

)

 

------------------- Intrari

insert into ___vinzari_costuri (

SiteCode, Divizie, Echipa, Agent, DataFact, Scadenta, Partener, Factura, ValFactura, StareDoc,

CodProdus, Produs, UM, Cantit, Greutate, ConversionRate, Paleti, PretIntrare, pretlista, ValidFrom,

PretListaStandart, PretFTVA, TVA, Valoare, DiscLinie, CantitateCompusa, CodUMCompusa, tip_produs,

level1, level2, level3, SyntheticName, ValLstStndrt, ValDiscStd, DiscountDat, MovementSign)

(

select a.SiteCode,

'Fara Divizie' as Divizie,

'Fara Echipa' as Echipa,

a.Agent,

a.DocumentDate as DataFact,

a.Scadenta,

a.PartnerName as Partener,

a.Factura,

a.ValFactura,

a.StareDoc,

a.ItemCode as CodProdus,

a.ItemName as Produs,

a.MeasuringUnitCode as UM,

a.Qtty as Cantit,

dbo.GetKG2(a.ItemId, a.Qtty) as Greutate,

d.ConversionRate,

(a.Qtty/d.ConversionRate) as Paleti,

a.pretintrare as PretIntrare,

pld.Price as PretListaStandart ,

pld.ValidFrom,

pld.Price as PretListaStandart ,

a.UnitPrice as PretFTVA,

a.VAT as TVA,

a.Amount as Valoare,

a.DiscountPercentInvDet as DiscLinie,

a.CantitateCompusa,

a.CodUMCompusa,

a.ItemTypeId as TipProdus,

b.level1,

b.level2,

b.level3,

StockAdminName,

-- a.RemarkText as TipAchizitie,

isnull(cast((pld.Price * a.Qtty) as decimal(10,2)),0) as ValLstStndrt,

0 as ValDiscStd,

0 as DiscountDat,

'Intrari' as MovementSign

from __total_intrari a

left outer join __ierarhii_articole b on a.ItemId=b.ItemId and b.ItemClassificationId=@clasificare

left join (select ItemId, Price, MeasuringUnitId, ValidFrom from PriceListDetail where ValidTo is null and PriceListId = 179) pld on a.ItemId = pld.ItemId and a.MeasuringUnitId = pld.MeasuringUnitId

left join __produse_paleti d on a.ItemId = d.ItemId and a.MeasuringUnitId = d.BaseMeasuringUnitId

where a.DocumentDate>=@dataini and a.DocumentDate<=@datafin and a.StareDoc<>'A'

and (isnull(@SiteId,-1)=-1 or a.SiteId = @SiteId)

and (@filtruarticol=0 or a.ItemId in (select ItemId from @Hierarchy H left join repItemXCategory IC on H.CategoryId = IC.ItemCategoryId and IC.ItemId = a.ItemId) )

)

----------------------------------------------------------------------------------------------------------

 

Exista posibilitatea ca inregistrarile respective sa fie blocate la momentul rularii de vreun utilizator sau de vreun trigger ... astfel incit sa fie omise ?

 

 

 

 

Nivelul de izolare al tranzactiei sub care ruleaza interogarile poate permite dirty reads (citirea datelor blocate pentru a fi modificate insa nescrise inca in baza de date).
Mai mult, daca este vorba de SQL Server 2005 exista nivelele de izolare Snaphot si Read Committed Snaphot care permit citirea unei versiuni anterioare a unui rand chiar daca acesta este modificat de alta tranzactie.
Puteti schimba comportamentul interogarii insa se va reduce nivelul de concurenta - concret: interogarea va astepta ca eventualele modificari sa fie persistate, iar eventualele modificari pot fi blocate temporar de interogare.

Pentru detalii vedeti subiectul transaction isolation levels in Books Online.

---

Cristian Andrei Lefter, SQL Server MVP
MCT, MCSA, MCDBA, MCAD, MCSD .NET,
MCTS, MCITP - Database Administrator SQL Server 2005
http://sqlserver.ro

S-ar putea ca problema ta sa fie "pamanteana".Daca vrei sa filtrezi toate documentele (where a.DocumentDate>=@dataini and a.DocumentDate<=@datafin) din perioada 1 sept 2008 pana la 30 sept 2008

(

set @dataini = '20080901'

set @datafin = '20080930'
)
trebuie sa folosesti (de exemplu) valoarea '2008-09-30 23:59' pentru @datafin, in caz contrar '20080930' este interpretat de catre SQL Server ca fiind 2008-09-30 00:00 (in ipoteza in care stochezi pentru documente si ora).

Pe setari implicite (iar din scriptul publicat de tine nu reiese posibilitatea existentei altui nivel de izolare) daca o tranzactie blocheaza pt. scriere o resursa ca de ex. o inregistrarea, o pagina, ... atunci un batch care incearca sa citeasca date din resursele blocate va fi pus in asteptare si eventual se poate ajunge si la un deadlock. Suplimentar fata de ce a scris XMLDev nu observ in scriptul tau (de exemplu) optiuni care sa determine comportamentul presupus de tine ca de exemplu: FROM ... tabela WITH(READPAST, READREADCOMMITTED ??? ... 

Vezi daca ieri ai rulat scriptul in aceleasi conditii (---> "isolation level", valori ale parametrilor) ca si pana atunci.

De asemenea vezi daca datele nu s-au schimbat intre timp - de exemplu daca nu s-a sters din inregistrari...