locking select statement - SQL Server Forums

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

Transact-SQL (2000)

locking select statement

Author

Topic

BSLillo
Starting Member

9 Posts

Posted - 2006-02-05 : 06:27:09

Hello everyone!
I'm developing an application in which it is possible that two or more threads arrives contemporarily executing a "select statement", but i would like to implement this in a sort of critical session, the firsdt thread runs the select and noone in the meantime should execute the select, only one per time..
Have you some ideas?
Please help me!
thank you very much!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-05 : 07:33:38

You should look at transaction isolation levels in BOL.
You can also specify lock hints in your select HOLDLOCK, TABLOCKX.
HOLDLOCK will ensure that no other transaction will modify the data, TABLOCKX will lock the table for other transactions.

Can you provide some more information why you want this?

rockmoose

BSLillo
Starting Member

9 Posts

Posted - 2006-02-05 : 07:42:47

Thank you very much...my problem is:
I have N number of incoming calls on my Callcenter, theese calls enters in my application in order to connect to the database and to retreive a free internal phone number in order to connect to it.
So at the beginning i have to make a select statement where a flag is = 1 and retreive the related number of the phone to connect
It is possible that up to 200 threads income contemporarily to ma ke this select query in the worse case, so i have to implement a sor of critical session to ensure that the result of the select will be managed only by one of theese threads.
I implemented this stored procedure:

BEGIN TRAN
SELECT * FROM T_STATUSTCP
WITH(HOLDLOCK, TABLOCKX)
WHERE flag=1
COMMIT TRAN

iN THIS WAY DO YOU THINK I'M SURE THAT THERE ARE NO PROBLEMS OF CONFLICTS??
THANKS!!!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-05 : 08:26:13

When is flag updated to 0?
Is there just one number at a time with flag=1?

The way I see it a thread would go in, get the first free number,
update the flag to 0 (mark it busy) for that number and return it to the client.

rockmoose

BSLillo
Starting Member

9 Posts

Posted - 2006-02-05 : 08:35:00

No in my database table i can have more than one record with the flag 0 (free),so i execute the select, order by another value in the record, and retreive the first record of the select. This is the free phone internal number to contact. After i put the flag to 1 (busy).
But i can have up to 200 threads which enter in my database to execute the select, so i would like to serialize this process and only one per time execute the select and retreive the free internakl number.
At this point i have no problems writing or updating the db but only reading it.

Do you think that this could be correct? :

BEGIN TRAN
SELECT tel FROM T_STATUSTCP where (........) order by....
WITH(HOLDLOCK, TABLOCKX)
WHERE flag=0
COMMIT TRAN

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-05 : 08:58:44

Do the select and update in one transaction.

-- tel is unique in T_STATUSTCP
-- Declare a variable to hold the free number

DECALRE @tel VARCHAR(35) -- use correct datatype

-- Start the transaction
BEGIN TRAN
	-- Retrieve the first free number (flag=0), order by some criteria
	-- lock the data for the duration of the transaction
	-- Take finer grain rowlock since we are dealing with one row
	SET @tel = (SELECT TOP 1 tel FROM T_STATUSTCP(HOLDLOCK,ROWLOCK) WHERE flag=0 ORDER BY ...)
	
	-- Update the number to mark it busy
	UPDATE T_STATUSTCP SET flag=1 WHERE tel = @tel
COMMIT

-- Return the data for the free number to the client
SELECT ... FROM T_STATUSTCP WHERE tel=@tel

rockmoose

BSLillo
Starting Member

9 Posts

Posted - 2006-02-05 : 09:25:41

es, ok!
But trying to execute in Query analyzer this:

Begin Tran
SELECT TOP 1 tel FROM T_STATUSTCP(HOLDLOCK,ROWLOCK) WHERE flag=0 ORDER BY ...

and opening another new Query and execute a select on the same record, it execute tis query! It's strange because i have no Commit the Transaction so the record should be locked, or no? Why?
Instead if i execute:

Begin Tran
SELECT TOP 1 tel FROM T_STATUSTCP(HOLDLOCK,TABLOCKX) WHERE flag=0 ORDER BY ...

i see thet the table is locked and i can't execute the select on it.

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-05 : 10:35:07

Have a look in bol about locking.
Your select has taken and held a shared lock which won't block another select.

You can do te update and set in a single statement and not have to worry about transactions

-- Update the number to mark it busy
UPDATE T_STATUSTCP SET flag=1, @tel = tel WHERE tel = (SELECT TOP 1 tel FROM T_STATUSTCP WHERE flag=0 ORDER BY ...)

-- Return the data for the free number to the client
SELECT ... FROM T_STATUSTCP WHERE tel=@tel

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-05 : 10:44:05

quote:
Originally posted by BSLillo

es, ok!
But trying to execute in Query analyzer this:

Begin Tran
SELECT TOP 1 tel FROM T_STATUSTCP(HOLDLOCK,ROWLOCK) WHERE flag=0 ORDER BY ...

and opening another new Query and execute a select on the same record, it execute tis query! It's strange because i have no Commit the Transaction so the record should be locked, or no? Why?
Instead if i execute:

Begin Tran
SELECT TOP 1 tel FROM T_STATUSTCP(HOLDLOCK,TABLOCKX) WHERE flag=0 ORDER BY ...

i see thet the table is locked and i can't execute the select on it.

HOLDLOCK, was wrong choice. UPDLOCK would work.

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-05 : 13:22:30

Holdlock was needed. Need th updlock as well. Depends exactly what the query is as to wether it will be blocked by the lock.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-05 : 14:41:14

UPDLOCK (BOL)
Specifies that update locks instead of shared locks are taken while reading the table, and that they are held until the end-of-statement or end-of-transaction.

It seems to me that HOLDLOCK is not needed in combination with UPDLOCK, as the lock is held until end of transaction!?

The atomic way of updating & retrieving the value rocks though

.

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-05 : 16:51:57

>> It seems to me that HOLDLOCK is not needed in combination with UPDLOCK, as the lock is held until end of transaction!?

Try testing it.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-05 : 18:02:11

>> Try testing it.

With (HOLDLOCK,UPDLOCK) or (HOLDLOCK) there are deadlocks, these do not happen with just (UPDLOCK).

begin tran
-- read the tel no (lockhint)
-- update the tel no
commit
-- return the tel no to client

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-05 : 18:07:46

That's because it doesn't block a select without the holdlock (because it doesn't hold the lock).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-02-05 : 18:49:58

This will have the highest concurrency and not cause deadlocks:

begin tran
-- read the tel no (UPDLOCK)
-- update the tel no
-- return the tel no to client
commit

rockmoose

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-05 : 19:01:46

Maybe - but it doesn't solve the problem.
read the tel no (UPDLOCK)
Won't hold the lock so another spid can get the same row - might be release dependent but I've never seen it hold a lock.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-06 : 06:43:50

Refer this also
http://www.windowsitpro.com/Article/ArticleID/14042/14042.html

Madhivanan

Failing to plan is Planning to fail

nr
SQLTeam MVY

12543 Posts

Posted - 2006-02-06 : 06:58:09

That applies to v6.5 although I wouldn't rely on the built in row level locking without testing (same as any locking).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Kristen
Test

22859 Posts

Posted - 2006-02-06 : 08:26:55

Slightly different tack:

We require that people cannot Save a modified record unless they are updating the "most recent version" of that record.

Our application is a web site, so we have no control over people's usage, "Out to lunch", "Saved in cache", etc.

So we put an "Edit Number" column on each table, and increment that column whenever a record is saved. The SELECT gets the (original) Edit Number, and the Save checks that it is still valid.

This only prevents a Save if someone else has already updated the record (which would be very annoying if there were lots of attempts at concurrent edits), but in our systems concurrent edits are very rare, so we are only trying to prevent them, rather than get an explicit lock.

Some something like this:


SELECT MyEditNumber, ... other columns ...
FROM MyTable
WHERE MyPrimaryKey = 'FOO'

and then somewhat later


UPDATE MyTable
SET ...some assignments ...
    , MyEditNumber = MyEditNumber + 1
WHERE     MyPrimaryKey = 'FOO'
      AND MyEditNumber = MyStoredOriginalEditNumber
IF @@ROWCOUNT = 0 ... User had a duff version ...

Kristen

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-06 : 10:20:32

quote:
Originally posted by Kristen
...So we put an "Edit Number" column on each table, and increment that column whenever a record is saved. The SELECT gets the (original) Edit Number, and the Save checks that it is still valid...

Isn't this what a column of datatype ROWVERSION (TIMESTAMP) is for? Is that what you are using, or are you rolling your own?

CODO ERGO SUM

BSLillo
Starting Member

9 Posts

Posted - 2006-02-06 : 17:01:38

Ok, thank you very much for the help!
I have one last question....if my select does not find a record with the flag = 0, so if there are no free internal phones, what does it return to me? What value? How ca i manage this returned value?
Because i think that @@rowcount in this case does not work, or yes?

Thank you very very much!!!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources