December 29, 2011 at 9:10 am
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/
Alright, in that article I see:
UPDATE Inventory SET Qty = Qty - @QtyRequired
WHERE ItemID = 796 AND LocationID = 1
Why wouldn't they write
UPDATE Inventory SET Qty = Qty - @QtyRequired
WHERE ItemID = 796 AND LocationID = 1 AND Qty >= @QtyRequired
Then check for rows modified during the update, and if the update didn't go (because of lack of qty),
just return something about lack of inventory????
Sure, repeatable read fixes this, but otherwise what am I missing here?
December 29, 2011 at 9:24 am
patrickmcginnis59 (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/
Alright, in that article I see:
UPDATE Inventory SET Qty = Qty - @QtyRequired
WHERE ItemID = 796 AND LocationID = 1
Why wouldn't they write
UPDATE Inventory SET Qty = Qty - @QtyRequired
WHERE ItemID = 796 AND LocationID = 1 AND Qty >= @QtyRequired
Then check for rows modified during the update, and if the update didn't go (because of lack of qty),
just return something about lack of inventory????
Sure, repeatable read fixes this, but otherwise what am I missing here?
The article was poorly written. The discussion on it covers much the same problem.
The main risks with either version of snapshot isolation is that other connections will read the prior version of data that's being updated/deleted, and this can, in some circumstances, cause problems.
In an inventory system, for example, that keeps a running total in the database (which is almost always a bad idea anyway), two updates could end up being run against the same prior running total, giving a wrong new running total.
Same for a "build your own identity" solution (also generally a bad idea overall) that gets hit by snapshot isolation instead of something like TablockX.
The sample in the article is just not well written is all.
- 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 29, 2011 at 9:25 am
David Moutray (12/29/2011)
This is the point when I need to step in and ask that we all remain positive and pleasant. 🙂All of you are helping me by offering your technical opinions based on your experience and professional knowledge. I would hate to discourage anyone from doing that. 🙂
Both Gus and Michael have given me valuable information. It sems to me, based on the discussion here, that RCSI is unlikely to help my situation much. It would probably be good for me to eliminate WITH(NOLOCK) wherever I can, and continue to monitor performance.
RCSI might be a good solution if I see locking problems after that point. Right now, however, it is difficult to say for certain.
I want to thank everyone who has taken part in this discussion. When I first asked the question, I did not realize how deep the topic could really get. You have all given me a lot to think about. 🙂
Live Long, and Prosper. :alien:
Glad we could help.
Sorry if it got ugly. I'm trying to make an important point about considering indirect consequences and costs.
- 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 29, 2011 at 10:38 am
Do you have a test system where you can try out RCSI?
I have past experience with legacy DWH where reports would write to persistant tables before pulling those results out to show in Crystal reports. In that case, multiple uses running reports would find contention, and RCSI would benefit. So DWH is all relative, there can be activities going on behind the scenes especially if there's an app running on top of the DWH that works in a particular way such as I've described.
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.
Bottom line, if you've got a test system test it out. We recently went from "Read Commited" to RCSI and for what we were doing found the overhead was not a problem and the benefits completely outweighed it.
December 29, 2011 at 10:49 am
Sadly, no, I do not have a test system where I can run my current environment under a load similar to production. (That would be awfully nice.)
I don't believe we have any reports that actually write to persistent tables before displaying the data to the user. It is possible that we do, but I haven't seen anything like that. I'll keep my eyes open.
I agree with your statement about legacy code. Unfortunately, "legacy code" is best defined as any code which has been in production more than five minutes. Sigh. 🙁
December 29, 2011 at 11:02 am
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.
December 29, 2011 at 11:12 am
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.
- 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 29, 2011 at 11:19 am
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.
Actually, I wouldn't mind my bank losing record of my withdrawals! 😉
Actually, if you just want to completely forget about transactions, then set your transaction isolation level to SERIALIZABLE. That eliminates all data integrity issues arising from concurrency. 🙂
Of course, it pretty much does this by eliminating concurrency. 🙁
Actually, that's a good idea for another post. 🙂
December 29, 2011 at 11:26 am
David Moutray (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.
Actually, I wouldn't mind my bank losing record of my withdrawals! 😉
Actually, if you just want to completely forget about transactions, then set your transaction isolation level to SERIALIZABLE. That eliminates all data integrity issues arising from concurrency. 🙂
Of course, it pretty much does this by eliminating concurrency. 🙁
Actually, that's a good idea for another post. 🙂
Make the devs start using TablockX instead of NoLock, as their query-hint of choice? Fun times! :w00t:
- 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 29, 2011 at 11:31 am
GSquared (12/29/2011)
Make the devs start using TablockX instead of NoLock, as their query-hint of choice? Fun times! :w00t:
Sure! First come, first serve! 🙂
December 29, 2011 at 11:38 am
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.
SQL Server, until the row versioning isolation levels (RCSI and Snapshot), in my opinion after working on a number of apps and warehouses, did not properly support the I in ACID. However, debating the I in ACID is akin to debating religion or politics - everyone has a different take. The fact that writers can (and do) block readers by default is still mind boggling to me. But with the row versioning isolation levels Microsoft now utilizes TempDB akin to how Oracle has utilized UNDO for quite some time and is able to scale/perform far better than was possible in the earlier SQL Server days.
December 29, 2011 at 12:08 pm
Brian.cs (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.
SQL Server, until the row versioning isolation levels (RCSI and Snapshot), in my opinion after working on a number of apps and warehouses, did not properly support the I in ACID. However, debating the I in ACID is akin to debating religion or politics - everyone has a different take. The fact that writers can (and do) block readers by default is still mind boggling to me. But with the row versioning isolation levels Microsoft now utilizes TempDB akin to how Oracle has utilized UNDO for quite some time and is able to scale/perform far better than was possible in the earlier SQL Server days.
Have to agree with you, with the caveate that the database needs to have been built from the ground up for concurrent, non-consistent reads, and the business logic needs to account for it.
It's not as much a technical question. Two queries that are run "at the same time" means completely different things to users and to the CPU.
The problems with snapshot isolation, or any other form of optimistic locking, generally aren't ones with the CPU doing the wrong thing. It's a user getting different results from two sub-reports on the same site, and that kind of thing. "How come report X says inventory of item A is 2,500 in the summary, but says it's 0 in the details?" It's hard to explain to users. Reports is just one example.
Repeatable Read or Serialiable transactions don't cause that kind of issue. They cause other problems, but they avoid that one.
It's all trade-offs, and there's a lot of homework needed, and work with the business unit/customer, to determine what trade-offs are best for what situations.
Please note, I have several OLTP databases set for RCS isolation. I'm not arguing against that. It's a wonderful thing when it's used correctly. All I'm asking is, make sure you know ALL the consequences before you change something as core to the database as transaction isolation.
Switching to RSC isn't like changing the rims on your car's tires. It's like replacing/enhancing the fuel system. It can improve gas mileage, power, et al, but it can also result in fuel leaks, being a road hazard, and dramatically reduced resale value. (I hope I didn't push that metaphore too far.) The trick is to really know what you're doing before you just start throwing parts under the hood.
- 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 29, 2011 at 1:24 pm
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.
print 'Create dbo.Inventory table'
create table dbo.Inventory
(
ItemIDintnot null,
LocationIDintnot null,
QuantityOnHandintnot null,
-- Used to control attempts at concurrent updates
RowVer rowversionnot null,
constraint PK_Inventory primary key clustered (ItemID, LocationID)
)
go
print 'Insert a row into dbo.Inventory'
insert into dbo.Inventory (ItemID, LocationID, QuantityOnHand)
select
ItemID= 10,
LocationID= 20,
QuantityOnHand= 145
print 'Select data from dbo.Inventory'
select* from dbo.Inventory
declare
@ItemIDint,
@LocationIDint,
@QuantityOnHandint,
@RowVer varbinary(8),
@QuantityNeededint
set @ItemID= 10
set @LocationID= 20
set @QuantityNeeded= 14
-- Get current values form dbo.Inventory
select
@ItemID= a.ItemID,
@LocationID= a.LocationID,
@QuantityOnHand= a.QuantityOnHand,
@RowVer= a.RowVer
from
dbo.Inventory a
where
a.ItemID= @ItemIDand
a.LocationID= @LocationID
-- Update row
print 'Update #1'
update dbo.Inventory
set
QuantityOnHand = QuantityOnHand - @QuantityNeeded
where
ItemID= @ItemIDand
LocationID= @LocationIDand
-- Don't want a negative balance
QuantityOnHand- @QuantityNeeded >= 0and
-- Don't update if changed since the row was read
RowVer= @RowVer
print 'Select data from dbo.Inventory after update 1'
select* from dbo.Inventory
-- Show failed attempt to update row because it has changed since it was read
print 'Update #2 updates nothing because RowVer has changed'
update dbo.Inventory
set
QuantityOnHand = QuantityOnHand -@QuantityNeeded
where
ItemID= @ItemIDand
LocationID= @LocationIDand
-- Don't want a negative balance
QuantityOnHand- @QuantityNeeded >= 0and
-- Don't update if changed since the row was read
RowVer= @RowVer
print 'Select data from dbo.Inventory after update 2 to show it is unchanged since first update'
select* from dbo.Inventory
go
drop table dbo.Inventory
Results:
Create dbo.Inventory table
Insert a row into dbo.Inventory
(1 row(s) affected)
Select data from dbo.Inventory
ItemID LocationID QuantityOnHand RowVer
----------- ----------- -------------- ------------------
10 20 145 0x00000000000007F8
(1 row(s) affected)
Update #1
(1 row(s) affected)
Select data from dbo.Inventory after update 1
ItemID LocationID QuantityOnHand RowVer
----------- ----------- -------------- ------------------
10 20 131 0x00000000000007F9
(1 row(s) affected)
Update #2 updates nothing because RowVer has changed
(0 row(s) affected)
Select data from dbo.Inventory after update 2 to show it is unchanged since first update
ItemID LocationID QuantityOnHand RowVer
----------- ----------- -------------- ------------------
10 20 131 0x00000000000007F9
(1 row(s) affected)
December 29, 2011 at 4:56 pm
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.
December 29, 2011 at 5:19 pm
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.
"NoSQL architectures often provide weak consistency guarantees, such as eventual consistency, or transactions restricted to single data items. Some systems, however, provide full ACID guarantees in some instances by adding a supplementary middleware layer (e.g., AppScale and CloudTPS).[13][14] Two systems have been developed that provide snapshot isolation for column stores: Google's Percolator system based on BigTable,[15] and a transactional system for HBase developed at the University of Waterloo.[16] These systems, developed independently, use similar concepts to achieve multi-row distributed ACID transactions with snapshot isolation guarantee for the underlying column store, without the extra overhead of data management, middleware system deployment, or maintenance introduced by the middleware layer."
Viewing 15 posts - 31 through 45 (of 70 total)
You must be logged in to reply to this topic. Login to reply