Solved

ms sql 2005 how can I lock a record for no access and then release it?

Posted on 2008-11-03
3
557 Views
Last Modified: 2008-11-10
I have records that are accssed and I want to lock them in a manner that if other users want to access them they have to wait until they are unlocked....

I access my ms sqldatabse from asp scripts
0
Comment
Question by:robrodp
  • 2
3 Comments
 
LVL 3

Expert Comment

by:richard_crist
ID: 22870721
This is not a complete answer to your question, but since you indicate that you are a beginner on this subject I will start with the link below.  The link discusses starting an explicit transaction.  In an explicit transaction you can state up front that you want exclusive access or locking.  Once you start an explicit write transaction any rows you update are locked until you commit your change or rollback your change.

You might also reply with more details if the link below does not answer your question.  We can then provide a more detailed answer to your question.  I am not an ms sql specific expert, but your question applies to locking in general.  All database engines provide transaction ability, which is what you will need to accomplish what you are trying to do.

http://msdn.microsoft.com/en-us/library/ms175127.aspx

0
 

Author Comment

by:robrodp
ID: 22870921
Its really too theoretical for me.
I need some actual code that when some one has access to a record like:
select  * from table where id=1 (somehow lock the record)
something happens (that does not take too long)
somehow the record is unlocked so
If somebody sends the same query (select  * from table where id=1)

but sql waits until the record is unlocked by the first user

If more queries are made ((select  * from table where id=1) they are
honored as the records are unlocked





0
 
LVL 3

Accepted Solution

by:
richard_crist earned 500 total points
ID: 22871844
I appreciate your additional information.  Even though you are somewhat new to this you have a good understanding of what you want.

The information I discuss below is taken from the link:

http://www.sqlteam.com/article/introduction-to-locking-in-sql-server

That link has a good description of locking.  Basically if you use an sql statement such as the one below you are automatically acquiring an exclusive lock on the data rows that match the where clause.  No one can read those records until the update is either committed or rolled back.  If the update takes 3 minutes followed by a commit or rollback, then no one can read those rows for three minutes.  I believe this is what you are looking for.  Using an sql statement like the one below should automatically do what you are needing.  An actual SQL Server 2005 expert may want to confirm this.

BEGIN TRAN

USE AdventureWorks

UPDATE Person.Address
SET AddressLine2 = 'Test Address 2'
WHERE AddressId = 5

COMMIT (or ROLLBACK if needed)

0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

867 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now