how to lock a row when perform SELECT statement

Hi,
i would like to know how to lock a table from other to view when select statement..can anyone help me with that?
i tried

SELECT * FROM IMIS_FRAMEWORK_RUNNING_NUMBER with (HOLDLOCK,ROWLOCK)
WHERE running_type=@.type;

but i m still able to see my row when i perform my SELECT STATEMENT from other query analyser..

Please help me with this..thank youCheck out this page, it shows you how to do what you're asking:

http://www.sql-server-performance.com/at_sql_locking.asp|||What you are asking to do is a sort of pessimistic lock, and this can be very dangerous in a web application. What happens e.g. when a user SELECTs the record and then their browser crashes?

You can read about concurrency control here:ASP.NET Optimistic Concurrency Control

Maybe if you explain more about what you are trying to do we can offer some suggestions.

Terri|||Thanks for helping ,

First of all i have a table to stores running number

tblRunningNumber
ID Desc LatestNumber
1 ModuleA 01
2 ModuleB 12

Then i wanna gat the latest runningnumber from tblRunningNumber n stores it in to my other table. For example i wanan get the latestNumber for ModuleA and stores it into ModuleA table.

To i need to have a stored procedure to lock the ModuleA rows n then update the latestNumber from 01 to 02 and at the same time update the 01 in my ModuleA database Table.

That y i need to lock the row..my issues is that when i do the select * statement it just hang there...until i close the connection or transactin is commited...

that the whole senarios that i m doing..can u give me any good suggestion on that coz i cannot use asp.net to control the running number so i decided to use storedprocedure .

Thanks alot.|||"can be very dangerous in a web application"

That's an understatement! Especially with a relatively unguarded select statement!|||But to ensure consistency, 'sometimes' you will have to enforce locking for the duration of a transcation. Can you tell us what would happen when a user SELECTs the record and then their browser crashes? The SELECT statement uses a HOLDLOCK. Isn't there a way to handle this crash scenario?|||

Holdlocks works on tables, pages, or rows it won't lock an entire database. If the commit and/or rollback is in the stored procedure, or contained in the batch sent to the SQL Server, then no. The batch and/or stored procedure will run to completion.

If you do something like issue this to the SQL Server: BEGIN TRANSACTION (Or start a transaction using the transaction object), then issue a SELECT ... (WITH HOLDLOCK) then crash before your ASP.NET application rollsback or commits the transaction, and the database server is remote, then yes, it'll be locked until the SQL Server realizes the connection is dead, and I'm not sure how long that would take. If it's a local SQL Server, then it realizes it immediate, and rollsback the transaction.

Also, the client crashing, disconnecting, going off-line, getting hit by lightning, etc means nothing. The server will continue executing it's page to completion no matter if the client is there or not, unless of course the page crashes itself, in which case the connection will be closed, and SQL Server will rollback it's transactions for those connections. Of course if the web server machine takes a complete dump (power loss, blue screen of death, etc), then SQL Server (if remote from the web server), will hold the lock until it realizes the connection is dead, and I'm not sure how quickly SQL Server detects a dead connection, but I'm guessing it'll be longer than you'd want in a web farm type environment.