Lock a row in the database

Hello,

I created a form that retrieve a number from a database. Can I lock that record so that other people cannot access it?

Similar to "lock" function in Visual Fox Pro database.

Best regards,
Tee Song Yann

Sounds like you are doing some form of incrementing.

A very good way to handle that would be to use the OUTPUT capability of SQL 2005 -it allows you to BOTH increment and retrieve the incremented value in the same query.

Perhaps something like this:

DECLARE @.MyNumberTable table
( NextNumber int )

UPDATE MyNumberTable
SET NextNumber = ( NextNumber + 1 )
OUTPUT inserted.NextNumber INTO @.MyNumberTable

SELECT NextNumber
FROM @.MyNumberTable

|||

Hello Arnie Rowland,

Thanks for the reply. Your solution solved the first part of my problem. The record locking part is still remain unsolved. A friend from another forum suggested:

SET TRANSACTION ISOLATION LEVEL { READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SNAPSHOT | SERIALIZABLE }[ ; ]

What do you think?

All your help is much appriciated.

Best regards,
Tee Song Yann

|||

Are you attempting to create an auto-incrementing column? If so use an identity column.

Updating a central value and then holding a lock on it will introduce a bottleneck into your application as all connections will eventually be blocked waiting to update the same value.

|||

Dear Jerome Halmans,

My idea is:

1) I have a table that store various last used number for various document.

2) I will use those number combine with a few more code to create a meaningful serial number. (For example: An invoice will consist of XX XXXXXX XXXX which is the company code, year/month/date and next number of last used number.)

3) If another person login from another company, it will have different company code and different last number.

4) The fun part is I do not want to have jump numbers.

5) If the first user cancel the record inserting, then the number should stay unchanged.

6) The only way that I can think of is by locking the record while the first user is inserting the record.

Is there a batter way for me to achieve that?

Best regards,

Tee Song Yann

|||

I HIGHLY recommend dropping the idea of trying to maintain consequtive 'numbers' if users can 'cancel' the insert action. That will prove to be a major headache. That will create a significant blocking issue and a major performance problem.

Unless, of course, you do not assign the 'number' until the data is saved. At that point, the number can be reported back to the user (post-hoc), and there is not a cancellation issue.

|||

Thanks Arnie,

I get what you mean. I think I will follow your second idea.

Then just out of curiosity, SQL server can perform record locking?

Best regards,

Tee Song Yann

|||

Yes. Look up [ Hints -Locking ] in Books Online.

There are several ways to accomplish locking -it depends upon what you want to accomplish. With the exception of TRANSACTIONS, and in the absence of a clear understanding of the implications and effects, it is best to leave record locking behavior to the server.