How to LOCK a table for INSERT and UPDATE, but not SELECT for duration of SqlTransaction?

  • Thread starter Thread starter sticky
  • Start date Start date
S

sticky

Hi

I need to be able to lock a table against INSERT and UPDATE, but not
SELECT, for the duration of a transaction.
The transaction will be defined at the application level in c#, and
then use stored procedures to make multiple selects and then an
insert.
What is the best way of doing this?

Description of the system:

I am developing a scheduling system in c# with sql 2005.
The system allows users to book resources for a specified period of
time.
each booking may contain multiple resources, which cannot be involved
in multiple bookings for any one time frame.

When a new booking request is received, the app must select all booked
resources for the time frame specified in the received booking request
and check that the resources specified in the booking request are not
involved in any other booking in that time frame.
This will entail multiple selects, and iterating through each of the
result sets to be able to determine if a booking request is valid.
 
A bit OT, but...

You can monkey with the isolation level of the transaction, and hints
like NOLOCK in the query, but you run the risk of non-repeatable reads
etc... serializable is the safest option, but introduces more locks.

To avoid escalation deadlocks (i.e. you query a range to check
validity, then if successful attempt to INSERT/UPDATE, only to find
you just deadlocked), you can issue an UPDLOCK hint on first SELECT to
ensure you have an exclusive lock sooner (rather than a shared lock).

The main thing, however, is to avoid long-running operations in the
middle of transactions; the main killers being things like user
confirmation dialogs (which don't help if the user has gone to lunch /
home while the transaction is alive).

Any help?

Marc
 
Hi,

Ina ddition to Marc's comments I suggest you to post this question in the
SQL NG
 
You just can't do this if the target of the SELECT, INSERT, and UPDATE
are the same. Placing a lock on one in a transaction is going to block out
access to the target rows (or pages, possibly) in another transaction. Like
Marc says, you can play with the isolation level, but you run the risk of
repeatable reads.

How long is your total operation taking? Are you seeing a good amount
of contention when you are performing your operations?

Are you loading all the data for your booking requests one-by-one, or
are you doing the check on the database server? You might be better off
with the latter because of the way that SQL Server handles set processing
(assuming the logic is transferrable).
 
Hi

I need to be able to lock a table against INSERT and UPDATE, but not
SELECT, for the duration of a transaction.
The transaction will be defined at the application level in c#, and
then use stored procedures to make multiple selects and then an
insert.
What is the best way of doing this?

Description of the system:

I am developing a scheduling system in c# with sql 2005.
The system allows users to book resources for a specified period of
time.
each booking may contain multiple resources, which cannot be involved
in multiple bookings for any one time frame.

When a new booking request is received, the app must select all booked
resources for the time frame specified in the received booking request
and check that the resources specified in the booking request are not
involved in any other booking in that time frame.
This will entail multiple selects, and iterating through each of the
result sets to be able to determine if a booking request is valid.
Click to expand...

If you have the database enforcing the "no resources multiply booked
at a time", say via an insert trigger, then do your selects separately
(perhaps combine them into a stored procedure to cutdown on
roundtrips). Then do your inserts/updates in a single transaction.
If the transaction fails, rollback and notify the user that there's a
resource conflict. Be sure to handle multirow inserts if applicable.
 
In SQLS 2005 you can configure the database to use snapshot isolation.
This creates a version of the row in tempdb that prevents readers from
blocking writers and writers from blocking readers. No locks are held
on the underlying tables during a select. You can enable it on a
per-database basis, or make it the default. You still get concurrency
conflicts during inserts and updates. See
http://msdn2.microsoft.com/en-us/library/ms189050.aspx for more
information.

-mary
 
Back
Top