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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » T-SQL » How To Lock in Sql Server 2005 The record...

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

Rate Topic

Display Mode

Topic Options

Author

Message

bharat.mundhada

Posted Sunday, September 14, 2008 11:13 PM

Forum 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

GilaMonster

Posted Monday, September 15, 2008 1:50 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:08 PM
Points: 38,099, Visits: 30,392

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

archidrb

Posted Thursday, April 09, 2009 11:07 AM

Forum 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

rahul_vasanth

Posted Thursday, May 28, 2009 12:59 AM

Forum 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

archidrb

Posted Thursday, May 28, 2009 7:41 AM

Forum 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

Sergiy

Posted Thursday, May 28, 2009 2:41 PM

SSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 8:01 AM
Points: 4,557, Visits: 8,237

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 »

Permissions