Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How To Lock in Sql Server 2005 The record When It access by Other Person? Expand / Collapse
Author
Message
Posted Sunday, September 14, 2008 11:13 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, January 15, 2010 1:24 AM
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.
Post #569245
Posted Monday, September 15, 2008 1:50 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:53 AM
Points: 42,822, Visits: 35,952
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 2008, MVP
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

Post #569276
Posted Thursday, April 9, 2009 11:07 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 10:03 AM
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.
Post #694235
Posted Thursday, May 28, 2009 12:59 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, June 26, 2010 11:17 PM
Points: 1, Visits: 4
Even I'm facing the same problem.

Did you find a suitable solution for this ?
Post #724835
Posted Thursday, May 28, 2009 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 14, 2009 10:03 AM
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.
Post #725044
Posted Thursday, May 28, 2009 2:41 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Monday, August 18, 2014 9:16 PM
Points: 4,576, Visits: 8,347
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.
Post #725467
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse