Using the as-a-service approach for your business model allows you to grow your revenue stream with new practice areas, without forcing you to part ways with existing clients just because they don’t fit the mold of your new service offerings.
MSSQL - Lock Row from reading by other programs
Hello,
I have an issue where I have a table that stores a unique value in it. Many programs read this unique value and then increment it. You can see the problem already - if two programs read at the same time (or even close to the same time), they get the same unique value and the result is a duplicate. The database structure cannot be changed (to use an auto-number for example).
There's another wrinkle here - I need to give priority to one program (web application) over the rest (since the web app can time out and the others can wait). So this solution needs to handle the instance where one application is doing reads/writes and if the web does a read, the other application needs to wait.
How can this be accomplished?
I have an issue where I have a table that stores a unique value in it. Many programs read this unique value and then increment it. You can see the problem already - if two programs read at the same time (or even close to the same time), they get the same unique value and the result is a duplicate. The database structure cannot be changed (to use an auto-number for example).
There's another wrinkle here - I need to give priority to one program (web application) over the rest (since the web app can time out and the others can wait). So this solution needs to handle the instance where one application is doing reads/writes and if the web does a read, the other application needs to wait.
How can this be accomplished?
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
I wear a lot of hats...
"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.
The only way I can think to do what you are asking is to write an EXE that runs on the server that all of the other programs use to access the database. It has to arbitrate between the accesses to do what you want. You're not going to be able to get PHP or the MS SQL server to do that. Unless you want to entirely rewrite one of them along with the drivers for them.
Keep all logic inside a transaction. This will lock the table until new value is stored. Example:
BEGIN TRAN
-- Get the next value
SELECT @value = myValue+1
FROM MyTable
-- Update the value
UPDATE MyTable
SET myValue = @value
COMMIT
-- Use the new value as you need
INSERT INTO AnotherTable (myValueField, anotherField, ...)
VALUES (@value, 'bla bla bla', ...)
The read and increment should happen in the same statement. This nearly guarantees concurrency is not a problem. You can use an OUTPUT clause to get the old or new value without having to do a separate SELECT.
Something like this:
DECLARE @tn_counters TABLE (
counter_column_old int NOT NULL,
counter_column_new int NOT NULL
);
UPDATE tn
SET counter_column = counter_column + 1
FROM dbo.table_name
OUTPUT DELETED.tn, INSERTED.tn INTO @tn_counters
Something like this:
DECLARE @tn_counters TABLE (
counter_column_old int NOT NULL,
counter_column_new int NOT NULL
);
UPDATE tn
SET counter_column = counter_column + 1
FROM dbo.table_name
OUTPUT DELETED.tn, INSERTED.tn INTO @tn_counters
Experts Exchange Solution brought to you by
Your issues matter to us.
Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.
Start your 7-day free trialSo this solution needs to handle the instance where one application is doing reads/writes and if the web does a read, the other application needs to wait.
that would require to abort the other transaction entirely
i'm pretty sure we'd be more helpful in finding a way to achieve your goal without that manual increment at all. what is the id used for ?
it could seem reasonable to increment the value first, and then let the programs do what they need so there is no race conditions at all and little contention so you don't need to bother with priorities...
is incrementing AFTER doing the work actually necessary ?
would that be a problem if the ids have gaps ? or are not incremental at all ?
if you can use arbitrary values or allow gaps, each app can have it's own reserved set of ids which can help letting them run at the same time.
also sql server has record-based versioning and can allow parallel execution in such cases. depending on your requirements in terms of consistency, that feature might be usable as well but i'd rather look into other possibilities first.
i'm pretty sure we'd be more helpful in finding a way to achieve your goal without that manual increment at all. what is the id used for ?
It's a transaction number. Yes the system design is TERRIBLE in this case but I can't change it. The way the system itself works is it looks to that field and gets a value and then puts the next # back.
it could seem reasonable to increment the value first, and then let the programs do what they need so there is no race conditions at all and little contention so you don't need to bother with priorities...
is incrementing AFTER doing the work actually necessary ?
No, I don't suppose that it is necessary to do it afterwards, but I'm not sure what effect it would have. Literally all the "work" that's done is read, increment, write (then use what was read). It amazes me that there is duplication but there is.
The read and increment should happen in the same statement.
This makes a lot of sense to me. I'm going to try this first.
It amazes me that there is duplication but there is.
this is actually very likely in such cases under even mild load.
contention from this tool or other tools working on the same table will produce a queue of waiting queries.
in order to get better performance, the server will move regular selects after whatever transactional statement would lock the same rows or the top of the queue as they don't block one another or updates.
the consequence is that a long select that makes a write wait will produce a time frame during which multiple selects will queue and later be executed in parallel or sequentially as soon as the lock is released.
--------
using a single statement/transaction will prevent duplicates.
@Scotts piece of code seems simple and working if it is usable in your case.
you can also use multiple statements in an isolated transaction or possibly change the select to a "select for update" which will lock the row without producing versioning or non acid transactions ( depending on you transaction isolation level ) and without toying with rollbacks or stored procedures.
if there is no time lag between the moment you read the id and increment, the above should be sufficient. if there is ( and i assume there is give your comment regarding reads and priorities ) the simplest solution by far is to increment and terminate the transaction as soon as possible which allows each app to reeserve an id whenever they need and work on that id as long as they need to without blocking other apps.
if each app needs to make sure all other apps finished their jobs before incrementing, there is no way you can hack them into running in parallel unless you/we dig into the apps logic and change it.
It will be a while before I know if this has the same flaw, but I don't expect it will. I liked Scott's solution best so gave him the bulk of the points (with an assist from skull). It was very straight-forward and simple. Thanks everyone!
good to see you got something. you can test your current setup by locking the table or row manually for a few minutes ( or more depending on how often your multiple apps are scheduled ) which will force the contention issue to appear.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server
From novice to tech pro — start learning today.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get this solution by purchasing an Individual license!
Start your 7-day free trial.
This might give some good ideas.
https://technet.microsoft.com/en-us/library/jj856598(v=sql.110).aspx
With databases (in general) the terms to search for include "lock" and "transaction." Those, together with "MSSQL" might be helpful for you as you look for alternate ideas.