How to get top 1 record from a pool ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to get top 1 record from a pool ?

Author

Topic

rapcoon
Starting Member

2 Posts

Posted - 2006-06-08 : 09:36:02

Hi,
I have a product table with a unique serial no.
More than one users could access this table and get a product.

To avoid to reserve same product for more than one user, what kind of way do i have to choose?

Let me explain more;
Asume that these 3 records in a table

serial_no product_code status
1 1 0
2 1 0
3 1 0

when a user need to reserve will get the top, but if 2 users try to access at the same time, there could be a problem.

What is the easiest way to solve this problem?

Regards

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-08 : 09:40:47

Depends how the app works.
Do you want to be able to read and guarantee that the product will be available if the user selects it or do you want the user to select and then possible get a message that it is unavailable.
If the latter you just update the table with the reservation (order?) id where it is not assigned then check to see if it has updated anything.
If two try to get it at the same time then one will succeed and one fail.

==========================================
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.

rapcoon
Starting Member

2 Posts

Posted - 2006-06-08 : 09:53:29

The way that i use is below
I think it is not correct;

SET @Serial_No=NULL
BEGIN TRANSACTION
SELECT TOP 1 @Serial_No=serial_no
FROM offline_voucher WITH (HOLDLOCK,ROWLOCK)
WHERE (product_id=CAST(@Product_Code as numeric))
AND (status IS NULL)
AND (operation_date IS NULL) ORDER BY insertion_date ASC

IF (@Serial_No IS NOT NULL)
BEGIN
UPDATE offline_voucher
SET status ='1' ,
operation_date=getdate() ,
terminal_no=@Terminal_No
WHERE (product_id=CAST(@Product_Code as numeric))
AND (status IS NULL)
AND (operation_date IS NULL)
AND (serial_no=@Serial_No)
END
COMMIT TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources