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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Issue Read Lock

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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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.

Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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
Go to Top of Page

denis_the_thief
Aged Yak Warrior

596 Posts

Posted - 2013-07-23 : 13:48:51
I'm not interested in your arrogance.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -