We help IT Professionals succeed at work.

MSSQL - Lock Row from reading by other programs

street9009
street9009 asked
on
163 Views
Last Modified: 2017-03-22
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?
Comment
Watch Question

Most Valuable Expert 2011
Author of the Year 2014

Commented:
The database structure cannot be changed (to use an auto-number for example).
That makes the design a non-starter.  The thing you're describing is called a "race condition" because the outcome is unpredictable until the race is run.  We do not design database tables this way because we do not have any way of predicting how they will work under a load.  They cannot be unit-tested with any confidence.

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.
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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.
Vitor Montalv√£oIT Engineer
CERTIFIED EXPERT
Distinguished Expert 2017

Commented:
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', ...)

Open in new window

Senior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
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.

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.
street9009IT Project Manager

Author

Commented:
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.
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
street9009IT Project Manager

Author

Commented:
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!
CERTIFIED EXPERT

Commented:
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.

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions