HELP! Is this a potential locking nightmare??

The following table is used to store the points earned each month by
salesmen. Items
purchased by customers on-line all have points assigned to them that are
used for this
purpose. We used to add to this table as each customers application was
completed.
For some reason we have been asked to make it realtime. The work is mostly
done but
I have an issue. As each item is being added/changed/deleted on customers
orders,
these points are accumulated into the corresponding row in this table. So,
if
100 customers purchasing from the same salesman are on-line at the same time
is
this going to be a potential locking issue? These updates are done within a
transaction. Since the sales structure does have an "upline" type of
structure,
these points have to trickle up to several peoples entries in an
all-or-nothing fashion.
This is supposed to roll out soon(tomorrow). I'm worried that once we
really get traffic hitting this that
our server is going to have issues.
CREATE TABLE [dbo].[tblMonthlyPoints](
[lngSalesPersonID] [int] NOT NULL,
[intMonth] [tinyint] NOT NULL,
[intYear] [int] NOT NULL,
[decPoints] [decimal](19, 4) NOT NULL CONSTRAINT
[DF_tblMonthlyPoints_decPoints] DEFAULT (0),
CONSTRAINT [PK_tblMonthlyPoints] PRIMARY KEY CLUSTERED
(
[lngSalesPersonID] ASC,
[intMonth] ASC,
[intYear] ASC
) ON [PRIMARY]
) ON [PRIMARY]On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:

>We used to add to this table as each customers application was
>completed.
>For some reason we have been asked to make it realtime.
Hi Tim,
Have you considered storing the raw data (i.e. the individual sales)
instead of the aggregated monthly totals? I usually prefer to work that
way, since it makes recovery after errors so much easier.
The table you posted could then be replaced with a view.

>if
>100 customers purchasing from the same salesman are on-line at the same tim
e
>is
>this going to be a potential locking issue?
Yes. Based on your table structure, registering the purchase would
require an exclusive lock. It will be a row lock, since the primary key
can be used to locate the row to be updated - but if several connections
want to update the same row (i.e. same salesman, same month, same year),
they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
run into deadlocks).

> These updates are done within a
>transaction. Since the sales structure does have an "upline" type of
>structure,
>these points have to trickle up to several peoples entries in an
>all-or-nothing fashion.
I don't really understand this. Do you mean that you update several rows
in the table, or do yoou mean that one update in the table fires a
trigger that does the trickling-up?
In the former case, try to update all rows in a single update statement:
UPDATE MonthlyPoints
SET Point s = Points + 5
WHERE SalesPersonID IN (123, 456, 7890)
AND Year = 2006
AND Month = 5

>CREATE TABLE [dbo].[tblMonthlyPoints](
> [lngSalesPersonID] [int] NOT NULL,
> [intMonth] [tinyint] NOT NULL,
> [intYear] [int] NOT NULL,
(snip)
Yoou should also consider replacing the individual Month and Year
columns with a single datetime (or smalldatetime) column that holds the
start of the month. Storing year and month as individual columns often
causes more grief than using a single datetime column.
Hugo Kornelis, SQL Server MVP|||Thanks for replying!! I'd given up on this thread...
I understand about updating them all in one statement the problem is the
list of ID's to update has to be created recursively. I know I could use
CTE's but we're not on 2005 just yet...(I am holding my breath here).....
We are storing the raw data already....I have already suggested the view
alternative which I preferred as well. It was shot down by others because I
guess they have some bonus scheme that gets applied as their points increase
( I don't know enough about their scheme yet to disagree ) which is too
complicated to do in that manner. I was just more curious about the
locking issue...I warned them it could be an issue so my hands are clean.
At least the raw data is present on each sales transaction so it can be
reconstructed after the fact "when" I have to fix it.
"Hugo Kornelis" <hugo@.perFact.REMOVETHIS.info.INVALID> wrote in message
news:lmes6291sa5ju81btj1g28oq367c1j052j@.
4ax.com...
> On Thu, 18 May 2006 07:17:26 -0700, "Tim Greenwood" <tim_greenwood A-T
> yahoo D-O-T com> wrote:
>
> Hi Tim,
> Have you considered storing the raw data (i.e. the individual sales)
> instead of the aggregated monthly totals? I usually prefer to work that
> way, since it makes recovery after errors so much easier.
> The table you posted could then be replaced with a view.
>
> Yes. Based on your table structure, registering the purchase would
> require an exclusive lock. It will be a row lock, since the primary key
> can be used to locate the row to be updated - but if several connections
> want to update the same row (i.e. same salesman, same month, same year),
> they'll have to wait. (Or, if your design isn't deadlock-proof, they'll
> run into deadlocks).
>
> I don't really understand this. Do you mean that you update several rows
> in the table, or do yoou mean that one update in the table fires a
> trigger that does the trickling-up?
> In the former case, try to update all rows in a single update statement:
> UPDATE MonthlyPoints
> SET Point s = Points + 5
> WHERE SalesPersonID IN (123, 456, 7890)
> AND Year = 2006
> AND Month = 5
>
> (snip)
> Yoou should also consider replacing the individual Month and Year
> columns with a single datetime (or smalldatetime) column that holds the
> start of the month. Storing year and month as individual columns often
> causes more grief than using a single datetime column.
> --
> Hugo Kornelis, SQL Server MVP|||On Fri, 19 May 2006 15:58:40 -0700, "Tim Greenwood" <tim_greenwood A-T
yahoo D-O-T com> wrote:

>Thanks for replying!! I'd given up on this thread...
>I understand about updating them all in one statement the problem is the
>list of ID's to update has to be created recursively. I know I could use
>CTE's but we're not on 2005 just yet...(I am holding my breath here).....
Hi Tim,
There are many alternatives on SQL Server 2000 as well. The best
alternatives might include a redesign of your table. The most commonly
used method for storing hierarchies is not the must suitable for use in
a relational model. Google for "nested sets model" if you want to find
out the most common alternative.
(snip)
> I
>guess they have some bonus scheme that gets applied as their points increas
e
>( I don't know enough about their scheme yet to disagree ) which is too
>complicated to do in that manner.
I'd like to see more info on that. To me, the words "too complicated"
are like a red rag. <g>
Hugo Kornelis, SQL Server MVP