Hi,

I have a datawarehouse that summarises a POS solution' daily
transactions. Frequently (1-2 in 7) when building this warehouse I
receive the message. 'Deadlock occurred and you are the deadlock
victim.' This seems to regardless of other database activity.

This has caused me some pain in resolving as I am selecting all data
with the 'WITH (NO LOCK) clause and in no circumstances am I updating
any data in the POS database.

The POS database allows for a distinct product (PRODUCTID = 1) to be
sold in different regions. The shop/location that is selling this
product determines the region that the product is sold in. The region
that the product is sold in defines its attributes ie Sell Price,
Description. This makes the whole things dynamic and has to be resolved
at run time by use of a SQL function.

Ie to join to the product from the transaction line table -

Select l.*, t.regionid from transline l
Left outer join table_product t on
t.productid = l.itemid and
t.regionid = fn_getTheRegionForThisProd( SiteID, ProductID)

There are nested functions within these functions too.

This structure is pretty much the same for nearly all components of the
database - ie Services, Employees etc.

I could not determine the cause of my locks at all. (Trying to see the
locks in Enterprise Manager only gave me a Lock Request time out!!!).
When using tools such as sp_who_3 to try and see the activity of the
blocked transactions on the database the input buffer was exclusively
'Create Function ETC" and this got me thinking that the functions
may be the cause of my deadlocks.

I then considered the size of the transaction line table for the POS
solution - at some Head Office installations it is up to 4500000 rows
and decided that resolving the regions per transaction line was very
expensive anyway and decided to create some stored procedures that ran
before I created the transaction lines in the datawarehouse. These
procedures resolved every possible combination of product/service etc
for each site and placed these in tables which I can then join when
creating my transaction table on product and site name with no need for
resolving the region etc as this is already done. (I need to know the
region still for my OLAP cubes which hang off the datawarehouse and I
just grab it from the product-site table)

Ie

Select l.*, t.productregionid from transline l
Left outer join new_table_product t on
t.productid = l.itemid and
t.siteid = l.siteid


So hey presto the build time has come down from 50 minutes to 1 hr per
warehouse to around 20 mins (including running the procedures to create
my products/services by site) and NO more deadlocks.

YAY

This has made me and our customers quite happy but my question is this
- could the creation of the functions be causing the deadlocks?

Is this possible as I can think of no other cause?

Any ideas appreciated...

Thank you

Help! Users can't connect to database
Bring back Query Analyser
Default constraints in SQL Server 2000
UNION problem
Optimize the Query