Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 575
  • Last Modified:

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

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
robrodp
Asked:
robrodp
  • 2
1 Solution
 
richard_cristCommented:
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
 
robrodpAuthor Commented:
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
 
richard_cristCommented:
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now