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.
| Author |
Topic |
|
mabian
Starting Member
2 Posts |
 Posted - 2004-08-31 : 04:07:39
|
| Hello, I have a rather short but tricky question: I have a table (SQL Server 2000 DB) in which records cannot be INSERTED if another record already exists with the same field values for a given subset of fields (let's say field ID and DATE, they are not primary keys by now).The application must first check if the record already exists and only if it doesn't must insert the new record. If the record already exists the application must "simply" notify the user gracefully.What's the best method to achieve this goal in "concurrency safe" (that is, atomic) fashion?Thanx and bye. |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-08-31 : 04:16:47
|
| Use a transaction.-------Moo. :) |
 |
|
|
eyechart
Master Smack Fu Yak Hacker
3575 Posts |
Posted - 2004-08-31 : 04:27:08
|
| It should be fairly straightforward. I would try the following1. put an index on over both columns (unique clustered if possible)2. check for the existance of the record in question. 3. if they don't exist, perform INSERT else return a -1 return code (or some other non zero return value) which your calling app interprets and gently notifies the user.You might also have to play with some hints to lock the table if you have many concurrent operations occuring. This will slow down your application of course.Also, make sure that you create a sproc to perform all those steps as this will be the absolute fastest (and safest) way to do this work.Some questions though: 1. How many rows in this table?2. How is it currently indexed?3. Does the table get a lot of inserts/updates? Or mainly queries?I am confused by your term 'concurrency safe', do you mean making this table able to handle many concurrent updates? -ec |
|
|
|
mabian
Starting Member
2 Posts |
Posted - 2004-09-01 : 01:47:32
|
| Hello again, I agree in using stored proc, but what I'd like to understand well is how to handle in the best way the situation in which two or more users try to insert the same record at about the same moment. I mean, if the record already exists there's no problem because all users will be said that they cannot do the thing because the record is already there. BUT what if the record does not exist?For the first user coming in time the SELECT does not find the record, so it can be inserted, but, if this thing is being made in a transaction, the SELECT for the second user would not find the record too and try to insert it as well; I need a way to block access - read and write - to the whole table while one user is trying to insert a new record; only when the check, and if needed the insert, is completed the table will be ready and available to all other users. How to do that?Thanx |
|
|
|
|
|
|
|
|
Avoid concurrent insert