473,327 Members | 2,025 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Update statement performing table lock even though where condition on clustered primary index?

Hi All,

I have a database that is serving a web site with reasonably high
traffiic.

We're getting errors at certain points where processes are being
locked. In particular, one of our people has suggested that an update
statement contained within a stored procedure that uses a where
condition that only touches on a column that has a clustered primary
index on it will still cause a table lock.

So, for example:

UPDATE ORDERS SET
prod = @product,
val = @val
WHERE ordid = @ordid

In this case ordid has a clustered primary index on it.

Can anyone tell me if this would be the case, and if there's a way of
ensuring that we are only doing a row lock on the record specified in
the where condition?

Many, many thanks in advance!

Much warmth,

Murray
Jul 20 '05 #1
1 3822
M Wells <pl**********@planetthoughtful.org> wrote in message news:<cs********************************@4ax.com>. ..
Hi All,

I have a database that is serving a web site with reasonably high
traffiic.

We're getting errors at certain points where processes are being
locked. In particular, one of our people has suggested that an update
statement contained within a stored procedure that uses a where
condition that only touches on a column that has a clustered primary
index on it will still cause a table lock.

So, for example:

UPDATE ORDERS SET
prod = @product,
val = @val
WHERE ordid = @ordid

In this case ordid has a clustered primary index on it.

Can anyone tell me if this would be the case, and if there's a way of
ensuring that we are only doing a row lock on the record specified in
the where condition?

Many, many thanks in advance!

Much warmth,

Murray


It's possible that the UPDATE is blocking other processes - if MSSQL
takes an update lock on the row, it will also take intent locks at a
higher level. Those intent locks may prevent other processes getting
the locks they want, because not all lock types are compatible. So if
process A holds a row-level update lock, and intent-exclusive locks on
the table, then process B will not be able to obtain an update lock at
the table level.

In your case, if process B requires a table-level lock, then it may be
blocked by process A's intent exclusive lock. But that's just a guess
- you would need to investigate the locks being held in your system.
See "Understanding and Avoiding Blocking" and "Lock Compatibility" in
Books Online for more information. Erland has a useful tool for
examining locks:

http://www.sommarskog.se/sqlutil/aba_lockinfo.html

Simon
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Chris Foster | last post by:
I am trying to implement a very fast queue using SQL Server. The queue table will contain tens of millions of records. The problem I have is the more records completed, the the slower it gets....
2
by: Edward | last post by:
SQL Server 2000 Enterprise Edition Access 2000 Front End One of our clients has recently been experiencing problems with an app that has run satisfactorily (though slowly) for some time. To...
4
by: Karaoke Prince | last post by:
Hi There, I have an update statement to update a field of a table (~15,000,000 records). It took me around 3 hours to finish 2 weeks ago. After that no one touched the server and no...
9
by: David Berman | last post by:
I'm having a problem with an update operation in a stored procedure. It runs so slowly that it is unusable, unless I comment a part out in which case it is very fast. However, I need the whole...
10
by: R Camarda | last post by:
I have the following statement that takes quite a long time. Longest of any of my SQL statment updates. UPDATE F_REGISTRATION_STD_SESSION SET PREVIOUS_YEAR_SESSION_ID = ( SELECT...
2
by: Danny | last post by:
Is there any way to update a column in a clustered index without incurring the cost of reordering. Example: Create table TableX ( Col1 int, Col2 smalldatetime, Col3 varchar(10)) go create...
0
by: PeterC | last post by:
We're getting numerous deadlocks in a multi-user system where users are coming in and updating their own data. In our troubleshooting/traces, the deadlocks seem to be coming from lock contention...
9
by: Frank Swarbrick | last post by:
New to SQL here... We have a CURSOR declared thusly: EXEC SQL DECLARE ALL-ADJSTMTS-CSR CURSOR FOR SELECT ACCT.ACCOUNT_ID , ACCT.APPL_ID , ACCT.BRANCH_NUMBER , ACCT.CATEGORY_CODE
3
by: Michel Esber | last post by:
Hi all, DB2 V8 LUW FP 15 There is a table T (ID varchar (24), ABC timestamp). ID is PK. Our application needs to frequently update T with a new value for ABC. update T set ABC=? where ID...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.