Record Locking in SQL

Posted on 2005-04-19
Medium Priority
Last Modified: 2010-03-19
I SELECT a record from my SQL database, display it on my VB form, edit the data and finally UPDATE the record back to the SQL database.

My problem is that whilst editing the data, anyone else could load that record and start to edit it. I need to lock the record so that no one else would be able to load into the edit form.

I have looked at SQL, but the locking seems only to apply to a single transaction. I can lock the record whilst SELECTING or UPDATING, but as soon as that is completed the record is unlocked. I cannot keep it locked for twenty minutes whilst I edit the data. (or am I mistaken).

Is there a built in method of record locking, or do I need to design my own Lock table mechanism?

Question by:MortimerCat
LVL 75

Expert Comment

by:Anthony Perkins
ID: 13815306
>>Is there a built in method of record locking, or do I need to design my own Lock table mechanism?<<
No and yes.

Accepted Solution

obahat earned 1000 total points
ID: 13817277
In your SQL statement, you can:
1. Specify the transaction isolation level:
If it is serializable, and you read from the table with a lock (and the whole operation is transacted), then you can guarantee that oo one would made modifications to the underlying table.
Other could read from the table using the NOLOCK querying hint.
This is not a good practice through.

If you do choose to puruse this or similar solutions, make sure that you only lock the row (i.e., read with ROWLOCK) or page (PAGLOCK) that contains that row. Otherwise you'd be locking others for a while, which is not a good practice.

2. You can only achieve this if the entire operation is transacted. It would not be wise to transact such a big operation (i.e., get data, display in form, update data).
Instead, you can simply record the fact that that row is updated (say, by storing another column which contains a bit - BeingEdited for example, together with EditingUserID) which tells others that they can't update that record.

Hope this help.

LVL 70

Expert Comment

by:Scott Pletcher
ID: 13819488
In general it's not a good idea to lock a row while a person is viewing/editing it.  What if he/she goes to lunch?

But, if you need to in your specific case, probably the best way is to add a bit column to the table to indicate that it's "in use" (of an int to store the id of the person updating, if you can determine that).  Note that this means that every SELECT from the table becomes an UPDATE *and* a SELECT.  

Author Comment

ID: 13823098
Thanks, you confirmed what I feared!


Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question