Update locks the row even if there is no row to update

From: Roman <fallen(at)fastmail(dot)com>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Subject: Update locks the row even if there is no row to update
Date: 2021-04-09 06:52:10
Message-ID: 33b04d82-830e-484f-bcb3-4c593612754d@www.fastmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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:
pid
blockpid
query
mode
wait_event_type
wait_event
locktype
state
granted
2,732
{}
select * from "UpdateOrWait" ('dfa58a44-a045-47d1-922a-4fddbc11cc06', 5, 1000);
RowExclusiveLock
Timeout
PgSleep
relation
active
true
20,132
{2732}
update table_for_update set boolupdate = false where id = 'dfa58a44-a045-47d1-922a-4fddbc11cc06'
ShareLock
Lock
transactionid
transactionid
active
false

--
Best Regards,
Roman

Browse pgsql-bugs by date

  From Date Subject
Next Message Julien Rouhaud 2021-04-09 10:42:46 Re: BUG #16953: OOB access while converting "interval" to char
Previous Message Robin Knipe 2021-04-08 17:14:12 Re: BUG #16956: psql won't load command history