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. 5 1903
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
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).
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com
"sticky" <st********@gmail.comwrote in message
news:11**********************@r29g2000hsg.googlegr oups.com...
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.
Hi,
Ina ddition to Marc's comments I suggest you to post this question in the
SQL NG
"sticky" <st********@gmail.comwrote in message
news:11**********************@r29g2000hsg.googlegr oups.com...
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.
On Oct 1, 6:27 am, sticky <stickym...@gmail.comwrote:
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.
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
On Mon, 01 Oct 2007 10:27:26 -0000, sticky <st********@gmail.com>
wrote:
>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.
This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: M Wells |
last post by:
Hi All,
I have a table that holds pregenerated member IDs.
This table is used to assign an available member id to web site
visitors who choose to register with the site
So, conceptually the...
|
by: Bryan Guilliams |
last post by:
I'm trying to come up with an elegant, simple way to compare two
consecutive values from the same table.
For instance:
SELECT TOP 2 datavalues FROM myTable ORDER BY timestamp DESC
That...
|
by: mahajan.sanjeev |
last post by:
Hi All,
I am using a SQLTransaction in a .Net application to insert records
into a SQL Server table. At one time, there are 5000 or more records to
be inserted one by one. It takes some 20-25...
|
by: MaRcElO PeReIrA |
last post by:
Hi guys,
I have a simple table:
teste=# \d forn
Table "public.forn"
Column | Type | Modifiers...
|
by: unkwb |
last post by:
Dealing with the Oracle / DB2 XA (2-phase commit) drivers, I found a
locking situation in DB2 I do not understand. It is actually a pretty
simple scenario to which I can drill it down.
Let say I...
|
by: Ed L. |
last post by:
I think I'm seeing table-level lock contention in the following function
when I have many different concurrent callers, each with mutually distinct
values for $1. Is there a way to reimplement...
|
by: adri4n |
last post by:
as wat ive mentioned in the title.. im would like to know whether the a
particular record/table is being locked in my program. some of the
methods which i would like to develop are as below:
...
|
by: Ian Davies |
last post by:
Hello
Needing help with a suitable solution.
I have extracted records into a table under three columns 'category',
'comment' and share (the category column also holds the index no of the
record...
|
by: sticky |
last post by:
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...
|
by: banging |
last post by:
Hi there,
I have a question regarding locking of tables so that when two or more people try to write or update the mysql tables, it locks up. Basically I only want one person to write to the...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
|
by: ryjfgjl |
last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
| | |