Update locks the row even if there is no row to update - Mailing list pgsql-bugs

Hello,

Necessary steps to reproduce the problem are:

1. P-SQL preparing:

create or replace function generate_random_string(

  length int,

  characters text default '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz   '

)

returns text

as $$

declare

  result text := '';

begin

  for __ in 1..length loop

    result := result || substr(characters, floor(random() * length(characters))::int + 1, 1);

  end loop;

  return result;

end; $$

language plpgsql;

create table table_for_update (

  id uuid primary key,

  boolupdate bool not null,

  somedata text

);

insert into table_for_update (id, boolupdate, somedata)

select gen_random_uuid(), floor(2*random())::text::bool, generate_random_string (30)

from generate_series(1,1000);

--The next function gives us an opportunity to update 'boolupdate' and if 'boolupdate' doesn't satisfy the requirement we are waiting some time for another transaction that updates the row to satisfy.

create or replace function "UpdateOrWait" (

  updid uuid,

  retry_count int4,

  retry_timeout int4

)

returns int

as $$

declare

  counter int;

begin

  counter := 0;

  --The first attempt to update

  update table_for_update

  set boolupdate = true

  where id = updid and boolupdate = false;

  while not found loop

    counter := counter + 1;

    if counter > retry_count then

      return 1;

    end if;

    perform pg_sleep(retry_timeout / 1000.0);

    update table_for_update

    set boolupdate = true

    where id = updid and boolupdate = false;

  end loop;

  return 0;

end; $$

language PLPGSQL;

--We need to take one guid for the following steps.

select *

from table_for_update

order by random()

limit 1;

2. The next step we need to do the next code (example from PowerShell):

#Open a connection (a disclaimer: it doesn't matter if we open and close this connection every time in a loop)

$DBConn = New-Object System.Data.Odbc.OdbcConnection

$DBConn.ConnectionTimeout = 0

$DBConn.ConnectionString = "Driver={PostgreSQL Unicode(x64)};Server=localhost;Port=5432;Database=test;Uid=postgres;Pwd=postgres;"

$DBCmd = $DBConn.CreateCommand()

$DBCmd.CommandTimeout = 0

$DBCmd.Connection.Open()

#The variable $i shows us the process

$i=0

while ($true)

{

  $i+=1

  write-host -nonewline "$i;"  #displays the counter

  #The first command call the function

  $command = "select * from `"UpdateOrWait`" ('dfa58a44-a045-47d1-922a-4fddbc11cc06', 5, 1000);" #The ID is from the last P-SQL output

  $DBCmd.CommandText = $command

  $ds = New-Object system.Data.DataSet

  (New-Object system.Data.odbc.odbcDataAdapter($DBCmd)).fill($ds) | out-null

  $result = $ds.Tables[0].UpdateOrWait

  if ($result -ne 0) {write-host -foregroundcolor red "!!!!!!!!!! - $result;" -nonewline} #if it was not successful

  #the second command makes an Update operation

  $command = "update table_for_update set boolupdate = false where id = 'dfa58a44-a045-47d1-922a-4fddbc11cc06'"

  $DBCmd.CommandText = $command

  $DBCmd.ExecuteNonQuery() | out-null

}

$DBCmd.Connection.Close()

3. If we use the second step in one PowerShell process everything is OK. However, if we open another process and run the second step simultaneously, from time to time we will get an unsuccessful output.

I've looked up at this process and have found that

  --The first attempt to update

  update table_for_update

  set boolupdate = true

  where id = updid and boolupdate = false;

this instruction makes a lock even if there is no row to update: