Deadlocking on indexes?

It seems that a common problem we have is deadlocks occurring when one
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
Joel
The order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums.aspx/sql-server/200804/1
|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel
|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.co m...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>
sql

Deadlocking on indexes?

It seems that a common problem we have is deadlocks occurring when one
stored proc is updating a row and another store proc is running a read query
against the same table. They'll often get deadlocked on two of the indexes
for the table.
For example:
SP1 is updating a row in TableA, causing it to get an exclusive lock on
TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
attempts to get a shared lock on TableAIndex1.
Deadlock!
Of course, I can fix this by lowering the isolation level in SP2 to "read
uncommitted", but I'd rather not.
I can normally fix deadlocks by changing the order in which locks are
acquired, but I don't know how to influence the order in which locks on
indexes are acquired. Is it controlled by the order in which the fields are
listed in a select or an update statement? If I standardize the order in
which individual fields are listed whenever I perform a select or update
from a given table, will that effect thr order in which index locks are
acquired?
Thoughts?
JoelThe order in which the columns appear in the query are not tied directly to
how the engine decides to take locks. Why is the query trying to use two
indexes on the same table? Is this two separate queries or is it doing
index intersection? If it is the latter you might want to see if adding the
column to the first index will solve the problem.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||One way to solve this would be to put a copy of the same Select statement
used in the SP1 for the update at the beginning of the SP2. This way, SP2
will have to first acquire the shared lock in the same order as SP1; hence
solving (I hope!) your deadlocking problem.
It has been a long time since the last time that I had to work on a locking
problem but I remember a suggestion whose idea was to put at the beginning
of a SP one or more queries with the purpose of acquiring a list of locks in
the same order as for the other SPs. At first, you might think that making
these queries will have some negative impact on the overall performance but
don't forget that most (if not all) of this stuff must be read from the I/O
and put into memory/buffer anyway. Acquiring all the locks in the exact
right order is more important than to save a few I/O or a few CPU cycles.
Don't know if there is a better way of solving this kind of problem.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joel
In addition to others , if you run SQL Server 2005 , let DTA (Tunning
Advisor) to reccomed you what indexes are missed.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Joe,
many things just have said by others like replications, keep only used index,
etc, but I've said one more: FILLFACTOR.
If you have a very busy OLTP database, you can controll lock at least to
minimun with appropriate fillfactor.
I administrate many OLTP servers anda database and after read many articles
and try to find a baseline to controll lock. Finally, I've used these
templates with excelent result : Recreate every clustered index with
fillfactor 80% and recreate every nonclustered index with 90%.
You can increase or decrease this value that depends if you have updated
tables that need more, but stay with a little less in clustered than
noclustered.
Try this !
Kris
Joel Lyons wrote:
>It seems that a common problem we have is deadlocks occurring when one
>stored proc is updating a row and another store proc is running a read query
>against the same table. They'll often get deadlocked on two of the indexes
>for the table.
>For example:
>SP1 is updating a row in TableA, causing it to get an exclusive lock on
>TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
>attempts to get a shared lock on TableAIndex1.
>Deadlock!
>Of course, I can fix this by lowering the isolation level in SP2 to "read
>uncommitted", but I'd rather not.
>I can normally fix deadlocks by changing the order in which locks are
>acquired, but I don't know how to influence the order in which locks on
>indexes are acquired. Is it controlled by the order in which the fields are
>listed in a select or an update statement? If I standardize the order in
>which individual fields are listed whenever I perform a select or update
>from a given table, will that effect thr order in which index locks are
>acquired?
>Thoughts?
>Joel
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200804/1|||Wow! Those are some great ideas! I'll look into them further. Thank you.
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||Here is, IMHO, the bible for deadlock troubleshooting:
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"Joel Lyons" <JoelL@.novarad.net> wrote in message
news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
> It seems that a common problem we have is deadlocks occurring when one
> stored proc is updating a row and another store proc is running a read
> query against the same table. They'll often get deadlocked on two of the
> indexes for the table.
> For example:
> SP1 is updating a row in TableA, causing it to get an exclusive lock on
> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2 and
> attempts to get a shared lock on TableAIndex1.
> Deadlock!
> Of course, I can fix this by lowering the isolation level in SP2 to "read
> uncommitted", but I'd rather not.
> I can normally fix deadlocks by changing the order in which locks are
> acquired, but I don't know how to influence the order in which locks on
> indexes are acquired. Is it controlled by the order in which the fields
> are listed in a select or an update statement? If I standardize the order
> in which individual fields are listed whenever I perform a select or
> update from a given table, will that effect thr order in which index locks
> are acquired?
> Thoughts?
> Joel|||slip of the finger. here is the link:
http://blogs.msdn.com/bartd/archive/2006/09/09/Deadlock-Troubleshooting_2C00_-Part-1.aspx
--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net
"TheSQLGuru" <kgboles@.earthlink.net> wrote in message
news:8aSdnTZNUZEe02vanZ2dnUVZ_jWdnZ2d@.earthlink.com...
> Here is, IMHO, the bible for deadlock troubleshooting:
>
> --
> Kevin G. Boles
> Indicium Resources, Inc.
> SQL Server MVP
> kgboles a earthlink dt net
>
> "Joel Lyons" <JoelL@.novarad.net> wrote in message
> news:4E93C8A1-6EAC-4522-9CB9-1CECEC9687C3@.microsoft.com...
>> It seems that a common problem we have is deadlocks occurring when one
>> stored proc is updating a row and another store proc is running a read
>> query against the same table. They'll often get deadlocked on two of the
>> indexes for the table.
>> For example:
>> SP1 is updating a row in TableA, causing it to get an exclusive lock on
>> TableAIndex1 and attempts to get an exclusive lock on TableAIndex2.
>> SP2 is reading TableA, causing it to get a shared lock on TableAIndex2
>> and attempts to get a shared lock on TableAIndex1.
>> Deadlock!
>> Of course, I can fix this by lowering the isolation level in SP2 to "read
>> uncommitted", but I'd rather not.
>> I can normally fix deadlocks by changing the order in which locks are
>> acquired, but I don't know how to influence the order in which locks on
>> indexes are acquired. Is it controlled by the order in which the fields
>> are listed in a select or an update statement? If I standardize the
>> order in which individual fields are listed whenever I perform a select
>> or update from a given table, will that effect thr order in which index
>> locks are acquired?
>> Thoughts?
>> Joel
>

Deadlock updating different rows

Here is the condensed version of my question: why do I get a deadlock
when multiple threads are updating different rows in the same table?
Details: I have a table with a clustered index spread across multiple
columns. I run multiple threads, each of which accesses a separate row
in the table. Nevertheless, I see deadlocks.
SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
is
requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
is
requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
It is my understanding that the KEY locks are essentially row-level
locks because with clustered indexes the data are leaf nodes of the
index. As you can see, each thread is requesting a lock held by the
other. The locks are on the same index but different rows (the long
hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
I can't understand why different threads updating distinct rows would
ever want to lock the same rows. Granted, the rows may be adjacent,
but should that cause the acquisition of locks on rows other than the
one being updated? I could understand a broader locking if INSERTs
were happening, but that is not the case.
Thanks for any help you can offer.Is the index unique? Please post the complete table DDL and UPDATE
statement.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135189033.769214.220820@.f14g2000cwb.googlegroups.com...
> Here is the condensed version of my question: why do I get a deadlock
> when multiple threads are updating different rows in the same table?
> Details: I have a table with a clustered index spread across multiple
> columns. I run multiple threads, each of which accesses a separate row
> in the table. Nevertheless, I see deadlocks.
> SPID 61 is granted KEY: 10:240719910:1 (83033c6fb2c1) Mode: X and
> is
> requesting KEY: 10:240719910:1 (84031b0a9740) Mode: U
> SPID 60 is granted KEY: 10:240719910:1 (84031b0a9740) Mode: X and
> is
> requesting KEY: 10:240719910:1 (83033c6fb2c1) Mode: S
> It is my understanding that the KEY locks are essentially row-level
> locks because with clustered indexes the data are leaf nodes of the
> index. As you can see, each thread is requesting a lock held by the
> other. The locks are on the same index but different rows (the long
> hex numbers are hashes related to rows, e.g. 83033c6fb2c1).
> I can't understand why different threads updating distinct rows would
> ever want to lock the same rows. Granted, the rows may be adjacent,
> but should that cause the acquisition of locks on rows other than the
> one being updated? I could understand a broader locking if INSERTs
> were happening, but that is not the case.
> Thanks for any help you can offer.
>|||Dan,
Thanks for your reply. Index is unique. Here is the table definition:
create TABLE [Sum_Item_Revenue] (
[tendered_business_period_dim_id] [int] NOT NULL ,
[posted_business_period_dim_id] [int] NOT NULL ,
[event_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___event__5B78929E] DEFAULT (0),
[profit_center_dim_id] [int] NOT NULL ,
[misc_period_dim_id] [int] NOT NULL ,
[pay_type_dim_id] [int] NOT NULL ,
[emp_dim_id] [int] NOT NULL ,
[item_dim_id] [int] NOT NULL ,
[total_sales_gross_amount] [decimal](18, 4) NULL ,
[total_discount_amount] [decimal](18, 4) NULL ,
[ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
[DF__Sum_Item___order__45FE52CB] DEFAULT (0),
CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
(
[tendered_business_period_dim_id],
[posted_business_period_dim_id],
[event_dim_id],
[profit_center_dim_id],
[misc_period_dim_id],
[pay_type_dim_id],
[emp_dim_id],
[item_dim_id],
[ordered_profit_center_dim_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
It turns out it's not just a straight UPDATE but a stored procedure. I
realize the stored procedure has the potential to do INSERTs but for
the testing I've been doing, it's all been updates, because the records
already exist. Here is the stored proc:
create procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
Declare @.count int
SELECT @.count = count(*)
FROM Sum_Item_Revenue
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
IF @.count = 0
INSERT INTO Sum_Item_Revenue
( tendered_business_period_dim_id ,
posted_business_period_dim_id ,
profit_center_dim_id ,
ordered_profit_center_dim_id ,
misc_period_dim_id ,
pay_type_dim_id ,
emp_dim_id ,
item_dim_id ,
total_sales_gross_amount ,
total_discount_amount
)
VALUES
( @.tendered_business_period_dim_id ,
@.posted_business_period_dim_id ,
@.profit_center_dim_id ,
@.ordered_profit_center_dim_id ,
@.misc_period_dim_id ,
@.pay_type_dim_id ,
@.emp_dim_id ,
@.item_dim_id ,
@.total_sales_gross_amount ,
@.total_discount_amount
)
ELSE
UPDATE Sum_Item_Revenue SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount ,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id =
@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id AND
profit_center_dim_id = @.profit_center_dim_id AND
misc_period_dim_id = @.misc_period_dim_id AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
I've removed some non-essential columns from the table for the purposes
of this posting, to remove clutter.
I call this routine from multiple threads, where each thread passes in
a unique profit_center_dim_id. Other values of the key are similar.
So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
SELECT is manifested by one of my SPIDs above attempting to obtain a
shared lock.
Thanks, rand|||Try this:
BEGIN TRAN
IF EXISTS(SELECT 1 FROM...WITH(UPDLOCK, HOLDLOCK) WHERE...)
BEGIN
UPDATE...
--error handling here
END
ELSE
BEGIN
INSERT...
--error handling here
END
COMMIT TRAN
WITH(UPDLOCK,HOLDLOCK) places an update range-lock on the table that is
about to be modified. This does not affect select concurrency, because
other transactions can obtain shared locks on rows that already have an
update lock. It only affects insert/update concurrency and not by much. It
will eliminate the deadlock that you're encountering.
The construct below doesn't take into account the fact that another
transaction can obtain a lock on the row to be updated between the SELECT
and the UPDATE or INSERT.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Brian, Thanks I'll give it a try. I should add that the lack of
transaction semantics within my stored procedure is because this
procedure is invoked from .NET code within BeginTransaction() and
Commit() using the default isolation level of Read Committed. Slightly
bigger picture: I'm multi-threading code that has heretofore been
single-threaded. The thing that has me baffled is why there is any
lock contention at all, given that different threads should be
accessing different rows. Unless my assumption is incorrect and the
locks I see are really not row-level, but are table- or page-level.|||First, I prefer to handle transaction processing within the stored
procedure. This makes it a lot easier to troubleshoot deadlocks and to
change code--for example, to implement optimistic concurrency.
Second, READ COMMITTED is good for reporting; for modifications, it is a
disaster waiting to happen. You should use REPEATABLE READ or preferably
SERIALIZABLE if the information you're reading will be used in a subsequent
modification within the same transaction. As a rule, Rows selected that may
be updated should have an update lock applied and held until the transaction
commits; rows selected that will not be updated but whose value will be used
either directly or indirectly as values that will be inserted or updated
should have a shared lock applied and held. This is extremely important to
keep garbage out of your database. Any change to the source data between
the SELECT and the UPDATE/INSERT renders the results you've just read out
stale, which can introduce incorrect information into the database. If the
update involves inserting or updating summary information, then you should
use SERIALIZABLE because an INSERT will cause the results to become stale.
READ COMMITTED doesn't prevent changes from occuring between the SELECT and
the UPDATE/INSERT, and REPEATABLE READ doesn't prevent new rows that meet
the criteria used for summarization from being inserted.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135217308.843942.88810@.z14g2000cwz.googlegroups.com...
> Brian, Thanks I'll give it a try. I should add that the lack of
> transaction semantics within my stored procedure is because this
> procedure is invoked from .NET code within BeginTransaction() and
> Commit() using the default isolation level of Read Committed. Slightly
> bigger picture: I'm multi-threading code that has heretofore been
> single-threaded. The thing that has me baffled is why there is any
> lock contention at all, given that different threads should be
> accessing different rows. Unless my assumption is incorrect and the
> locks I see are really not row-level, but are table- or page-level.
>|||I see that the event_dim_id column is part of the primary key but is not
included in the where clause of the SELECT or UPDATE. This could increase
the likelihood of your deadlocks.
Brian pointed out that you are vulnerable to changes between the SELECT and
INSERT/UPDATE. Since you run the proc is run as part of a transaction,
below is another 'UPSERT' technique that I like to use. I hard-coded a zero
value for event_dim_id in this example.
alter procedure InsertUpdate_Sum_Item_Revenue
@.tendered_business_period_dim_id int ,
@.posted_business_period_dim_id int ,
@.profit_center_dim_id int ,
@.ordered_profit_center_dim_id int = 0,
@.misc_period_dim_id int ,
@.pay_type_dim_id int ,
@.emp_dim_id int ,
@.item_dim_id int ,
@.total_sales_gross_amount decimal(18, 4),
@.total_discount_amount decimal(18, 4)
AS
SET NOCOUNT ON
INSERT INTO Sum_Item_Revenue
(
tendered_business_period_dim_id,
posted_business_period_dim_id,
profit_center_dim_id,
ordered_profit_center_dim_id,
misc_period_dim_id,
pay_type_dim_id,
emp_dim_id,
item_dim_id,
total_sales_gross_amount,
total_discount_amount
)
SELECT
@.tendered_business_period_dim_id,
@.posted_business_period_dim_id,
@.profit_center_dim_id,
@.ordered_profit_center_dim_id,
@.misc_period_dim_id,
@.pay_type_dim_id,
@.emp_dim_id,
@.item_dim_id,
@.total_sales_gross_amount,
@.total_discount_amount
WHERE NOT EXISTS
(
SELECT *
FROM Sum_Item_Revenue WITH (UPDLOCK, HOLDLOCK)
WHERE
tendered_business_period_dim_id =@.tendered_business_period_dim_id
AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
)
IF @.@.ROWCOUNT = 0
BEGIN
UPDATE Sum_Item_Revenue
SET
total_sales_gross_amount = total_sales_gross_amount +
@.total_sales_gross_amount,
total_discount_amount = total_discount_amount +
@.total_discount_amount
WHERE
tendered_business_period_dim_id
=@.tendered_business_period_dim_id AND
posted_business_period_dim_id = @.posted_business_period_dim_id
AND
profit_center_dim_id = @.profit_center_dim_id
AND
misc_period_dim_id = @.misc_period_dim_id
AND
pay_type_dim_id = @.pay_type_dim_id
AND
emp_dim_id = @.emp_dim_id AND
item_dim_id = @.item_dim_id AND
ordered_profit_center_dim_id = @.ordered_profit_center_dim_id AND
event_dim_id = 0
END
GO
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135212067.893743.113210@.z14g2000cwz.googlegroups.com...
> Dan,
> Thanks for your reply. Index is unique. Here is the table definition:
> create TABLE [Sum_Item_Revenue] (
> [tendered_business_period_dim_id] [int] NOT NULL ,
> [posted_business_period_dim_id] [int] NOT NULL ,
> [event_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___event__5B78929E] DEFAULT (0),
> [profit_center_dim_id] [int] NOT NULL ,
> [misc_period_dim_id] [int] NOT NULL ,
> [pay_type_dim_id] [int] NOT NULL ,
> [emp_dim_id] [int] NOT NULL ,
> [item_dim_id] [int] NOT NULL ,
> [total_sales_gross_amount] [decimal](18, 4) NULL ,
> [total_discount_amount] [decimal](18, 4) NULL ,
> [ordered_profit_center_dim_id] [int] NOT NULL CONSTRAINT
> [DF__Sum_Item___order__45FE52CB] DEFAULT (0),
> CONSTRAINT [Sum_Item_Revenue_PK] PRIMARY KEY CLUSTERED
> (
> [tendered_business_period_dim_id],
> [posted_business_period_dim_id],
> [event_dim_id],
> [profit_center_dim_id],
> [misc_period_dim_id],
> [pay_type_dim_id],
> [emp_dim_id],
> [item_dim_id],
> [ordered_profit_center_dim_id]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> ) ON [PRIMARY]
> It turns out it's not just a straight UPDATE but a stored procedure. I
> realize the stored procedure has the potential to do INSERTs but for
> the testing I've been doing, it's all been updates, because the records
> already exist. Here is the stored proc:
> create procedure InsertUpdate_Sum_Item_Revenue
> @.tendered_business_period_dim_id int ,
> @.posted_business_period_dim_id int ,
> @.profit_center_dim_id int ,
> @.ordered_profit_center_dim_id int = 0,
> @.misc_period_dim_id int ,
> @.pay_type_dim_id int ,
> @.emp_dim_id int ,
> @.item_dim_id int ,
> @.total_sales_gross_amount decimal(18, 4),
> @.total_discount_amount decimal(18, 4)
> AS
> Declare @.count int
> SELECT @.count = count(*)
> FROM Sum_Item_Revenue
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> IF @.count = 0
> INSERT INTO Sum_Item_Revenue
> ( tendered_business_period_dim_id ,
> posted_business_period_dim_id ,
> profit_center_dim_id ,
> ordered_profit_center_dim_id ,
> misc_period_dim_id ,
> pay_type_dim_id ,
> emp_dim_id ,
> item_dim_id ,
> total_sales_gross_amount ,
> total_discount_amount
> )
> VALUES
> ( @.tendered_business_period_dim_id ,
> @.posted_business_period_dim_id ,
> @.profit_center_dim_id ,
> @.ordered_profit_center_dim_id ,
> @.misc_period_dim_id ,
> @.pay_type_dim_id ,
> @.emp_dim_id ,
> @.item_dim_id ,
> @.total_sales_gross_amount ,
> @.total_discount_amount
> )
>
> ELSE
> UPDATE Sum_Item_Revenue SET
> total_sales_gross_amount = total_sales_gross_amount +
> @.total_sales_gross_amount ,
> total_discount_amount = total_discount_amount +
> @.total_discount_amount
> WHERE
> tendered_business_period_dim_id =
> @.tendered_business_period_dim_id AND
> posted_business_period_dim_id = @.posted_business_period_dim_id AND
> profit_center_dim_id = @.profit_center_dim_id AND
> misc_period_dim_id = @.misc_period_dim_id AND
> pay_type_dim_id = @.pay_type_dim_id AND
> emp_dim_id = @.emp_dim_id AND
> item_dim_id = @.item_dim_id AND
> ordered_profit_center_dim_id = @.ordered_profit_center_dim_id
> I've removed some non-essential columns from the table for the purposes
> of this posting, to remove clutter.
> I call this routine from multiple threads, where each thread passes in
> a unique profit_center_dim_id. Other values of the key are similar.
> So, when I call this it is doing SELECTs and UPDATEs. I'm assuming the
> SELECT is manifested by one of my SPIDs above attempting to obtain a
> shared lock.
> Thanks, rand
>|||Thanks Dan & Brian. I will experiment. Any idea why different threads
accessing different rows should even be contending for resources at
all? Dan, event_dim_id is not significant since it is not used and
always has a default value of 0.|||Look at the lock information in your original post. It's not enough that
you're only modifying one row at a time. Your procedure also reads rows:
that's why you get deadlocks. Both SPIDs have exclusive locks on one row,
but before the transactions commit, they also are trying to obtain shared or
update locks on the other transaction's row. What's strange is that the
locks represented in the original post don't match what you would get from
your procedure. I suspect that there is another procedure involved. You
have an update lock, but the procedure you posted doesn't have
WITH(UPDLOCK). It is my understanding that update locks are only obtained
when an explicit locking hint is specified.
Is it possible that other statements are issued by the application. There
are many other things that could be causing the deadlocks. That's why I
prefer to encapsulate database updates in procedures, and whenever possible,
to handle any transaction processing within those procedures. It makes
troubleshooting much, MUCH easier.
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>|||> Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
The event_dim_id column might not be significant from your perspective but
SQL Server can't make the assumption that only one row will be returned
unless you include it in your WHERE clause. Also, the column is badly
needed to use the primary key index effectively. Check out the details of
the SEEK operator in the query plan without and with event_dim_id:
--without event_dim_id: scans all values with specified
tendered_business_period_dim_id
--and posted_business_period_dim_id
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]),
WHERE:((((([Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id]
AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d]) AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id]) AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id]) AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id]) AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
--without event_dim_id: single row retrieved via s
SEEK:([Sum_Item_Revenue]. [tendered_business_period_dim_id]=[@.tend
ered_busine
ss_period_dim_id]
AND
[Sum_Item_Revenue]. [posted_business_period_dim_id]=[@.posted
_business_period_
dim_id]
AND
[Sum_Item_Revenue].[event_dim_id]=0 AND
[Sum_Item_Revenue]. [profit_center_dim_id]=[@.profit_center_d
im_id] AND
[Sum_Item_Revenue]. [misc_period_dim_id]=[@.misc_period_dim_i
d] AND
[Sum_Item_Revenue].[pay_type_dim_id]=[@.pay_type_dim_id] AND
[Sum_Item_Revenue].[emp_dim_id]=[@.emp_dim_id] AND
[Sum_Item_Revenue].[item_dim_id]=[@.item_dim_id] AND
[Sum_Item_Revenue]. [ordered_profit_center_dim_id]=[@.ordered
_profit_center_di
m_id])
ORDERED FORWARD)
Not only will the inefficient plan hurt performance, it can contribute to
the likelihood of deadlocks.
Hope this helps.
Dan Guzman
SQL Server MVP
"rand" <randclark2005@.yahoo.com> wrote in message
news:1135276930.354270.55180@.g49g2000cwa.googlegroups.com...
> Thanks Dan & Brian. I will experiment. Any idea why different threads
> accessing different rows should even be contending for resources at
> all? Dan, event_dim_id is not significant since it is not used and
> always has a default value of 0.
>

DDL Trigger to update Instead Of Insert trigger

Hello NG,
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name =
@.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
Peter
Peter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>>
...
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name =
> @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>

DDL Trigger to update Instead Of Insert trigger

Hello NG,
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name =
@.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
PeterPeter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>
>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>
>
...
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name =
> @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>

DDL Trigger to update Instead Of Insert trigger

Hello NG,
In order to prohibit users from updating a CreationDate I have a database
with an Instead Of Insert trigger on several tables. To make life easier I
created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
Instead Of Insert trigger for a given table and adds/removes new/deleted
columns from the Instert statement.
As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
executing a query looking like this:
ALTER TRIGGER [ioiApplicationTrigger]
ON [dbo].[Application]
INSTEAD OF INSERT
AS
INSERT INTO Application
(ApplicationID, Title, Type, test1, test2, CreationDate)
SELECT
ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
FROM inserted
This works as expected.
In order to make life even easier I tried to create a DDL trigger looking
like this:
CREATE TRIGGER [UpdateStandardTriggers]
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE
AS
BEGIN
DECLARE @.trigger_name nvarchar(max);
DECLARE @.table_name nvarchar(max);
DECLARE @.data XML
-- Get table name from eventdata
SET @.data = EVENTDATA()
SET @.table_name = @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
EXEC UPDATE_IOI_TRIGGER @.table_name
END
The idea was thet this will automatically update my trigger whenever a
column in my table has been added, removed or changed.
However, this does not work. When I try to save a table after a change I get
the following error:
'Application' table
- Unable tp preserve trigger 'ioiApplicationTrigger'.
Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
because an INSTEAD OF INSERT trigger already exists.
In my procedure I check for the trigger using IF EXISTS and then I tried
both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a DROP
TRIGGER.
Any idea how I can achieve what I tried to explain before?
PeterPeter,
Why not just use column level DENY, e.g.,
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly1>>
DENY UPDATE ON [dbo].[Application] (CreationDate) TO <<UserEntitly2>>
...
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Peter Gloor" <p_gloor@.hotmail.com> wrote in message
news:ecCVYCuQGHA.2628@.TK2MSFTNGP15.phx.gbl...
> Hello NG,
> In order to prohibit users from updating a CreationDate I have a database
> with an Instead Of Insert trigger on several tables. To make life easier I
> created a stored procedure called UPDATE_IOI_TRIGGER which recreates the
> Instead Of Insert trigger for a given table and adds/removes new/deleted
> columns from the Instert statement.
> As an example EXEC UPDATE_IOI_TRIGGER 'Application' will create a trigger
> executing a query looking like this:
> ALTER TRIGGER [ioiApplicationTrigger]
> ON [dbo].[Application]
> INSTEAD OF INSERT
> AS
> INSERT INTO Application
> (ApplicationID, Title, Type, test1, test2, CreationDate)
> SELECT
> ApplicationID, Title, Type, test1, test2, GETDATE() AS CreationDate
> FROM inserted
> This works as expected.
> In order to make life even easier I tried to create a DDL trigger looking
> like this:
> CREATE TRIGGER [UpdateStandardTriggers]
> ON DATABASE
> FOR CREATE_TABLE, ALTER_TABLE
> AS
> BEGIN
> DECLARE @.trigger_name nvarchar(max);
> DECLARE @.table_name nvarchar(max);
> DECLARE @.data XML
> -- Get table name from eventdata
> SET @.data = EVENTDATA()
> SET @.table_name => @.data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(128)')
> EXEC UPDATE_IOI_TRIGGER @.table_name
> END
> The idea was thet this will automatically update my trigger whenever a
> column in my table has been added, removed or changed.
> However, this does not work. When I try to save a table after a change I
> get the following error:
> 'Application' table
> - Unable tp preserve trigger 'ioiApplicationTrigger'.
> Cannot create trigger 'ioiApplicationTrigger' for table 'dbo.Application'
> because an INSTEAD OF INSERT trigger already exists.
> In my procedure I check for the trigger using IF EXISTS and then I tried
> both, a) using ALTER TRIGGER and b) CREATE TRIGGER after I executing a
> DROP TRIGGER.
> Any idea how I can achieve what I tried to explain before?
> Peter
>
>