Solved

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

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

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.
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
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 to return specific rows and columns, with various degrees of sorting and limits in place.

746 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

13 Experts available now in Live!

Get 1:1 Help Now