insert blocking...

[DLu]

found many blocking on SQL2000 enterprise by insert statement, the same insert statement failed in the sql/w with violation on the primary key.

however, the appl insert also got the same error from log, but still in 'blocking' instead of to be 'killed' by the pk violation.

what would be the options to deal with this issue?

thanks
-D

[MAK]

when you get the statement using DBCC inputbuffer you will get the PK violation in ISQLw because by the time you execute that statement it would have already been inserted through application.

Blocking is ok if it exist for a few seconds.

you should investigate the following
a. how the insert statements are executed? from a proc or dynamic sql
b. Does it have a clustered index or non clustered
c. The table that is getting inserted being queried by some other query?
d. What kind of lock is in place when there insert statement gets executed.
e. what kind of lock timeout is in place?
and so on....

[DLu]

a. insert statement is for 3 columns including the pk column, other columns either with null or default vale, via dynamic sql, not a proc.
b. the pk for the table is a clustered index.
c. the table is very heavily queried by multipler users.
d. insert lock is by default, no explicit, then guess is the exclusive lock.
e. @@lock_timeout = -1

thanks
-D
__________________

[MAK]

1. All the users that are querying this table should use (NOLOCK) hint (if it is ok with phantom rows). Usually I do not allow DSS in my OLTP environment. I create a dedicated replicated environment (TRANSACTIONAL) for DSS.

2. locktimeout is -1. which means wait indefinetely. If you want to avoid deadlock set the lock timeout to something rational.

3. Check how often you reindex the table.

4. Is PK column is a primary key using Identity column or you query the same table for finding the maximum value in order to get assign new PK value.

[DLu]

Hi MAK:

1. agree with the seperate qry db and insert db approach, but need to convince the management...
2. locktimeout has been reset. Is this reset one time deal, or need to reset whenever the sql svr is restarted?
3. that PKcolumn is the only index for the table, other coulmns all allow nulls except one coulmn with datetime datatype not null. Thus, think whenever a new record inserted/updated/deleted, the clustered pkColumn index would 'auto' be reindexed?
4. PK column is a primary key not using identity value increment. And the query basically just select whole row where pkColumn = value.

Try to clear the concept a bit:
A deadlocking occurs when 2 users/sessions have locks on separate objects and each user wants a lock on the other's object. Each user waits for the other to release their lock. Here, the object is the table, right? But the insert blocking is only try to insert into the same table where is a pk violation but the connection was not killed. Thus, in this case, is on the same object, not 2 seperate objects like Supplier table and Part table, 2 separate objects? The whole application insert/update only and only against one table. So, this is not a deadlocking according to definition. But it is a deadlocking to the table level in reality?....

if it helps, is there a way to increase the default lock value, like in old sql version? I was heard that the default locks allocation is only 2% of the memory, but hesitate to change the system settings.

thanks
-D

[Rawhide]

It is acting like a deadlock only because the lock wasn't timing out. Now that it is, you shouldn't have that problem any more. The problem isn't the number of locks you can have at once because there can only be one active table lock on a given table.

When a new PK value is inserted, the whole table will be auto-resorted. This is going to affect the performance of your insert queries.

Index statistics are updated by SQL Server, but not well enough. If it was to re-index the table every time a new record is inserted, your performance would make the database unusual for more than 1 person.

You should have a routine of reindexing that you do based on the use of your db. For my high production databases, I defrag the indexes every 2 hours or every hour and then reindex once per night at 3 AM.