select for update with ruby oci8

Question

how do I do a 'select for update' and then 'update' the row using ruby oci8.

I have two fields counter1 and counter2 in a table which has only 1 record. I want to select the values from this table and then increment them by locking the row using select for update.

thanks.

Answer 1

You need to make sure that autocommit is set to false on your connection. This is the key. Then you would do the following steps:

1. Do your select with the for update clause on the end (select column1, column2 from mytable for update). This will lock the row.

2. Perform your Update query.

3. Issue an explicit commit which would release the lock on the row.

Of course remember that locking the row just locks it from modification. Another session could still query those rows. For example if this was an ID and the way to fetch a new ID was to query the table doing a select max(id) + 1 from table. Locking the row would not prevent another session from doing this select.

Better yet would be to skip the select and update the records in place and use the returning clause to return to you the new updated values. I have never done it in Ruby OCI8, so I'm not sure if it supports that feature. The docs for that clause in an update are here:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10007.htm#i2126358

Answer 2

select_stmt = conn.prepare('select * from table_name for update')
select_stmt.exec
while row = select_stmt.fetch
  conn.exec('update table_name set col = :1 where rowid = :2', 'val', select_stmt.rowid)
end