How To Lock in Sql Server 2005 The record When It access by Other Person?

Question

How To Lock in Sql Server 2005 The record When It access by Other Person?

bharat.mundhada

Valued Member

Points: 69
More actions
September 14, 2008 at 11:13 pm

#192822

Lock in Sql Server 2005 The record When It access by Other Person
Suppose Table "test" contain 10 records .
If Person one is editing record Number "1" then other person could not get access to update record Number "1" he only view that record doesn't have permission to edit that record.

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

SQL Server does that automatically. If a user is updating a row then that row is locked exclusive and cannot be read or changed by anyone else.

Have I misunderstood what you want?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

archidrb SSC Enthusiast Points: 170 More actions · Answer 2

Gila,

I read this as "it's normal for a SQL DB to lock at the record level". I have a SQL Server 2005 backend connected to MS Access FEs using an ODBC connection. I learned today that no matter what I set for record locking in the front end, it is ignored by an ODBC connection.

I have a "work flow" piece. A user opens into a form a record from a main table (table 1) that pulls into a sub-form a second set of related record(s) from a second table. It is my assumption that the first person to open this record locks it (table 1 record) from another person opening it. This is not happening in my FE. I continue to get complaints that two people will open the same record.

I added to the table (and forms) a "checkout" bubble that the user checks as soon as the open the record (from the first table), saves the record and moves the curser to the lower portion of the form. Then told the system to ignore the record (move to the next) if the bubble is checked (-1 in the bit field). My users say that they check the bubble, but a second person can still open the same record. I cannot replicate the issue, but can see it in the data that one person marks the main record (table 1), but someone else marks the related records (table 2). Since a second person should not be able to get to the related records without opening the main record, I don't understand.

Is there a way to create record level locking in SQL that the query in the Access FE will be able to "see" and correctly open the next main record each time when the previous record is locked (or completed)? Or is it possible that there is a measurable delay in the connection from FE to BE back to FE with the update that the record has been locked?

Thanks for your help.

rahul_vasanth SSC Journeyman Points: 85 More actions · Answer 3

Even I'm facing the same problem.

Did you find a suitable solution for this ?

archidrb SSC Enthusiast Points: 170 More actions · Answer 4

No.

A SQL guru assisted me in creating a stored proc that opens the data into the form in the front end and that worked great EXCEPT the data then becomes read-only (stick out tongue, make raspberry noise). I also tried a function, but the same result.

So, no solution yet.

Sergiy SSC Guru Points: 110206 More actions · Answer 5

You need to read about locking in SQL Server to understand the consequences of what you are asking for. When you get to understanding you stop to ask for trouble.

You may try to work-around by flagging a record you have opened for editing.

But then you need to figure out how to release blocked record when connection is dropped or when user went home leaving the record blocked, or... Try figure.

_____________
Code for TallyGenerator