Table locking? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Table locking?

Author

Topic

groston
Yak Posting Veteran

61 Posts

Posted - 2005-11-20 : 17:07:09

We are developing a scheduler application - think MS Outlook for reserving common facilities. The questions are related to the need to lock certain tables whening storing reservations.

When a user accesses the system, s/he is provided a 'snapshot' of the reservations for the particular resource for the selected day. Consider this situation: User A and User B both access a resource's schedule for the same day. When the resource's scheudle for that day is returned to both users, a particular time slot is available. Both user decide to reserve this time slot. Without a check in the database itself, the same slot could get reserved twice, and this is not acceptable.

So the questions are these:
1) For an application of this type, should the reservation tabel be momentarily locked when trying to create a reservation such that a query can first be performed to ensure that the time slot is still available?
2) If table locking is the recommended approahc, how is this done? (i.e., what are the TSQL statement(s) needed?)

The application is ASP.NET 1.1 (will be ported to 2.0 next year). The database is SQL Server 2000 SP3 (will be ported to SQL Serve 2005 next year). The table in question grows by approximately 200 records per day.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-20 : 17:15:09

well usualy the simplest way is to have a status column. original status is 1.
when updated it's set to 2 and if another one wants to update it you return an error.
that's it.

Go with the flow & have fun! Else fight the flow

groston
Yak Posting Veteran

61 Posts

Posted - 2005-11-20 : 17:29:38

spirit1

Thanks for the quick reply. I obviously did not make myself clear in the first posting, so let me try again.

My concern is related to a race condition - unless the reservation table is locked, there is not way to ensure that a reservation record won't be created between checking for a reservation and creating one. Note that this table has distinct records for reservations and is not a 'grid' of resources and available times.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-11-20 : 17:52:33

hmm... could you provide table structure for this?
i'm not a big fan of table locking in such cases...

Go with the flow & have fun! Else fight the flow

groston
Yak Posting Veteran

61 Posts

Posted - 2005-11-20 : 21:17:20

Nothing special:

Reservation ID int, primary key
ResourceID int
ReserverID int
ReservationStart datetime
ReservationEnd datetime

and many other columns that are not of importance to this discussion...

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-11-20 : 22:21:36

It is not a good idea to try to use table locking for something like this. You need to do the locking in your application logic.

The best way to do this depends on the exact needs of your application. For example, if you can only reserve resources for discrete time periods, you might be able to use primary keys or unique indexes to prevent the resource from being overallocated.

If the time periods are not defined exactly, for example, I want a room from 14:00 to 16:00 and you want it from 13:30 to 15:00, then you will have to do an insert that takes this kind of overlap into account. You may end up with an insert that looks like:


insert into Reservation (col list...)
Select
	ResourceID		= @ResourceID,
	ReserverID		= @ReserverID,
	ReservationStart	= @start_datetime,
	ReservationEnd		= @end_datetime,
	more cols...
where
	not exists
	(
	select *
	from
		Reservation
	where
		ResourceID	= @ResourceID	and
		ReserverID	= @ReserverID	and
		(( 
		@start_datetime >= ReservationStart
		@start_datetime <  ReservationEnd
		) or
		( 
		@end_datetime >= ReservationStart
		@end_datetime <  ReservationEnd
		) or
		( 
		@Start_datetime < ReservationStart
		@end_datetime   > ReservationEnd
		))

Most of the time, you should just use a "first one wins" method, and have the application inform someone if the slot is no longer available. This means that you might have a situation were a time slot appears open, but is no longer available by the time a user actually selects it.

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources