Solved

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

Posted on 2008-11-03
3
565 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 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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