« Oracle outer join with multiple fields | Main | Google toolbar spell checker rocks! »
August 11, 2005
Optimistic locking with Oracle 10
New in Oracle 10 is the ORA_ROWSCN pseudo column. This column contains a number which will be automatically incremented upon update.
For example table A
ORA_ROWSCN | ID | DESC
=======================
4300001231 | 10 | Just some desc
4300001231 | 11 | another desc
When you do an update use a simple select statement like this:
update A set DESC = 'New desc' where ID = 10 AND ORA_ROWSCN = 4300001231;
Of course this is assuming that you brought back the ORA_ROWSCN value when you do your select statement. After the update and commit the ORA_ROWSCN value of 4300001231 will be automatically incremented. So how do you know that your update is successful or not? If you are using ADO.NET you should be able to tell how many records are affected and throw an error if it's less than you expected (this is situation it is 1 record), in PL/SQL you check the ROWCOUNT variable like this:
if SQL%ROWCOUNT<=0 then
raise_application_error(-20000, 'Record has been changed by another');
end if;
This way you can do you update safely without locking the row. Oh by default Oracle will not enable rowscn tracking so you have to enable it yourself using the rowdependencies keyword when creating the table, ie:
create table A (...) rowdependencies;
Posted by vhadiant at August 11, 2005 10:57 PM
Trackback Pings
TrackBack URL for this entry:
http://www.hadianto.net/mov32/mt-tb.cgi/90
