"Hal Heinrich" wrote:

> Hi,
>
> During a concurrency test of an Access ADP application, two users were
> running the same report simulataneously.
> This resulted in a SQL Server TSQL proc failing as a deadlock victim with
> error -2147467259.
>
> The proc in question consists of three update statements like this one:
>
> UPDATE dbo.tmpProjectFilterVar
>    SET isIncluded = 0
>    WHERE (isIncluded = 1)
>    AND (spid=@@SPID)
>    AND (groupProjectId IS NULL)
>
> The spid field is part of the tmpProjectFilterVar table to avoid deadlock
> problems. Each user is only interested in records with a matching spid, so no
> user should impact anothers record(s). Will adding ROWLOCK resolve my
> deadlock problem?
>
> UPDATE dbo.tmpProjectFilterVar WITH (rowlock)
>    SET isIncluded = 0
>    WHERE (isIncluded = 1)
>    AND (spid=@@SPID)
>    AND (groupProjectId IS NULL)
>
> Thanks in advance for your help,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.

"R.D" wrote:

> Yup
> This is because adp is a com application and all com objects will
> automatically have serilizable lock on the resourses.So specifying lower leve
> lock is one option to solve such problems
> Regards
> R.D
>
> "Hal Heinrich" wrote:
>
> > Hi,
> >
> > During a concurrency test of an Access ADP application, two users were
> > running the same report simulataneously.
> > This resulted in a SQL Server TSQL proc failing as a deadlock victim with
> > error -2147467259.
> >
> > The proc in question consists of three update statements like this one:
> >
> > UPDATE dbo.tmpProjectFilterVar
> >    SET isIncluded = 0
> >    WHERE (isIncluded = 1)
> >    AND (spid=@@SPID)
> >    AND (groupProjectId IS NULL)
> >
> > The spid field is part of the tmpProjectFilterVar table to avoid deadlock
> > problems. Each user is only interested in records with a matching spid, so no
> > user should impact anothers record(s). Will adding ROWLOCK resolve my
> > deadlock problem?
> >
> > UPDATE dbo.tmpProjectFilterVar WITH (rowlock)
> >    SET isIncluded = 0
> >    WHERE (isIncluded = 1)
> >    AND (spid=@@SPID)
> >    AND (groupProjectId IS NULL)
> >
> > Thanks in advance for your help,
> > Hal Heinrich
> > VP Technology
> > Aralan Solutions Inc.

"Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
news:CA76BF9D-0DB2-4F11-80AF-7273D588DEC6@microsoft.com...
> Hi,
>
> During a concurrency test of an Access ADP application, two users were
> running the same report simulataneously.
> This resulted in a SQL Server TSQL proc failing as a deadlock victim with
> error -2147467259.
>
> The proc in question consists of three update statements like this one:
>
> UPDATE dbo.tmpProjectFilterVar
>   SET isIncluded = 0
>   WHERE (isIncluded = 1)
>   AND (spid=@@SPID)
>   AND (groupProjectId IS NULL)
>
> The spid field is part of the tmpProjectFilterVar table to avoid deadlock
> problems. Each user is only interested in records with a matching spid, so
> no
> user should impact anothers record(s). Will adding ROWLOCK resolve my
> deadlock problem?
>
> UPDATE dbo.tmpProjectFilterVar WITH (rowlock)
>   SET isIncluded = 0
>   WHERE (isIncluded = 1)
>   AND (spid=@@SPID)
>   AND (groupProjectId IS NULL)
>
> Thanks in advance for your help,
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.

"Louis Davidson" wrote:

> Do you have indexes on this table to avoid table scans?  It will have to
> lock all of the rows to scan them to see if the update needs to be done.  If
> you had indexes on spid, it should do it (especially if it was unique)
>
> You might also consider temp tables, as they are process bound, and you
> can't be storing anything permanent.
> --
> ----------------------------------------------------------------------------
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing."
> (Oscar Wilde)
>
> "Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
> news:CA76BF9D-0DB2-4F11-80AF-7273D588DEC6@microsoft.com...
> > Hi,
> >
> > During a concurrency test of an Access ADP application, two users were
> > running the same report simulataneously.
> > This resulted in a SQL Server TSQL proc failing as a deadlock victim with
> > error -2147467259.
> >
> > The proc in question consists of three update statements like this one:
> >
> > UPDATE dbo.tmpProjectFilterVar
> >   SET isIncluded = 0
> >   WHERE (isIncluded = 1)
> >   AND (spid=@@SPID)
> >   AND (groupProjectId IS NULL)
> >
> > The spid field is part of the tmpProjectFilterVar table to avoid deadlock
> > problems. Each user is only interested in records with a matching spid, so
> > no
> > user should impact anothers record(s). Will adding ROWLOCK resolve my
> > deadlock problem?
> >
> > UPDATE dbo.tmpProjectFilterVar WITH (rowlock)
> >   SET isIncluded = 0
> >   WHERE (isIncluded = 1)
> >   AND (spid=@@SPID)
> >   AND (groupProjectId IS NULL)
> >
> > Thanks in advance for your help,
> > Hal Heinrich
> > VP Technology
> > Aralan Solutions Inc.
>
>
>

"Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
news:7F440D21-D873-42FE-A156-D371412E5E05@microsoft.com...

>
> --
> VP Technology
> Aralan Solutions Inc.
>
>
> "Louis Davidson" wrote:
>
>> Do you have indexes on this table to avoid table scans?  It will have to
>> lock all of the rows to scan them to see if the update needs to be done.
>> If
>> you had indexes on spid, it should do it (especially if it was unique)
>>
>> You might also consider temp tables, as they are process bound, and you
>> can't be storing anything permanent.
>> --
>> ----------------------------------------------------------------------------
>> Louis Davidson - http://spaces.msn.com/members/drsql/
>> SQL Server MVP
>> "Arguments are to be avoided: they are always vulgar and often
>> convincing."
>> (Oscar Wilde)
>>
>> "Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
>> news:CA76BF9D-0DB2-4F11-80AF-7273D588DEC6@microsoft.com...
>> > Hi,
>> >
>> > During a concurrency test of an Access ADP application, two users were
>> > running the same report simulataneously.
>> > This resulted in a SQL Server TSQL proc failing as a deadlock victim
>> > with
>> > error -2147467259.
>> >
>> > The proc in question consists of three update statements like this one:
>> >
>> > UPDATE dbo.tmpProjectFilterVar
>> >   SET isIncluded = 0
>> >   WHERE (isIncluded = 1)
>> >   AND (spid=@@SPID)
>> >   AND (groupProjectId IS NULL)
>> >
>> > The spid field is part of the tmpProjectFilterVar table to avoid
>> > deadlock
>> > problems. Each user is only interested in records with a matching spid,
>> > so
>> > no
>> > user should impact anothers record(s). Will adding ROWLOCK resolve my
>> > deadlock problem?
>> >
>> > UPDATE dbo.tmpProjectFilterVar WITH (rowlock)
>> >   SET isIncluded = 0
>> >   WHERE (isIncluded = 1)
>> >   AND (spid=@@SPID)
>> >   AND (groupProjectId IS NULL)
>> >
>> > Thanks in advance for your help,
>> > Hal Heinrich
>> > VP Technology
>> > Aralan Solutions Inc.
>>
>>
>>

"Louis Davidson" wrote:

> > Temporary tables have been considered, tried, and rejected.
>
> That is fine, though it would be interesting to hear the reasoning :)
>
> > Thank you replying. The table has a an identity primary key, and indexes
> > on
> > two fields not used in the query. The spid field is not unique - it will
> > have
> > very few distinct values.
>
> You should index the spid column most likely.  This will help avoid locks
> from other processes on the rows that are "owned" by a process.  Of course
> the indexes on the other columns may actually hurt you, depending on how
> many rows are in the tables.  If an index is not used in the queries, as I
> am guessing your tables are fairly small, then it is a hindrance because it
> has to be maintainted.
>
> --
> ----------------------------------------------------------------------------
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing."
> (Oscar Wilde)
>
> "Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
> news:7F440D21-D873-42FE-A156-D371412E5E05@microsoft.com...
>
> >
> > --
> > VP Technology
> > Aralan Solutions Inc.
> >
> >
> > "Louis Davidson" wrote:
> >
> >> Do you have indexes on this table to avoid table scans?  It will have to
> >> lock all of the rows to scan them to see if the update needs to be done.
> >> If
> >> you had indexes on spid, it should do it (especially if it was unique)
> >>
> >> You might also consider temp tables, as they are process bound, and you
> >> can't be storing anything permanent.
> >> --
> >> ----------------------------------------------------------------------------
> >> Louis Davidson - http://spaces.msn.com/members/drsql/
> >> SQL Server MVP
> >> "Arguments are to be avoided: they are always vulgar and often
> >> convincing."
> >> (Oscar Wilde)
> >>
> >> "Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
> >> news:CA76BF9D-0DB2-4F11-80AF-7273D588DEC6@microsoft.com...
> >> > Hi,
> >> >
> >> > During a concurrency test of an Access ADP application, two users were
> >> > running the same report simulataneously.
> >> > This resulted in a SQL Server TSQL proc failing as a deadlock victim
> >> > with
> >> > error -2147467259.
> >> >
> >> > The proc in question consists of three update statements like this one:
> >> >
> >> > UPDATE dbo.tmpProjectFilterVar
> >> >   SET isIncluded = 0
> >> >   WHERE (isIncluded = 1)
> >> >   AND (spid=@@SPID)
> >> >   AND (groupProjectId IS NULL)
> >> >
> >> > The spid field is part of the tmpProjectFilterVar table to avoid
> >> > deadlock
> >> > problems. Each user is only interested in records with a matching spid,
> >> > so
> >> > no
> >> > user should impact anothers record(s). Will adding ROWLOCK resolve my
> >> > deadlock problem?
> >> >
> >> > UPDATE dbo.tmpProjectFilterVar WITH (rowlock)
> >> >   SET isIncluded = 0
> >> >   WHERE (isIncluded = 1)
> >> >   AND (spid=@@SPID)
> >> >   AND (groupProjectId IS NULL)
> >> >
> >> > Thanks in advance for your help,
> >> > Hal Heinrich
> >> > VP Technology
> >> > Aralan Solutions Inc.
> >>
> >>
> >>
>
>
>

"Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
news:243F96A8-DEE8-4FEE-88C1-9F75508B01E6@microsoft.com...
> Two users will never attempt to update the same row because they will
> never
> have the same @@SPID, so it would be best if deadlocks never occurred - as
> opposed to rarely.
> The indexes in question were recommended by SQL Server Profiler, so it is
> more likely that removing them will hurt performance.
> Indexing the spid column ought to degrade performance as it is used to
> disambiguate users - one spid value per user and each user may have
> thousands
> of records. Still, this is preferable to deadlocking.
>
> Again, my question is 'How Do I Avoid Update Deadlock?' Secondarily, 'Will
> ROWLOCK resolve my problem?'
>
> Temporary tables, dbo.#tempTable, do not persist thru to the MS-Access ADP
> reporting engine. Global temporary tables, dbo.##tempTable, reintroduce
> the
> need to distingusih between users. Additionally, making this 'temporary'
> table permanent enables additional SQL Server optimization.
>
> Hal Heinrich
> VP Technology
> Aralan Solutions Inc.
>
> "Louis Davidson" wrote:
>
>> > Temporary tables have been considered, tried, and rejected.
>>
>> That is fine, though it would be interesting to hear the reasoning :)
>>
>> > Thank you replying. The table has a an identity primary key, and
>> > indexes
>> > on
>> > two fields not used in the query. The spid field is not unique - it
>> > will
>> > have
>> > very few distinct values.
>>
>> You should index the spid column most likely.  This will help avoid locks
>> from other processes on the rows that are "owned" by a process.  Of
>> course
>> the indexes on the other columns may actually hurt you, depending on how
>> many rows are in the tables.  If an index is not used in the queries, as
>> I
>> am guessing your tables are fairly small, then it is a hindrance because
>> it
>> has to be maintainted.
>>
>> --
>> ----------------------------------------------------------------------------
>> Louis Davidson - http://spaces.msn.com/members/drsql/
>> SQL Server MVP
>> "Arguments are to be avoided: they are always vulgar and often
>> convincing."
>> (Oscar Wilde)
>>
>> "Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in message
>> news:7F440D21-D873-42FE-A156-D371412E5E05@microsoft.com...
>>
>> >
>> > --
>> > VP Technology
>> > Aralan Solutions Inc.
>> >
>> >
>> > "Louis Davidson" wrote:
>> >
>> >> Do you have indexes on this table to avoid table scans?  It will have
>> >> to
>> >> lock all of the rows to scan them to see if the update needs to be
>> >> done.
>> >> If
>> >> you had indexes on spid, it should do it (especially if it was unique)
>> >>
>> >> You might also consider temp tables, as they are process bound, and
>> >> you
>> >> can't be storing anything permanent.
>> >> --
>> >> ----------------------------------------------------------------------------
>> >> Louis Davidson - http://spaces.msn.com/members/drsql/
>> >> SQL Server MVP
>> >> "Arguments are to be avoided: they are always vulgar and often
>> >> convincing."
>> >> (Oscar Wilde)
>> >>
>> >> "Hal Heinrich" <HalHeinr***@discussions.microsoft.com> wrote in
>> >> message
>> >> news:CA76BF9D-0DB2-4F11-80AF-7273D588DEC6@microsoft.com...
>> >> > Hi,
>> >> >
>> >> > During a concurrency test of an Access ADP application, two users
>> >> > were
>> >> > running the same report simulataneously.
>> >> > This resulted in a SQL Server TSQL proc failing as a deadlock victim
>> >> > with
>> >> > error -2147467259.
>> >> >
>> >> > The proc in question consists of three update statements like this
>> >> > one:
>> >> >
>> >> > UPDATE dbo.tmpProjectFilterVar
>> >> >   SET isIncluded = 0
>> >> >   WHERE (isIncluded = 1)
>> >> >   AND (spid=@@SPID)
>> >> >   AND (groupProjectId IS NULL)
>> >> >
>> >> > The spid field is part of the tmpProjectFilterVar table to avoid
>> >> > deadlock
>> >> > problems. Each user is only interested in records with a matching
>> >> > spid,
>> >> > so
>> >> > no
>> >> > user should impact anothers record(s). Will adding ROWLOCK resolve
>> >> > my
>> >> > deadlock problem?
>> >> >
>> >> > UPDATE dbo.tmpProjectFilterVar WITH (rowlock)
>> >> >   SET isIncluded = 0
>> >> >   WHERE (isIncluded = 1)
>> >> >   AND (spid=@@SPID)
>> >> >   AND (groupProjectId IS NULL)
>> >> >
>> >> > Thanks in advance for your help,
>> >> > Hal Heinrich
>> >> > VP Technology
>> >> > Aralan Solutions Inc.
>> >>
>> >>
>> >>
>>
>>
>>

"Hugo Kornelis" wrote:

> On Fri, 9 Sep 2005 10:30:04 -0700, Hal Heinrich wrote:
>
> >Two users will never attempt to update the same row because they will never
> >have the same @@SPID, so it would be best if deadlocks never occurred - as
> >opposed to rarely.
> (snip)
>
> Hi Hal,
>
> Since there's no index in the spid column, SQL Server will have to check
> all rows before it can determine if it needs them or not - and as such,
> will have to wait until any exclusive locks are lifted. The only wawy to
> prevent deadlocks is to have an index on spid.
>
> In fact, based on your description, I think your best bet is a clustered
> index with spid as the first column. This means that all access to the
> table will be able to use a clustered index seek (yes, seek - not scan)
> and only rows in the "correct" range (i.e. with the correct spid) have
> to be fetched.
>
> Other tips:
>
> - The table name suggests it's a temporary table. If so, why not make it
> a real temporary table (#Tablename)? That way, each connection could get
> it's own version, and there be no interference at all. You'll also be
> able to remove the spid column.
>
> - What's the use for the tmpId IDENTITY column? An identity should only
> be used as a surrogate for the real key, and only if it's beneficial
> (e.g. in foreign key constraints). In your table, I see no real key at
> all (I do hope that you forgot to script the PRIMARY KEY and/or UNIQUE
> constraint, but that you do have one!) - and since temporary tables are
> hardly ever referenced in foreign key constraints, there's not much
> point in havind a surrogate, is there?
>
> >The indexes in question were recommended by SQL Server Profiler, so it is
> >more likely that removing them will hurt performance.
>
> Hmm, the index tuning wizard isn't always right - and it surely is never
> considering how indexes might influence the chance of deadlocks!
>
> >Indexing the spid column ought to degrade performance as it is used to
> >disambiguate users - one spid value per user and each user may have thousands
> >of records. Still, this is preferable to deadlocking.
>
> As I said, make it the first column in the clustered index, and it might
> actually improve performance (though it will hurt performance of
> modifications). Especially if the clustered index is a unique index
> (maybe one that coincides with the natural key for this table?)
>
> >Again, my question is 'How Do I Avoid Update Deadlock?' Secondarily, 'Will
> >ROWLOCK resolve my problem?'
>
> Row-level locking is default behaviour. The ROWLOCK hint is basically a
> no-op.
>
> >Temporary tables, dbo.#tempTable, do not persist thru to the MS-Access ADP
> >reporting engine.
>
> Ah, missed this remark when typing the paragraphs above.
> I don't know ADP too well. Is there no setting that will tell ADP to
> stop disconnecting and reconnecting all the time? Because if you keep
> the transaction open, the temp table should not be dropped.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>

>Hi Hugo,
>
>Thanks for your reply. Here's the DDL for the table in question:
>
>CREATE TABLE dbo.tmpProjectFilterVar
>(  tmpId int NOT NULL IDENTITY,
>   spId int NOT NULL,
>   focusProjectId int NULL,
>   compareProjectId int NULL,
>   isIncluded bit NOT NULL,
>   createDate datetime,
>   groupProjectId int NULL,
>   group1 int NULL,
>   group2 int NULL,
>   group3 int NULL,
>   group4 int NULL,
>   group5 int NULL,
>   PRIMARY KEY (tmpId)
>)
>CREATE NONCLUSTERED INDEX [IX_tmpProjectFilterVar]
>   ON [dbo].[tmpProjectFilterVar]
>   ([focusProjectId] ASC)
>CREATE NONCLUSTERED INDEX [IX_tmpProjectFilterVar_1]
>   ON [dbo].[tmpProjectFilterVar]
>   ([compareProjectId] ASC)
>
>The 'natural' key for this table is groupProjectId - which would be
>focusProjectId, unless it's null, then it'd be compareProjectId. But the way
>this table is populated makes it awkward to insist that groupProjectId be
>non-null. So tmpId is just a garbage key to get me by.

"Hugo Kornelis" wrote:

> On Fri, 9 Sep 2005 16:00:02 -0700, Hal Heinrich wrote:
>
> >Hi Hugo,
> >
> >Thanks for your reply. Here's the DDL for the table in question:
> >
> >CREATE TABLE dbo.tmpProjectFilterVar
> >(  tmpId int NOT NULL IDENTITY,
> >   spId int NOT NULL,
> >   focusProjectId int NULL,
> >   compareProjectId int NULL,
> >   isIncluded bit NOT NULL,
> >   createDate datetime,
> >   groupProjectId int NULL,
> >   group1 int NULL,
> >   group2 int NULL,
> >   group3 int NULL,
> >   group4 int NULL,
> >   group5 int NULL,
> >   PRIMARY KEY (tmpId)
> >)
> >CREATE NONCLUSTERED INDEX [IX_tmpProjectFilterVar]
> >   ON [dbo].[tmpProjectFilterVar]
> >   ([focusProjectId] ASC)
> >CREATE NONCLUSTERED INDEX [IX_tmpProjectFilterVar_1]
> >   ON [dbo].[tmpProjectFilterVar]
> >   ([compareProjectId] ASC)
> >
> >The 'natural' key for this table is groupProjectId - which would be
> >focusProjectId, unless it's null, then it'd be compareProjectId. But the way
> >this table is populated makes it awkward to insist that groupProjectId be
> >non-null. So tmpId is just a garbage key to get me by.
>
> Hi Hal,
>
> If groupProjectId is always equal to either focusProjectId or
> compareProjectId, then why not make it a computed column, then use it as
> PRIMARY KEY?
>
> Here's how I would design the table:
>
> CREATE TABLE dbo.WkProjectFilterVar
> (  SpId int NOT NULL,
>    FocusProjectId int NULL,
>    CompareProjectId int NULL,
>    GroupProjectId AS ISNULL(ISNULL(FocusProjectId, CompareProjectId),0),
>    IsIncluded bit NOT NULL,
>    CreateDate datetime,
>    Group1 int NULL,
>    Group2 int NULL,
>    Group3 int NULL,
>    Group4 int NULL,
>    Group5 int NULL,
>    PRIMARY KEY NONCLUSTERED (GroupProjectId)
> )
> CREATE UNIQUE CLUSTERED INDEX IX_WkProjectFilterVar_2
>   ON dbo.WkProjectFilterVar (SpId, GroupProjectId)
>
> If you actually meant to say that groupProjectId is unique within each
> SpId, but not in the whole table, then drop the unique clustered index
> and change the primary key to
>    PRIMARY KEY CLUSTERED (SpId, GroupProjectId)
>
> Note: the second ISNULL is only there because SQL Server doesn't know
> that either FocusProjectId or CompareProjectId will always be non-NULL,
> but it has to know the column to be non-NULL if you include it in a
> primary key. This is also the reason for choosing ISNULL instead of the
> more standard COALESCE - for some reason, SQL Server marks a computed
> column that uses COALESCE as nullable, even if the last argument in the
> COALESCE can never be NULL.
>
>
> >The table is misnamed, dbo.wkProjectFilterVar, would be a more appropriate
> >name. This table is used to stage data for a report, so it to persist beyond
> >the confines of a single proc. Also debugging is greatly aided by having this
> >table's data available.
> >
> >If I understand your suggestion correctly, I'd replace the existing primary
> >key with PRIMARY KEY (spId, tmpId)?
>
> My suggestion was actually to get rid of the identity column. See the
> suggested layout above.
>
> >Note that all records per user will have the same spid, and each user will
> >have hundreds/thousands of records depending on the report parameters they
> >select.
> >Whenever a report is run, existing records for that spid are deleted, and
> >any records over three days old are also deleted.
>
> I can't make any promises - you'll have to test it in your database. The
> only things I know are that clustering on SpId (as first column) will
> * very probably minimize (and maybe completely eradicate) deadlocks,
> * improve performance for queries that include SpId as search argument,
> * hurt performance of inserts and deletes, since you'll be facing page
> splits.
>
> Test in your environment and see if the overall effect is beneficial or
> detrimental.
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>