December 30, 2011 at 2:47 am
Michael Valentine Jones (12/29/2011)
Gianluca Sartori (12/28/2011)
NOLOCK? :sick:With RCS enabled writers don't block readers. Is this the kind of concurrency issue you're trying to address?
As far as the dangers is concerned, you could read this: http://www.jimmcleod.net/blog/index.php/2009/08/27/the-potential-dangers-of-the-read-committed-snapshot-isolation-level/
That link shows a bad way to control problems with lost updates, no matter what isolation level you are using.
This code shows a simple way to eliminate those problems using a rowversion column in the table to make sure it has not changed since it was read and to eliminate the need to hold locks to prevent lost updates.
...lots of code...
You are right and your code makes a good point.
I know that RCS Isolation can alleviate lock waits in heavy concurrent environments, I just wanted to point out that bad code can get even worse with RCSI.
It's a highly debated topic. Those who are accustomed to Oracle's optimistic locking can't even think of any good reason to implement pessimistic locking. Those (like myself) who have worked for years with SQL Server can get a bit nervous about optimistic locking.
However, locking is the last step for me when I tune a database. I usually start investigating locks when the data model, the queries and the indexes are already tuned at their best. When locks are taken for a very short time, I don't care much if they're optimistic or pessimistic.
-- Gianluca Sartori
December 30, 2011 at 6:29 am
I agree with you. I would like to understand transaction locking in much better detail than I do currently, but in the past whenever I have investigated a locking problem, I have found a really crappy query behind it. (Maybe I've just never had the incredible pleasure of working with a well-designed and implemented database system. Ah, the very thought! :-))
Where was I? Oh, yes, reality. 🙁 Anyway, if you can re-write the query such that it doesn't take a week to execute, then locking is much, much less of a problem.
Actually, I would also like to work on a system that was so incredibly busy that queries running in fractions of a millisecond also presented locking problems! 🙂 Ah, Nirvana!
Where was I? :unsure: Oh, yes, reality. 🙁
Sigh. Reality bites, you know.
December 30, 2011 at 6:38 am
patrickmcginnis59 (12/29/2011)
GSquared (12/29/2011)
patrickmcginnis59 (12/29/2011)
Brian.cs (12/29/2011)
RCSI really puts SQL Server running in a similar mode to what Oracle does by default, which is really what all robust modern RDBMS platforms should support. Locking by default is a very antiquated methodology and usually only well liked by legacy applications because that's the only option they had available.
I hope so. I'm absolutely sick of transactions. I just want to write the data and be done with it.
Its no secret that Macintosh record locks are advisory only, and if Microsoft doesn't get with the program they'll be sitting in history's dustbin with Digital Research and their failed CP/M.
There's a big difference between file locking and transactional data locking.
Non-ACID transactions, like in NoSQL, are only good if you don't mind losing data or corrupting it. Works just fine for something like Google search results or Twitter posts. Not so much for an order-taking system, or (even worse) a financial system.
I wasn't too terribly serious in my transaction post, but I'm really interested in the notion that NoSQL must lose data without hardware failure. Interprocess communications, ordered writes to a storage device, along with a turing complete cpu device with memory pretty much provides the ingredients upon which up to today I believed were the ingredients needed to devise a transactional mechanism for storing data.
If this is not the case, if I could not write code on these ingredients sufficient to devise a transaction then I'm wondering why? If it were impossible to devise a transaction on such a device then what extra ingredient did Microsoft include in SQL Server to allow transactions besides an ordered storage device, interprocess communications, and a turing complete cpu with memory? And why can't NoSQL duplicate this?
I don't know much about NoSQL, so thats why I'm asking.
Parallel processing has long had things like MUTEXes, semaphores, et al. The problem was originally solved in '65 (almost 50 years ago), very early in the days of parallel processing.
Locks are just the RDBMS version of a MUTEX. As with all MUTEX processes, they make for a more concurrency-safe system, but also for a slower system. They require resources just to establish, and they block resources that could otherwise run. Even optimistic locking, like snapshots, slow things down because they do require writes to be done in two places (the snapshot data in tempdb, the new data in the actual database), and CPU/RAM to manage.
NoSQL achieves a significant speed and footprint boost over traditional RDBMS systems by eliminating most locking. This means it is faster, takes less resources, and allows for faster concurrency. It also means there are commonly issues with concurrent data access.
Some systems built on NoSQL simply move locking out of the database and into a middle tier (data access layer is common). This has the advantage of moving those resource costs off of the database server and into another layer, often on other hardware. It has the disadvantage of still being less thread safe, because the rules are farther away from the data resources they are being applied to.
Other NoSQL systems simply assume that different query results for concurrent access won't hurt the end user. For example, if you're searching for web pages about some movie star, if you get 150,000 results, and I get 150,010 results, for the same search at the same time, does it really matter to either of us?
Really understanding locks and why they matter takes understanding the pros and cons of various co-processing paradigms. Most of the people who simply reject locks (insist on NoLock) couldn't tell you what a MUTEX or semaphore is, much less why a system should or should not use them.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2011 at 6:42 am
Dude, you should write an article (or maybe a series of articles).
December 30, 2011 at 6:48 am
David Moutray (12/30/2011)
Dude, you should write an article (or maybe a series of articles).
Already has done so. It's called 10K posts on ssc.com 😀
December 30, 2011 at 6:57 am
True, and that is an accomplishment I envy!
Really, though, you could do us all a favor by sitting down and explaining transaction locking in language the rest of us can understand.
At some point all of that knowledge in your head needs to get passed along, you know! (Or were you perhaps planning to live forever? :-))
December 30, 2011 at 7:14 am
David Moutray (12/30/2011)
True, and that is an accomplishment I envy!Really, though, you could do us all a favor by sitting down and explaining transaction locking in language the rest of us can understand.
At some point all of that knowledge in your head needs to get passed along, you know! (Or were you perhaps planning to live forever? :-))
He is not from this planet... He is refugee here so he has all superpowers (super brains :hehe:) and long life.
(God Bless him with long life!!!)
December 30, 2011 at 7:37 am
There's a lot of material out there about locking, and its history and relation to parallel processing of other sorts, but a common-sense article on it might be a good idea. I'll check with Steve on the need for that.
On the "from another planet" bit, shhhh! You're not supposed to tell anyone! :w00t:
On the 10k posts, thanks. It's a lot of work and a lot of fun. I intend to keep going on it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 30, 2011 at 7:48 am
GSquared (12/30/2011)
There's a lot of material out there about locking, and its history and relation to parallel processing of other sorts, but a common-sense article on it might be a good idea. I'll check with Steve on the need for that.On the "from another planet" bit, shhhh! You're not supposed to tell anyone! :w00t:
On the 10k posts, thanks. It's a lot of work and a lot of fun. I intend to keep going on it.
Ya that lots of fun (as I've had twice as much pleasure as you've had) :alien:
:hehe:
January 2, 2012 at 2:33 am
Ninja's_RGR'us (12/30/2011)
David Moutray (12/30/2011)
Dude, you should write an article (or maybe a series of articles).Already has done so. It's called 10K posts on ssc.com 😀
This means you already have written two? 😉
-- Gianluca Sartori
January 2, 2012 at 3:11 am
@GL
Ninja's_RGR'us (12/30/2011)
GSquared (12/30/2011)
There's a lot of material out there about locking, and its history and relation to parallel processing of other sorts, but a common-sense article on it might be a good idea. I'll check with Steve on the need for that.On the "from another planet" bit, shhhh! You're not supposed to tell anyone! :w00t:
On the 10k posts, thanks. It's a lot of work and a lot of fun. I intend to keep going on it.
Ya that lots of fun (as I've had twice as much pleasure as you've had) :alien:
:hehe:
January 3, 2012 at 1:19 am
Have I missed something? Can anyone tell me why RCS might be a solution, if it is not clear what the problem really is, and when it occurs? Are we talking about extensive locking, reoccurring deadlocks, incomplete loads, incorrect results, or still something else?
This discussion seems to run away from the original problem, maybe because no original problem has been stated. The question is about a solution, but every DBA knows the most common answer of every other DBA: it depends.
Generally updates do not occur in a DWH, so I'm puzzled about the fact that nobody wonders what kind of updates to your DWH are the cause of these (dead)locks. Even if it makes extensive use of an EAV model, that fact alone tells me only part of the story.
January 3, 2012 at 3:19 am
David Moutray (12/27/2011)
1. Do you know of any good articles that describe the benefits of READ_COMMITTED_SNAPSHOT to a relatively non-technical audience? (It might be more accurate to say that they are not SQL Server experts. They do have technical expertise in their own areas.)
The BOL entry Choosing Row Versioning-based Isolation Levels covers most of the main points, using relatively accessible language. It would be a good starting point for you to write an 'executive summary' anyway.
David Moutray (12/27/2011)
2. What are the disadvantages of enabling READ_COMMITTED_SNAPSHOT? Does it expose me to errors that I would not face if I did not enable it? How large is the risk (in very general and relative terms)?
The answer to the second part is yes, if the current application relies on READ COMMITTED locking semantics. This often manifests as code that depends on exclusive locks blocking concurrent readers to ensure correct results. A slightly more subtle variation of this occurs where triggers are used to enforce referential integrity (or worse, CHECK constraints calling user-defined functions); these need to have READCOMMITTEDLOCK hints added to ensure correct results (link). Where RI is enforced using FOREIGN KEY constraints, the database engine takes care of this detail for you: shared locks will be taken when checking constraints under RCSI or SI (link). The engine also handles a few other detailed scenarios, including indexed view maintenance.
Before moving on to disadvantages, one underestimated advantage of RCSI (and SI) is that it ensures results based on a consistent point-in-time view of the data. Alexander Kuznetsov has a powerful example of how READ COMMITTED and even REPEATABLE READ cannot count rows properly here: link
The main disadvantages of RCSI (and SI) have been covered already (increased tempdb activity and so on) but I would also like to highlight the fact that 14 bytes are added to every row that gets versioned - base table or index. This can have unexpected consequences for page splitting and fragmentation, at least until the system reaches a steady state.
Aside from that, the main risks really stem from poor-quality existing code.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 3:23 am
Michael Valentine Jones (12/29/2011)
This code shows a simple way to eliminate those problems using a rowversion column in the table to make sure it has not changed since it was read and to eliminate the need to hold locks to prevent lost updates.
Another popular solution is to wrap the read and write parts in a transaction, and take an UPDLOCK (indicating the intention to update the row later) on the read.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 3, 2012 at 3:35 am
SQL Kiwi (1/3/2012)
Michael Valentine Jones (12/29/2011)
This code shows a simple way to eliminate those problems using a rowversion column in the table to make sure it has not changed since it was read and to eliminate the need to hold locks to prevent lost updates.Another popular solution is to wrap the read and write parts in a transaction, and take an UPDLOCK (indicating the intention to update the row later) on the read.
Very popular in Oracle too (SELECT ... FOR UPDATE). Sometimes Often devs do this in PL/SQL code used in forms and hold the lock until the form gets closed. :sick:
-- Gianluca Sartori
Viewing 15 posts - 46 through 60 (of 70 total)
You must be logged in to reply to this topic. Login to reply