Re: Counterintuitive locking behavior

On Sunday, January 06, 2013 7:48 AM Chris Travers wrote:

> I recently discovered that subselects in update statements don't assume that the select is for update of the updating table.

> For example, if I do this:

> CREATE TABLE foo (
> test int primary key,
> );

> INSERT INTO foo VALUES (1);

> then in one session:

> BEGIN;
> UPDATE foo SET test = 2 WHERE test in (select test from foo where test = 1);

> and then in the other session

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

The behavior will be same even for UPDATE foo SET test = 3 WHERE test =1;

> 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.

With Regards,
Amit Kapila.