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  13531 
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.co m
"sticky" <st********@gma il.comwrote in message
news:11******** **************@ r29g2000hsg.goo glegroups.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********@gma il.comwrote in message
news:11******** **************@ r29g2000hsg.goo glegroups.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...@gma il.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********@gma il.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 process has been, from the site (in ASP), to:
- select the top record from the members table where the assigned flag
|
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 gives me the two latest values. I want to test the rate of
change of these values. If the top row is a 50% increase over the row
below it, I'll execute some special logic.
|
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 mins for the entire process
to run.
Another application accesses the same table.
As long as the insert process within the transaction isn't completed,
|
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 have a table with one column. This table contains 2 rows:
select * from my_tab
test1
test2
Now I insert a value (with a 1st CLP): insert into my_tab values('x1')
|
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 this function using
select-for-update (or equivalent) in order to get a row-level lock (and
thus less contention) while maintaining the function interface? The docs
seem to suggest so, but it's not clear how to return the SETOF queued_item
and also...
| | |
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:
Lock(KEY, SQLCODE)
- to create a lock. Returns 0 on success, 1 if a lock already exists
and -1 on failure with SQLCODE. There is no necessity to use IsLock()
for checking if a lock exists or not, just insert the record and check
for
|
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 in a hidden field)
I wish the user to be able to edit the data in the table, so I have
extracted the records into hiddenfield, textareas, dropdown list and
checkbox so that they can make changes.
I named these elements as arrays and wish to run an...
|
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 stored procedures to make multiple selects and then an
insert.
What is the best way of doing this?
Description of the system:
|
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 file, but many are able to read the files (or tables entities).
I am not sure if I need to lock the tables in my Java code or do I lock the tables within the MySQL syntax. I'm just a little confused on the matter.
This java code is a working...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| | |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| | |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| | |
