Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
bharat.mundhada
bharat.mundhada
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 21
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)SSC Guru (54K reputation)

Group: General Forum Members
Points: 54971 Visits: 44680
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
archidrb
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 7
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
rahul_vasanth
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 4
Even I'm facing the same problem.

Did you find a suitable solution for this ?
archidrb
archidrb
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 7
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
Sergiy
SSCertifiable
SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)SSCertifiable (6.5K reputation)

Group: General Forum Members
Points: 6456 Visits: 11548
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search