October 1, 2007 at 4:23 am
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.
October 1, 2007 at 5:43 am
If you have a shared lock on a resource, exclusive locks will not be allowed on it until your shared lock is released. You ca get this to happen using an updatable server-side cursor with ADO.
That being said, without seeing the structure of your database, it is difficult to help you with your strategy. Locking objects is tricky. You typically want to lock things for the shortest duration possible, and you need to be careful to take into consideration what other people will be looking at before and after an update is made. Concurrency gets complicated quickly.
Typically, most applicaiton developers like to use optomostic concurrency allowing users to see data and make changes and error out the second user's changes if something was modified between the time the retrieved what they are looking at and the time they posted their change. This seems like what you are trying to do, but you are trying to ensure a very detailed level of data consistency for your business rules.
In my opinion, putting the enforcement of data integrity at the lowest level possible is
always a good idea. What I mean by this is let the database control data integrity whenever possible, if it cannot (performance, unsupported complexity, etc) move the data integrity into your application business logic layer. That being said, can you create a check constraint that will verify that no resource is booked at the same time twice? If you can, your locking will not matter and you can keep things simple. In fact, you can even skip the business logic in your application for checking for conflicts and simply check what conflicted after you get a check constraint error. This would also ensure that the new developer you hire a year from now cannot insert a record using Management Studio that conflicts with your rules.
If you think performace of this type of constraint will be a problem, remember this. We have a 14 million row table that has a check constraint to verify no two records have overlap in their dates when it is for the same store. It added 90 milliseconds to a single insert, and 7 seconds to an insert of 50,000 records.
October 1, 2007 at 7:15 am
Hi Michael
Thanks for the quick reply.
I agree with you that data integrity should be left to the database as much as possible, and I would love to be able to do that for this project, but the more I think about it the less I think it will work.
Re-reading my original post, I realise that I didn't really include enough detail in my original post.
A bit more on the table structure:
The booking record defines the start and end time, and references one of the resources with a foreign key, the other 2 resource types are in a one to many relationship with the booking.
creating one booking record will entail multiple selects to check existing data, and multiple inserts to multiple tables, so I assumed that it would not be possible to create a constraint to handle the integrity checks.
You were saying that a shared lock will prevent any other processes from obtaining an exclusive lock, but this will only be for the records that have been read, adn not the whole table is this correct?
October 1, 2007 at 7:26 am
You can use lock escalation hints to cause any select statement to take a shared lock on an entire table. Check books online (TABLOCK). You could also make your cursor the entire table. In ADO, you can open a recordset for an entire table and only get one record. If it is a server-side query, it does not need to pull all records.
And then back to the constraint. Using a UDF, you can make a check constraint that references other tables. If you are able to run a query in your database that checks for resource overlap, you can create a check constraint. Depending on the complexity of the database and the number of records, you can lose efficiency doing this, but it is worth persuing. You should try creating the UDF for this and check the performance. You may be surprised.
October 2, 2007 at 5:33 am
You could use SET TRANSACTION ISOLATION LEVEL REPEATABLE READ or SET TRANSACTION ISOLATION LEVEL SERIALIZABLE before your BEGIN TRAN statement to lock all SELECTED rows until the transaction is finished. You can also use the SERIALIZABLE (or HOLDLOCK) and REPEATABLEREAD table hints to limit the locking to specific tables. This raises the possibility of deadlocks, which your application must be prepared to handle. TABLOCK is probably a bad idea, you should SELECT and lock only the rows you need for your transaction.
To try to avoid deadlocks, you can use SET LOCK_TIMEOUT 0 or the NOWAIT table hint to force an immediate error if any needed row is already locked. Your application should then immediately rollback the transaction and start over.
October 2, 2007 at 5:52 am
Hi Scott
Thanks for the reply.
And thanks for the last advice Michael.
I believe I have resolved the issue, in theory at least as it hasn't been tested yet.
The main issue was contention. Getting a lock on the rows involved in one transaction would not prevent another writing a record that violates a rule because of the first, as I didn't think it would be possible to create a constraint for the business rule.
This was compounded by the fact that there is another application (the version 1 app that is being replaced) which also writes to the database, and I have limited access to change it.
Following Michaels advice I have created several UDF's which are used as constraints, on the various tables involved. Resources are checked for conflicts one at a time, as they are inserted, if there is any conflict, all inserts relating to that booking are rolled back - which guarantees consistency. I don't care which trans gets rolled back, as long as one of them does!
There is only light traffic in the database, so even if I haven't written the best performing functions, it shouldn't be a problem.
thanks for the help
October 2, 2007 at 7:01 am
Tim Chapman (10/2/2007)
Hi ScottThanks for the reply.
And thanks for the last advice Michael.
I believe I have resolved the issue, in theory at least as it hasn't been tested yet.
The main issue was contention. Getting a lock on the rows involved in one transaction would not prevent another writing a record that violates a rule because of the first, as I didn't think it would be possible to create a constraint for the business rule.
This was compounded by the fact that there is another application (the version 1 app that is being replaced) which also writes to the database, and I have limited access to change it.
Following Michaels advice I have created several UDF's which are used as constraints, on the various tables involved. Resources are checked for conflicts one at a time, as they are inserted, if there is any conflict, all inserts relating to that booking are rolled back - which guarantees consistency. I don't care which trans gets rolled back, as long as one of them does!
There is only light traffic in the database, so even if I haven't written the best performing functions, it shouldn't be a problem.
thanks for the help
If you are using stored procedures, which you should, you could implement Scott's solution and it would apply to the 1st app as well.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply