update mytable set node_index=0 where id in (
SELECT
id
FROM mytable
WHERE
rownum<=10 and PROCS_DT is null
order by CRET_DT,PRTY desc)
I got this query as part of a previous answer to my question. Now how to lock the rows in the select query to make sure no other thread over writes my update.
It's not clear to me that you have the problem you seem to think you have.
Updating a row in the table inherently places a row-level lock on that row. No other session can update that row until you release the lock by ending your transaction by committing or rolling back. Once you complete your transaction, other sessions are free to overwrite your update. There is no need or benefit to locking the rows in the SELECT.
If you are trying to write your application to avoid lost updates (i.e. you want to avoid another session updating the same rows that you did after you committed your transaction without showing the other user your updated data first), your application would need to implement some sort of additional locking. The most efficient would be to implement optimistic locking using some sort of LAST_UPDATE_TIMESTAMP column in the table. Assuming you added this column to the table if it doesn't already have one, you would select the LAST_UPDATE_TIMESTAMP whenever you queried data to present to the user and you would specify that LAST_UPDATE_TIMESTAMP in your UPDATE. If your UPDATE updated exactly 1 row, you know that no one had changed the data since you queried it. If your UPDATE updated 0 rows, you would know that the data had changed since you had queried it and your application would need to take appropriate action (i.e. re-querying the data, re-presenting it to the user, asking the user whether to keep any of the uncommitted changes they had made, etc.).
Your query does have another problem, however. The SELECT statement is almost certainly not doing what you think (or intend) for it to do. This query gets an arbitrary 10 rows from MYTABLE where PROCS_DT is NULL and then orders those arbitrary 10 rows.
SELECT
id
FROM mytable
WHERE
rownum<=10 and PROCS_DT is null
order by CRET_DT,PRTY desc
Assuming that you actually want to get the "top 10" results, you would need to do the ORDER BY in a subquery before applying the ROWNUM predicate, i.e.
SELECT
id
FROM (SELECT *
FROM mytable
WHERE procs_dt IS NULL
ORDER BY cret_dt, prty desc)
WHERE
rownum<=10
Or you could use an analytic function, i.e.
SELECT
id
FROM (SELECT m.*,
rank() over (ORDER BY cret_dt, prty desc) rnk
FROM mytable m
WHERE procs_dt IS NULL)
WHERE
rnk<=10
LAST_UPDATED_TIMESTAMP rather than ORA_ROWSCN for the optimistic locking section.To lock rows in Oracle, you can do:
SELECT *
FROM table1
WHERE some_condition
FOR UPDATE OF table1;
It's much better to just lock the rows you need instead of the whole table.
Refer here: http://www.techonthenet.com/oracle/cursors/for_update.php
But in general, if you are doing the UPDATE with a single statement, you should not need to worry about locking the table or even the rows. It's when you have to use multiple statements that you need to lock rows.
A scenario where you would need to use this feature would be in a reservation system. Consider this example:
1. Execute SELECT to find out if room XYZ is available for a reservation on date X.
2. The room is available. Execute UPDATE query to book the room.
Do you see the potential problem here? If between steps 1 and 2 the room gets booked by another transaction, then when we reach step 2 we are operating on an assumption which is no longer valid, namely, that the room is available.
However, if in step 1 we use the SELECT FOR UPDATE statement instead, we ensure that no other transaction can lock that row, so when we go to UPDATE the row, we know it's safe to do so.
You can try using SKIP LOCKED inside the nested query.
For detail please check this blog:
https://www.2ndquadrant.com/en/blog/what-is-select-skip-locked-for-in-postgresql-9-5/
Not the exact one, but you can take idea from this one:
update mytable set node_index=0 where id in (
SELECT
id
FROM mytable
WHERE
rownum<=10 and PROCS_DT is null
order by CRET_DT,PRTY desc
FOR UPDATE SKIP LOCKED)
RETURNING *;
