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 |
|
Jangla
Starting Member
2 Posts |
 Posted - 2010-02-16 : 07:20:21
|
| Bit of a head scratcher here (at least for me!).I have a table which has (for this example) 2 columns: ID and NumberID is the incremental primary keyNumber is a non-unique integer numberI have an application that may be running multiple instances. Each instance can query the database to retrieve the max(number) at a given point of time and then add a new record with a value of max(number) + 1 in the number field.The issue I have is that if the application instances query and insert into the database at close to the same time, both instances of the application can be told the same max(number) value and therefore add a row with the same value in the number column.I need to find a way to ensure this doesn't happen...ever! Transactions? Locking? Easiest and most effective method appreciated :) |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 07:33:38
|
"Transactions? Locking? "Neither  You need to establish the ID at the point of INSERTing the new record. No other method will be atomic. (Well, I suppose you could Lock the row, and then let the user do their data entry ... but they could go to lunch leaving the system locked, and that is obviously A Bad Thing)So the normal approach isINSERT INTO MyTable(MyID, Col1, Col2, ...)SELECT (SELECT MAX(MyID)+1 FROM MyTable), Col1, Col2, ... your problem then is how to return that ID to the user (because just querying SELECT MAX(MyID) FROM MyTable is unreliable as someone else may have added that.You can use the IDENTITY column attribute in your table - then the problem goes away:INSERT INTO MyTable(ol1, Col2, ...)SELECT Col1, Col2, ...SELECT scope_identity() as MyNewlyAddedID where scope_identity() provides you with the latest allocated ID in your session, and is thus unaffected by other people adding records at the same time.Any good? If not (e.g. you need to get the number client-site in a way that this doesn't work) tell us why and I expect someone can come up with a solution for you. |
 |
|
|
Jangla
Starting Member
2 Posts |
Posted - 2010-02-16 : 10:12:28
|
| Actually, I think your normal approach may work...perhaps :)Couple of things I maybe should have mentioned in my initial post that may help arrive at the perfect solution; the applications in this case are automated - they run automatically run when an email arrives in a given email account (they are the basis of an automatic email routing system I've written) and therefore locking rows *is* an option provided there's an escape route if one instance of the application crashes and the next instance needs to access the database.At the moment the application gets the max number from the database in one query and then uses the returned value to add a new record to the database by running an sp with that max number. My guess is if I combined these two processes into one properly by using something similar to your SQL snippet to get the max number at the same time as adding the record, it may work....? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 10:35:58
|
There is a alternative way ... You could have a table holding the "Next Available Number" and then have an Sproc that increments it and returns the number - the SProc is atomic, so works OK concurrently.The biggest benefit of this approach is to have the SProc hand out a range of numbers. I'm assuming that your application knows that it needs to insert N email records, so could ask for N IDs (i.e. it gets the lowest ID number, and knows that the next N numbers are available), so if you have a batch-of-emails to process this may work best.The best answer is to have your SP (that adds a new record) to just allow a column in the database, defined with the IDENTITY attribute, to assign the next available number (and, if the application needs it, return it to the App by using SELECT scope_identity )"therefore locking rows *is* an option provided there's an escape route if one instance of the application crashes and the next instance needs to access the database"How long will it take before the database times the APP out after a crash, and will that be reliable?An alternative route is to have some sort of date-time stamp, and for the APP to perform its own "The other guy must have crashed" when it attempts to perform its update - i.e. PC2 checks and finds that a) there is a "PC1 Locked this record" semaphore and b) the "It was locked at 2010-02-16 15:28:52.220" is now more than, say, 30 seconds ago.When the PC1 process finishes, and just before it does a COMMIT, it needs to check that both (a) and (b) still hold the data as it originally set them - otherwise it has, perhaps, just taken more than its allotted 30 seconds and it should perform a ROLLBACK (and log the fact that 30 seconds is no longer long enough to do the job!)CREATE PROCEDURE usp_AllocateNumberRange @intRange int = 1, @intNextID int = NULL OUTPUTASSET NOCOUNT ONUPDATE USET @intNextID = NextID, NextID = NextID + @intRangeFROM dbo.MyNextNumberTable AS URETURN @intNextID (There are ways of putting a constraint on a table like this to only allow 1 row to be added) |
|
|
|
denis_the_thief
Aged Yak Warrior
596 Posts |
Posted - 2010-02-16 : 13:41:56
|
| Using the select max(...) method is even more dangerous when you are using long-running transactions.Consider:User 1 - begins a 30-second transaction that begins with insertUser 2 - does a quick transaction with the select max, which won't see User 1's insert because it hasn't been comitted!User 1 - commits - PK violation!!!Here is a reference to SQL Server's Output clause, just in case you need to get all new Identity values from a Select-Insert with multiple rows:http://www.sqlteam.com/article/using-the-output-clause-to-capture-identity-values-on-multi-row-inserts |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-02-16 : 13:51:16
|
| Good point Thief! |
|
|
|
|
|
|
|
|
Ensuring I don't get the same max value returned