When it comes to updating all rows in a table, does the method of locking matter for performance?

Posted by Evan Carroll on Stack Overflow See other posts from Stack Overflow or by Evan Carroll
Published on 2010-06-06T16:41:48Z Indexed on 2010/06/06 16:52 UTC
Read the original article Hit count: 321

Filed under:

sql

|

locking

Question is a follow up to this.

The SQL in question was

UPDATE stats SET visits = (visits+1)

And the question is, for the purpose of performance, does it matter if you lock all rows in stats in comparison to locking the table stats? Or, if the database uses a page-lock rather than a table/row lock?

© Stack Overflow or respective owner

Related posts about sql

SQL SERVER – Concat Strings in SQL Server using T-SQL – SQL in Sixty Seconds #035 – Video

as seen on SQL Authority - Search for 'SQL Authority'
Concatenating string is one of the most common tasks in SQL Server and every developer has to come across it. We have to concat the string when we have to see the display full name of the person by first name and last name. In this video we will see various methods to concatenate the strings. SQL… >>> More
SQL SERVER – Concat Function in SQL Server – SQL Concatenation

as seen on SQL Authority - Search for 'SQL Authority'
Earlier this week, I was delivering Advanced BI training on the subject of “SQL Server 2008 R2″. I had great time delivering the session. During the session, we talked about SQL Server 2010 Denali. Suddenly one of the attendees suggested his displeasure for the product. He said, even though… >>> More
Error with SQL Server Setup 2012 on Windows 2012

as seen on Server Fault - Search for 'Server Fault'
I am trying to install SQL Server on Windows 2012. I was able to finally get the wizard up and running after making some changes on the server, but now it fails no matter what I do with the following error: TITLE: SQL Server Setup failure. SQL Server Setup has encountered the following error: … >>> More
Nested SQL Select statement fails on SQL Server 2000, ok on SQL Server 2005

as seen on Stack Overflow - Search for 'Stack Overflow'
Here is the query: INSERT INTO @TempTable SELECT UserID, Name, Address1 = (SELECT TOP 1 [Address] FROM (SELECT TOP 1 [Address] FROM [UserAddress] ua INNER JOIN UserAddressOrder uo ON ua.UserID = uo.UserID WHERE ua.UserID = u.UserID ORDER BY uo.AddressOrder ASC) q ORDER BY AddressOrder… >>> More
How can I detect which version of SQL (eg SQL 2008 or SQL Azure)

as seen on Stack Overflow - Search for 'Stack Overflow'
I need to detect which version of SQL I am dealing with to perorm various tasks, I need specifically detect if I am on SQL 2008 or SQL Azure. How can I do this with detection code written in SQL? >>> More

Related posts about locking

Locking Cache Key without Locking the entire Cache

as seen on Stack Overflow - Search for 'Stack Overflow'
I have servlets that caches user information rather then retrieving it from the user store on every request (shared Ehcache). The issue I have is that if a client is multi-threaded and they make more then one simultaneous request, before they have been authenticated, then I get this in my log: Retrieving… >>> More
SQLite file locking and DropBox

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm developing an app in Visual C++ that uses an SQLite3 DB for storing data. Usually it sits in the tray most of the time. I also would like to enable putting my app in a DropBox folder to share it across several PCs. It worked really well up until DropBox has recently updated itself. And now it… >>> More
LifeLock fined $12 million over lack of life-locking ability

as seen on Internet.com - Search for 'Internet.com'
<b>ars Technica:</b> "As it turns out, there is no way to fully guarantee that identity theft won't happen, no matter what someone puts on the side of a truck." >>> More
Follow-up Answers for my Australia Classes

as seen on SQL Blog - Search for 'SQL Blog'
I was out of the country for the last two weeks of March, delivering classes in Brisbane and Sydney, which were organized by WardyIT . It was a great visit and there were 24 terrific students! As is sometimes (perhaps often?) the case, there were questions posed that I couldn’t answer during class… >>> More
Compaq R4000 laptop randomly locking up

as seen on Super User - Search for 'Super User'
I have a Compaq R4000 laptop with 2GB of RAM, running Ubuntu Linux 9.10. It is randomly locking up on me, approximately once every two days. I have a second partition with Windows XP Home installed, and I have had the system lock up in XP as well, meaning I believe this is a hardware issue. I have… >>> More