Posted on 2006-06-14
I am using Oracle 10
I having been going round in circles for ages trying to work out a solution, here is the scenario:
I have a table which has a column that indicates the status of the row, i.e. closed or open
I want to be able to recycle the row by finding the id of the first row that is closed and put my new data into that row.
I know how to do this in isolation by on the lines of "Select id from myTable where status = 'closed' and rownum = 1" "or also by using rowid instead as its a constant"
However, I want to ensure that if process a retrieves the id, then that row is locked out until it has been written to and the status is now open in case in the meantime process b gets given the same id and process b overwrites what process a has done. I am thinking of doing this as a "select .... for update", however I am struggling to find out if a row is locked already, the skip locked would return nothing if the first row is locked as it seems to process the skip locked statement *after* finding the first row with 'closed' status.
The only other way I was thinking was to use the data dictionary objects (v$lock, v$session, etc.), but they only from what I gather show the object that is locked and not the row. Sadly this cannot be done using a stored procedure as I could have done it that way. Only other avenue I am thinking of if this helps is to view the transaction id of each row (if this can be done or anyone knows how to do this) and perform a join on one of the data dictionary tables (v$transaction ??) to see if the transaction is still active.
Thanks in advance