Q
Problem solve Get help with specific problems with your technologies, process and projects.

Is there a SQL-specific way to control record locking?

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.

Thanks!
Richard


Hi Richard

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

Or --

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


This was last published in May 2001

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close