Locking some rows on a SqlServer db

  • Thread starter Thread starter Giulio Petrucci
  • Start date Start date
G

Giulio Petrucci

Hi there,

is there a (simple) way to lock some rows in a Sql Server db table from
a C# application?

Thanks,
Giulio
--
 
Giulio Petrucci said:
is there a (simple) way to lock some rows in a Sql Server db table from a
C# application?
Click to expand...


If you start a transaction that "touches" those rows, they will be
locked untill the transaction is either rolled back or committed.

The type of locks (shared, update, exclusive) will depend on the
isolation level of the transaction and the kind of operation that you
perform on them (read or modify). Be aware of possible automatic lock
escalation on the server, which may lock additional rows that you didn't
intend to lock. Also bear in mind that depending on the configuration of the
server there may be a timeout in case some other process tries to access a
locked row. Also consider the possibility od deadlocks, which would cause
termination of one of the deadlocked processes.

From the preceding paragraph you will note that locking rows on a Sql
Server is not a trivial proposition and that you should devote some time to
study the Sql Server documentation. However, from the point of view of C#
things are relatively simple: use a SqlTransaction to start the transaction
with your required isolation level, and then use one or more SqlCommands to
access the rows that you want to lock.
 
Hi Alberto,

first of all thanks for your reply,

Alberto said:
If you start a transaction that "touches" those rows, they will be
locked untill the transaction is either rolled back or committed.
Click to expand...
[cut]

Unfortunatly the db I'll have to work on, does not support
transactions... :-(
From the preceding paragraph you will note that locking rows on a Sql
Server is not a trivial proposition and that you should devote some time
to study the Sql Server documentation.
Click to expand...

I see. Could you link me anithing about?
However, from the point of view
of C# things are relatively simple: use a SqlTransaction to start the
transaction with your required isolation level, and then use one or more
SqlCommands to access the rows that you want to lock.
Click to expand...

Thanks,
Giulio
--
 
If you start a transaction that "touches" those rows, they will be
locked untill the transaction is either rolled back or committed.
Click to expand...
[cut]

Unfortunatly the db I'll have to work on, does not support transactions...
:-(
Click to expand...

I think you should tell us WHY you're trying to lock rows, because then we
may be able to suggest other methods which will achieve your ultimate goal.
 
Giulio said:
Alberto said:
If you start a transaction that "touches" those rows, they will be
locked untill the transaction is either rolled back or committed.
Click to expand...
[cut]

Unfortunatly the db I'll have to work on, does not support
transactions... :-(
Click to expand...

Then it is not MS SQLServer.

So start by telling us what database server it is.

Arne
 
Hi Arne,
Then it is not MS SQLServer.
So start by telling us what database server it is.
Click to expand...

AFAIK (as I'm not so "skilled" in db administration), transaction must
be explicitly enabled on a Sql Server DMBS, right? So the
project-manager told me to avoid trasnactional scopes, as in the
environment we're going to deploy our application trasnactions are not
allowed.

Thanks,
Giulio
--
 
Hi Jeff,

Jeff said:
I think you should tell us WHY you're trying to lock rows, because then we
may be able to suggest other methods which will achieve your ultimate goal.
Click to expand...

To avoid concurrent writings.

Thanks,
Giulio
--
 
Giulio Petrucci said:
AFAIK (as I'm not so "skilled" in db administration), transaction must be
explicitly enabled on a Sql Server DMBS, right?
Click to expand...

Every Database Management System worthy of that name (including, of
course, Microsoft SQL Server) ALWAYS uses transactions, even if they are
implicit.
For instance, if you execute an "Update MyTable Set col=value where...",
then the server will treat that command as a transaction all by itself. It
will lock all the records that are updated and then release the locks when
its work is done (or roll back all the changes if an error is encountered
when updating one of the rows). Other processes will automatically be
prevented from accessing the modified rows until the query (or rather, the
implicit transaction started by this query) is finished.

The only instance when you need to explicitly script a transaction is the
case when you are doing a sequence of several changes (several UPDATEs,
INSERTs and DELETEs), and you want to "group" them so that they all succeed
or they all fail (and meanwhile, you don't want any other process
interferring or reading data that are not yet committed, so everything needs
to be locked).
This does not need to be "enabled" in your environment, at least in MS
Sql Server. The server _always_ supports starting a transaction, executing
queries, and committing the transaction. Concurrent writings are
automatically blocked while the transaction is being done.
 
Back
Top