Locking a record in an MS-SQL Server 
Author Message
 Locking a record in an MS-SQL Server

Hello to All,

I'm writing an application in C# accessing an Microsoft SQL Database Server
for consulting, creating and updating . The Database is accessed via the
internet , the local network or even via telephone. Because the risk of
losing connection I stay as short as possible connected to the database and
use only INSERT, UPDATE or SELECT statements to perform my duty.

If the user selects a record, I get it with a SELECT. When the user has
changed data I first read the old record, compare it to see if an other user
did not change the record and then I write it to the database using an
UPDATE.

I was just wondering if there is a way to  lock  a record using an SQL
instruction or whatever from the moment the user starts to make changes,
close the connection. When all changes are made, open the connection, write
the changes and then free up the lock before closing the connection at new.
This in combination with triggers on the database who frees the locks when a
user is not updating the record within a predefined time.

If anyone has experience working in that way I would be thankful to hear how
you solved the problem.

Greetings,

Jean Paul.



Sat, 11 Dec 2004 21:43:33 GMT  
 Locking a record in an MS-SQL Server



Quote:
> Hello to All,

> I'm writing an application in C# accessing an Microsoft SQL Database
Server
> for consulting, creating and updating . The Database is accessed via the
> internet , the local network or even via telephone. Because the risk of
> losing connection I stay as short as possible connected to the database
and
> use only INSERT, UPDATE or SELECT statements to perform my duty.

Stored Procedures are faster. If you're concerned about speed, you
should be using these.

Quote:
> If the user selects a record, I get it with a SELECT. When the user has
> changed data I first read the old record, compare it to see if an other
user
> did not change the record and then I write it to the database using an
> UPDATE.

Checking this is pointless because no matter how many times you check,
there could be someone updating the very second after you check.

What you need are transactions.

Quote:

> I was just wondering if there is a way to  lock  a record using an SQL
> instruction or whatever from the moment the user starts to make changes,
> close the connection. When all changes are made, open the connection,
write
> the changes and then free up the lock before closing the connection at
new.
> This in combination with triggers on the database who frees the locks when
a
> user is not updating the record within a predefined time.

> If anyone has experience working in that way I would be thankful to hear
how
> you solved the problem.

It is possible to lock rows, however it's not recommended from what I hear.
What you should consider using are transactions.

-c



Sat, 11 Dec 2004 21:51:16 GMT  
 Locking a record in an MS-SQL Server
Hello Chad,

tnx for responding so fast.



Quote:



> > Hello to All,

> Stored Procedures are faster. If you're concerned about speed, you
> should be using these.

The query's are most of the time verry simple so I don't know if I gain a
lot by using stored procedures for them.

Quote:

> Checking this is pointless because no matter how many times you check,
> there could be someone updating the very second after you check.

> What you need are transactions.

While Im checking theif there are differences, i stay connected and use a
transaction to SELECT, compare and UPDATE the record. So in the mean while
nobody can chance the record.

Is it posible to begin a transaction, close the database connection and
after a while reopen the connection and end my transaction?

Quote:

> It is possible to lock rows, however it's not recommended from what I

hear.

Quote:
> What you should consider using are transactions.

What's the trick :-) ?

Quote:
> -c

JP


Sat, 11 Dec 2004 22:47:45 GMT  
 Locking a record in an MS-SQL Server
Jean, see inline responses...



Quote:
> Hello Chad,

> tnx for responding so fast.





> > > Hello to All,

> > Stored Procedures are faster. If you're concerned about speed, you

> > should be using these.

> The query's are most of the time verry simple so I don't know if I gain a
> lot by using stored procedures for them.

You'd be suprised.

- Show quoted text -

Quote:

> > Checking this is pointless because no matter how many times you check,

> > there could be someone updating the very second after you check.

> > What you need are transactions.

> While Im checking theif there are differences, i stay connected and use a
> transaction to SELECT, compare and UPDATE the record. So in the mean while
> nobody can chance the record.

> Is it posible to begin a transaction, close the database connection and
> after a while reopen the connection and end my transaction?

No. It's good, though, that you are using transactions. However, if
you're in a transaction, there's no need to compare because the values
will not have changed inside the transaction.

Quote:
> > It is possible to lock rows, however it's not recommended from what I
> hear.

> > What you should consider using are transactions.

> What's the trick :-) ?

Why do you need to lock rows? It kind of defeats the purpose of a
multi-user database if you're locking things all the time.

What is it that you're scared of? People stepping over each other?

If you must:
http://msdn.microsoft.com/library/en-us/architec/8_ar_sa2_2sit.asp

Locking is hairy business though. Deadlock scenarios are easy
to come by. Fortunately, SQL Server will help prevent a total
deadlock, but it'll still cause exceptions to be thrown in your
application.

-c



Sat, 11 Dec 2004 22:59:55 GMT  
 Locking a record in an MS-SQL Server
Jean Paul,

    I think your design is flawed.  Considering that you can not guarantee
that you are in a connected state all the time, it would be unreasonable for
SQL server to allow you to place a lock on a record in the database (you
might never come back and unlock it).

    Having said this, when the select is first performed, get the timestamp
for the record.  Once you have this, compare the timestamp to the same
record before you insert it.  If it is different, then you know that someone
else touched the data and that your operation should be aborted.

    If, however, you can maintain the connection, then Chad's original
suggestion of using transactions would be the best way to go.

    Hope this helps.

--
               - Nicholas Paldino [.NET MVP]



Quote:
> Hello Chad,

> tnx for responding so fast.





> > > Hello to All,

> > Stored Procedures are faster. If you're concerned about speed, you

> > should be using these.

> The query's are most of the time verry simple so I don't know if I gain a
> lot by using stored procedures for them.

> > Checking this is pointless because no matter how many times you check,

> > there could be someone updating the very second after you check.

> > What you need are transactions.

> While Im checking theif there are differences, i stay connected and use a
> transaction to SELECT, compare and UPDATE the record. So in the mean while
> nobody can chance the record.

> Is it posible to begin a transaction, close the database connection and
> after a while reopen the connection and end my transaction?

> > It is possible to lock rows, however it's not recommended from what I
> hear.

> > What you should consider using are transactions.

> What's the trick :-) ?

> > -c

> JP



Sat, 11 Dec 2004 23:08:09 GMT  
 Locking a record in an MS-SQL Server
Chad,



Quote:
> Jean, see inline responses...

> > The query's are most of the time verry simple so I don't know if I gain
a
> > lot by using stored procedures for them.

> You'd be suprised.

I'll give it a try.

Quote:

> > While Im checking theif there are differences, i stay connected and use
a
> > transaction to SELECT, compare and UPDATE the record. So in the mean
while
> > nobody can chance the record.

> > Is it posible to begin a transaction, close the database connection and
> > after a while reopen the connection and end my transaction?

> No. It's good, though, that you are using transactions. However, if
> you're in a transaction, there's no need to compare because the values
> will not have changed inside the transaction.

I use the transaction only between the SELECT for comparing and the UPDATE,
so I'm protected again changes in this time. But the time between a user
changes his first character in a record and the time he apply the store
button is not protected. I think it is not a good idee to hold a transaction
open for minutes on a network where the risks of losing connection is great.

Quote:
> > > It is possible to lock rows, however it's not recommended from what I
> > hear.

> > > What you should consider using are transactions.

> > What's the trick :-) ?

> Why do you need to lock rows? It kind of defeats the purpose of a
> multi-user database if you're locking things all the time.

> What is it that you're scared of? People stepping over each other?

> If you must:
> http://msdn.microsoft.com/library/en-us/architec/8_ar_sa2_2sit.asp

> Locking is hairy business though. Deadlock scenarios are easy
> to come by. Fortunately, SQL Server will help prevent a total
> deadlock, but it'll still cause exceptions to be thrown in your
> application.

Well as I explained before, I would give the user his changes a kind of
protection for the time he is editing a form, without having to hold the
transaction open for all the time.

JP



Sat, 11 Dec 2004 23:16:05 GMT  
 Locking a record in an MS-SQL Server
Nicholas,



Quote:
> Jean Paul,

>     I think your design is flawed.  Considering that you can not guarantee
> that you are in a connected state all the time, it would be unreasonable
for
> SQL server to allow you to place a lock on a record in the database (you
> might never come back and unlock it).

For that reason I was thinking of a trigger freeing the lock if the user did
not after a sertain time.

Quote:
>     Having said this, when the select is first performed, get the
timestamp
> for the record.  Once you have this, compare the timestamp to the same
> record before you insert it.  If it is different, then you know that
someone
> else touched the data and that your operation should be aborted.

I do a thing like this comparing (and sauvegarding) the fieldcontent so that
I can see if the fields my user can change are changed or not.

Quote:
>     If, however, you can maintain the connection, then Chad's original
> suggestion of using transactions would be the best way to go.

I 100% agree thats the best way but the connections are so unstable that I
can't rely on them for a long time (client's IP is changing etc.)

Quote:

>     Hope this helps.

> --
>                - Nicholas Paldino [.NET MVP]



> > Hello Chad,

> > tnx for responding so fast.





> > > > Hello to All,

> > > Stored Procedures are faster. If you're concerned about speed, you

> > > should be using these.

> > The query's are most of the time verry simple so I don't know if I gain
a
> > lot by using stored procedures for them.

> > > Checking this is pointless because no matter how many times you check,

> > > there could be someone updating the very second after you check.

> > > What you need are transactions.

> > While Im checking theif there are differences, i stay connected and use
a
> > transaction to SELECT, compare and UPDATE the record. So in the mean
while
> > nobody can chance the record.

> > Is it posible to begin a transaction, close the database connection and
> > after a while reopen the connection and end my transaction?

> > > It is possible to lock rows, however it's not recommended from what I
> > hear.

> > > What you should consider using are transactions.

> > What's the trick :-) ?

> > > -c

> > JP



Sat, 11 Dec 2004 23:23:10 GMT  
 Locking a record in an MS-SQL Server


Quote:
> I was just wondering if there is a way to  lock  a record using an SQL
> instruction or whatever from the moment the user starts to make changes,
> close the connection. When all changes are made, open the connection, write
> the changes and then free up the lock before closing the connection at new.
> This in combination with triggers on the database who frees the locks when a
> user is not updating the record within a predefined time.

If I understand you correctly, other than the non-persistent connection, you
are describing pessimistic locking.  ADO.NET really doesn't support anything
but optimistic locking.  If you really must implement pessimistic locking you
have to use legacy ADO or SQL pass-thru.  In addition, pessimistic locking
pretty much by definition requires persistent database connections, which don't
scale well.

In most real world scenarios, actual conflicts with optimistic locking schemes
are fairly rare.  You have to check / provide for them, to be sure, but users
by and large are not going to be annoyed by once in a great while having to
redo an edit, as they would be by finding records inaccessible and/or database
performance bogged down by static locks.

Optimistic locking is really the way to go in the vast, vast majority of modern
apps, and there is really no way around this fact.

--Bob



Sat, 11 Dec 2004 23:25:19 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Records being locked in SQL Server database

2. Perform SQL statement in VC++ against MS SQL Server 7.0

3. Help - Locking issue in sql server 2k in 6.5 comp mode

4. entry locking in SQL server using ODBC

5. Getting a random record from a SQL Server table

6. Error updating SQL-Server record via ODBC (CRecordset)

7. Problem Fetching Records in Bulk using MFC/ODBC/SQL Server

8. Cannot delete SQL server duplicate records

9. Adding records to SQL Server

10. CRecordset: Error when adding records SQL Server view

11. ADO record binding from SQL server to String type

12. Update call to SQL Server with large record never returns

 

 
Powered by phpBB® Forum Software