Re: Counterintuitive locking behavior

On Sunday, January 06, 2013 11:10 AM Amit kapila wrote:
On Sunday, January 06, 2013 7:48 AM Chris Travers wrote:

>> Is there a reason why we don't do locking this way? (i.e. where on UPDATE foo, all rows selected from foo during the
>> update are locked unless the subselect specifically states otherwise.)

>The reason for this behavior is if it locks all rows during select, then it can so happen that Update will actually not happen on the row but it will be locked.
> For example

> UPDATE foo SET test = 3 WHERE test in (select test from foo where test = 1) and FALSE;

> Now in this case if it locks the rows during subselect, then the rows will be locked during whole transaction
> irrespective of the fact that they will not be updated.

In the above example and FALSE, I mean to say any other subquery which will yield the overall condition to not return any row.
Similarly there can be many more scenarios where only half of the selected rows (by one of the conds. ) will be actual candidates of Update.

With Regards,
Amit Kapila.