Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-07-18 : 11:52:55
|
I am trying to find a command that will lock a table so that the record can neither be written nor read by any other session during the transaction? I am aware this can be done with an Update statement but I can't use that.This is basically what I want to do: Start Transaction Place a lock on record so that no one else can read/write Use the value from this record and use a certain logic to get a new value Update the record with this new value Execute some additonal commands Commit Transaction If I can't block reads, another process could do a select while this process is mid-transaction and retrieve the same value, and using the same logic will get the same new value and that would be a problem. |
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-07-19 : 03:51:00
|
What you describe is SQL read committed default locking.Snapshot isolation will read past your in progress update. That's actaully generally a good thing. Not sure why you would not want that as snapshots are way more consistent than read committed.What are you doing that you want to wait for all commits? You'll cripple throughput you realise? |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-07-19 : 09:39:12
|
quote: Originally posted by LoztInSpace What you describe is SQL read committed default locking.
If I started with an Update, the scenario would be like Read Committed, blocking Selects, if I'm not mistaken. But I need to start with a Select or somehow a read lock, and have that block other selects so that no one else can retrieve that same value once the process starts.quote: Snapshot isolation will read past your in progress update. That's actaully generally a good thing. Not sure why you would not want that as snapshots are way more consistent than read committed.What are you doing that you want to wait for all commits? You'll cripple throughput you realise?
Snapshot Isolation won't help, that means the same value can be retrieved by more than one process, creating the problem we want to avoid.Here is more a less what we need to do. For every Company Record, we have a NextOrderNumber which we need to save with the new Order as the CompanyOrderNo. But we want to update it to a new NextOrderNumber when the next order comes around. And our problem is that when 2 processes are creating orders at the same time for the same company, the CompanyOrderNo is being repeated.Also, when we retrieve the NextOrderNumber, we need to do some checks first based on some rules, usually we will just add one but not always. So this is why we want to start off with a select or lock and have no one else retrieve the value until the process is done.If you have a work around, great, but what I really want to know is if some sort of read (i.e. read/write) lock can be placed on a record other than with an Update statement. |
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2013-07-19 : 09:49:27
|
I admit I don't know what you are trying to do and I agree with LostInSpace that there is probably a better way. However, if you want to continue down that path, if you run each of these statements in a transaction you will see that the last three wil block a different session from executing:- SELECT 1 FROM MyTable WHERE ID = 101
- SELECT 1 FROM MyTable WITH(XLOCK) WHERE ID = 101
- SELECT 1 FROM MyTable WITH(XLOCK,REPEATABLEREAD) WHERE ID = 101
- SELECT 1 FROM MyTable WITH(XLOCK,SERIALIZABLE) WHERE ID = 101
- SELECT 1 FROM MyTable WITH(UPDLOCK,ROWLOCK,HOLDLOCK) WHERE ID = 101
- SELECT 1 FROM MyTable WITH(TABLOCKX) WHERE ID = 101
To implement a pessimistic pattern, you will need to use one of the hint combinations that blocks another session. I *believe* that (UPDLOCK,ROWLOCK,HOLDLOCK) is the cheaper among them. |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-07-19 : 15:49:43
|
That is certainly interesting.But I have Read_Comitted_Snapshot enabled. I first tried this with Read_Comitted_Snapshot enabled and select ... WITH(TABLOCKX), would not block out other selects, unless the other select had WITH(TABLOCKX). But then I tried turning Read_Comitted_Snapshot off and everything worked.But I guess with Read_Comitted_Snapshot enabled, an update statement won't force a select statement to wait.I wonder if there is a way to force select statements to wait when Read_Comitted_Snapshot enabled? I know that is the purpose of Read_Comitted_Snapshot enabled, to eliminate the waiting/blocking, but it would be a good option for certain situations. |
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2013-07-23 : 00:24:45
|
Hi Denis,All I see here is a need for something unique to identify the order. An identity, GUID (or sequence in 2012) would do what you need without locking and allow much higher throughput. Are you absolutely sure you need to get a specific number? Why? What's the purpose of that? |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-07-23 : 11:03:10
|
quote: Originally posted by LoztInSpace Hi Denis,All I see here is a need for something unique to identify the order. An identity, GUID (or sequence in 2012) would do what you need without locking and allow much higher throughput. Are you absolutely sure you need to get a specific number? Why? What's the purpose of that?
Hi. It is a wrapping number for one. i.e. 998,999,100,101. So that rules out identity an GUID. Also it is a customer-based sequence, i.e. CustomerA could be at 500, 501... and CustomerB at 450, 451...If it were like this, we could use an update/case statement. But there are a few more business-specific wrinkles to it so we need to select the current number and call a function to get the next number. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-23 : 12:39:12
|
I am more interested why you want to mimic .net programming style instead of thinking set-based which we normally do in a database.What is your objective? What could possible be the reason for this awkward behaviour? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-07-23 : 12:48:13
|
quote: Originally posted by SwePeso I am more interested why you want to mimic .net programming style instead of thinking set-based which we normally do in a database.What is your objective? What could possible be the reason for this awkward behaviour? Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
I'm not trying to smartie pants. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-23 : 13:15:53
|
Me neither. I am just trying to save your job in the long run.Why do you need to lock the row in the first place? What is the logic for updating the row? Is there really a need for locking the row?You cannot guarantee this behaviour even when you specify (ROWLOCK) table hint! It is just a suggestion where SQL Server should start locking, but SQL Server is not by any means forced to the wanted lock. SQL Server can put a page lock, or even a table lock, if it's a better choice.That's why I wonder why you want this behaviour. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-07-23 : 13:48:51
|
I'm not interested in your arrogance. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2013-07-23 : 14:13:18
|
The *ONLY* way to do this your way is to manage your own locking mechanism. Probably by adding new bit-column "Working". If set to 1, no-one is allowed to select, update or delete this row. However, that requires a lot of code change.Let me know how thing progress. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA |
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2013-07-23 : 14:14:24
|
quote: Originally posted by SwePeso The *ONLY* way to do this your way is to manage your own locking mechanism. Probably by adding new bit-column "Working". If set to 1, no-one is allowed to select, update or delete this row. However, that requires a lot of code change.Let me know how thing progress. Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Thankyou. |
|
|
|
|
|
|
|