?
Solved

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

Posted on 2008-11-03
3
Medium Priority
?
570 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

765 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