Table locking issue in high-volume site

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 2000 Forums

SQL Server Development (2000)

Table locking issue in high-volume site

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-01-17 : 11:06:54

Barry writes "Hi,
I am trying to do the following:

Serialize the process of order number generation.
I want to avoid users getting the same order number.
I am working on a high-volume site and get 1000's or orders a day.
Here is what I am doing:

Begin Transaction

--- Increment order number ---
Update order_number
With (TABLOCK HOLDLOCK) --Lock the table for reads and writes to serialize
access
--With (TABLOCKX)
Set
number = number + 1

--- Return the new order number ---
Select
number
From
order_number

Commit Transaction

My question is is there a difference between "With (TABLOCK HOLDLOCK)" and
"With (TABLOCKX)" or is basically 2 ways of doing the same thing?

Any insight is appreciated.
Also, is there a better way to do it?

Thanks,
Barry"

smccreadie
Aged Yak Warrior

505 Posts

Posted - 2002-01-17 : 11:40:36

Why don't you use an IDENTITY field to increment the numbers? Seems a lot simpler then what you're trying to do.

aaa
Starting Member

4 Posts

Posted - 2002-01-17 : 11:50:14

quote:

Why don't you use an IDENTITY field to increment the numbers? Seems a lot simpler then what you're trying to do.

Unfortunatly, I inherited the system so I don't have that luxury.

Nazim
A custom title

1408 Posts

Posted - 2002-01-18 : 05:58:14

How about trying this way. it did worked for me (this one was suggested by nr)
begin tran
update tbl set @id = nextid + 1, nextid = nextid + 1
insert rec
commit tran
should work

as should
begin tran
update tbl set nextid = nextid + 1
select @id = nextid from tbl
insert rec
commit tran

you can use BOL to find the difference between different types of locks.
HTH

----------------------------------
"True love stories don't have endings."

izaltsman
A custom title

1139 Posts

Posted - 2002-01-18 : 09:44:39

Barry,

Your order_number is a table with just a single row, right?
In that case, I don't see any need for query hints.
Update statement always takes exclusive locks. When update is inside an explicit transaction, its locks are held to the end of that transaction. So your query hints aren't making much difference in this case.

aaa
Starting Member

4 Posts

Posted - 2002-01-18 : 10:05:06

quote:

Barry,

Your order_number is a table with just a single row, right?
In that case, I don't see any need for query hints.
Update statement always takes exclusive locks. When update is inside an explicit transaction, its locks are held to the end of that transaction. So your query hints aren't making much difference in this case.

Yeah, But what I'm worried about is the select statement after that.
I want the table locked exclusively for the duration of the update and the select.

aaa
Starting Member

4 Posts

Posted - 2002-01-18 : 10:08:43

quote:

Barry,

...When update is inside an explicit transaction, its locks are held to the end of that transaction. So your query hints aren't making much difference in this case.

Also, I'm not sure if that statement is true. I believe you do need to add hints to hold the locks until the end of the trans. (That, after all, is the purpose for many of these hints).

- Barry

izaltsman
A custom title

1139 Posts

Posted - 2002-01-18 : 10:11:26

I know... But like I said in my post, UPDATE holds exlusive locks to the end of your explicit transaction. So as long as you SELECT before COMMIT your locks will stay in place.

Edited by - izaltsman on 01/18/2002 10:12:00

aaa
Starting Member

4 Posts

Posted - 2002-01-18 : 10:18:11

quote:

I know... But like I said in my post, UPDATE holds exlusive locks to the end of your explicit transaction. So as long as you SELECT before COMMIT your locks will stay in place.

Edited by - izaltsman on 01/18/2002 10:12:00

Hmmmm.... I'm not sure if that is so. Does an update really hold the lock until the end of its transaction OR only until its statement is executed. I'm not sure the select statement after it (even in the same transaction) maintains that exclusive lock. Can you provide me confirmation from somewhere? I am under the strong impression that you need to specify a locking hint to hold the transaction beyond the update statement.

izaltsman
A custom title

1139 Posts

Posted - 2002-01-18 : 11:36:52

So now my word just isn't enough?! Oh, I'm hurt!

(kidding!)

Ok. So here are a few ways you can confirm:
1. Experiment.
Open a QA window and go to pubs, Northwind or any other database that you can mess around with.
Type in the following code:


--create and populate your table: 
create table order_number (number int)

insert into order_number values (1)

select @@spid    -- Take note of this number. 
                 -- We'll need it to monitor our locks from another window

--Let's start a transaction 
BEGIN TRAN 
   -- And now your UPDATE 
	Update order_number
	Set number = number + 1

-- stop here for a moment

Run this code. As you can see, this code opens a transaction and runs your update statement
leaving your transaction open for now. Let's see what we got for locks. Open another QA window and run:


sp_lock <spid>  -- Where <spid> is the number returned by select @@spid in the first window

Note exclusive lock on your row. Let's continue your transaction. Go back to first window and type:


Select number From order_number

Run it (be sure to highlight just this line 'cause you don't want to re-run everything above it).
Let's see if the lock situation changed at all. Go back to the second window and re-run sp_lock.
As you can see there are no changes -- your row is still exclusively locked. So now we are done.
Let's commit the transaction:


COMMIT TRAN

Run sp_lock once more to see that the exclusive lock (and any intent locks that came with it) have
been released. Was that convincing?

2. Reading a book.
Pick up a good SQL Server book (such as Inside SQL Server by Kalen Delaney quoted below) and read
about locking. Goes something like this:

quote:

Exclusive Locks
SQL Server automatically acquires exclusive locks on data when it is modified by an insert, update, or delete operation. Only one process at a time can hold an exclusive lock on a particular data resource; in fact, as you'll see when we discuss lock compatibility, no locks of any kind can be acquired by a process if another process has the requested data resource exclusively locked. Exclusive locks are held until the end of the transaction. This means that the changed data is normally not available to any other process until the current transaction commits or rolls back. Other processes can decide to read exclusively locked data by using query hints.

3. Searching SQLTeam articles for a keyword "locks". You'll find quote a few articles that may be of interest to you.

Hope this is convincing enough!
Cheers!

Edited by - izaltsman on 01/18/2002 11:39:39

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-01-18 : 13:02:09

Acutally an update lock has a update lock while searching for rows to update and then converts affected rows to exclusive lock (escalating up if too many rows get involved)

izaltsman
A custom title

1139 Posts

Posted - 2002-01-18 : 13:34:04

Right... But seeing how this is a one-row table and that the UPDATE statement has no WHERE clause and therefore no "search" phase, I believe SQL Server will opt for exclusive locks from the start.
Besides, even if an UPDATE lock was acquired, no other transaction would be granted UPDATE locks until the first transaction completed. So like I said, I see no need for query hints.

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-01-18 : 13:45:14

if you want it simple, just set transaction isolation level to serializable for that 1 transaction and change it back to read commited or whatever you prefer after the commit.

GreatInca
Posting Yak Master

102 Posts

Posted - 2002-01-18 : 13:47:26

Oh yeah serializable transactions are not concurrency friendly. Might find out that when you reach 10000+ orders per day when even the biggest meanest server performs not much better than the old one (short transactions lessen the effect but bigger servers do little to fix blocking problems). Identity fields and @@Identity is your best friend.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources