Is there an SQL-specific (i.e., vendor independent) way to control record locking? I've created a script that updates a numeric field (in a single row) but before updating it, the script reads the value of the field (a SELECT statement) then updates it (with an UPDATE statement). The connection to my database is via ODBC, so I'm looking for a way to "lock" the result of the SELECT statement so that my update is using the latest data.
Yes, there is a standard way of specifying a transaction block, and yours would look something like this --
BEGIN TRANSACTION lockme SELECT thevalue FROM thetable WHERE thekey = foo IF ERROR THEN ROLLBACK TRANSACTION lockme UPDATE thetable SET thevalue = bar WHERE thekey = foo IF ERROR THEN ROLLBACK TRANSACTION lockme COMMIT TRANSACTION lockme
That said, you should nevertheless look up the specific syntax for the database you are using -- each database has its own extensions and options, and you simply should not rely on the standard syntax to work best, or at all, in any database. In addition, and I must claim ignorance here, the ODBC driver might not let you pass an entire transaction block, so you'd have to check into that too.
That said, are you by any chance setting the value to highest current number in the table plus one? This is a common requirement where the database doesn't support identity or autonumber fields. If so, you would be right to use a transaction block, because of multithreaded updates interfering with one another. The only way around this is to come up with a strategy where you don't have to do the select first!
Is the update dependent on the value, or is it the same type of update regardless of what the selected value is? If the latter, consider just doing the update without first selecting the value --
UPDATE thetable SET thevalue = thevalue + 14.98 WHERE thekey = foo
UPDATE thetable SET thevalue = thevalue * 1.07 WHERE thekey = foo
As long as you can write an expression for the new value based on the current value, you wouldn't actually have to select it first (and thus you would avoid the need for the transaction block). But maybe you've got something more sophisticated going on...
For More Information
- What do you think about this answer? E-mail us at editor@searchDatabase.com with your feedback.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
- Ask your technical SQL questions--or help out your peers by answering them--in our live discussion forums.
- Ask the Experts yourself: Our SQL guru is waiting to answer your technical questions.
Dig Deeper on Oracle and SQL
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.