Can transaction in Postgres handle concurrency data?

Question

I am new to PostgreSQL. I created a function to insert data into 2 tables and update 1 table.

1.Get latest number invoice and update table res_invoice_number. 2.Insert into table res_purchase. 3.Insert into table res_purchase_detail. 4.I use this function in Supabase function. 5.I call this function the same time in flutter the invoice number is good.

What I want to know is, what if the client requests this function at the same time, does it get the same invoice number?

Thanks.

create or replace function public.rpc_purchase_create(json text)
    returns int
    language 'plpgsql'
    security definer
as $body$
declare json_main jsonb;
declare json_detail jsonb;
declare invoice_number integer;
declare main_id text;
declare client_code text;
declare invoice text;
begin
  --assign json
  json_main := json::jsonb;
  json_detail := (json_main -> 'list_purchase_detail_model') :: jsonb;
  --assign sale id
  main_id := (json_main ->> 'id') :: text;
  client_code := (json_main ->> 'client_id') :: text;
  invoice := (json_main ->> 'invoice') :: text;
  invoice := split_part(invoice,'/',1);
  --increase invoice number
  with x as (update res_invoice_number set number = number + 1 where code = 'PO' and client_id=client_code returning number) select x.number into invoice_number from x;
  --insert into main
  insert into res_purchase (id,client_id,supplier_id,datetime,due_date,invoice,converted,reference,note,list_picture,sub_total,discount_percent,discount_amount,
                            discount_total,total,vat,vat_total,grand_total,amount_paid,amount_left,active,create_by,create_at,modify_by,modify_at) 
  values (
    main_id,
    client_code,
    json_main->>'supplier_id',
    (json_main->>'datetime')::bigint,
    (json_main->>'due_date')::bigint,
    concat(invoice,'/' ,trim(to_char(invoice_number,'000000'))), 
    '',
    json_main->>'reference',
    json_main->>'note',
    (json_main->>'list_picture')::jsonb,
    (json_main->>'sub_total')::float4,
    (json_main->>'discount_percent')::int2,
    (json_main->>'discount_amount')::float4,
    (json_main->>'discount_total')::float4,
    (json_main->>'total')::float4,
    (json_main->>'vat')::float4,
    (json_main->>'vat_total')::float4,
    (json_main->>'grand_total')::float4,
    (json_main->>'amount_paid')::float4,
    (json_main->>'amount_left')::float4,
    (json_main->>'active')::bool,
    json_main->>'create_by',
    (json_main->>'create_at')::bigint,
    null,null
  );
  --insert into detail
   insert into res_purchase_detail (
        purchase_id,
        product_id,
        product_name,
        description,
        qty,
        cost,
        discount,
        amount
    ) 
    select main_id,
           dt.data ->> 'product_id',
           dt.data ->> 'product_name',
           dt.data ->> 'description',
           (dt.data ->> 'qty')::float4,
           (dt.data ->> 'cost')::float4,
           (dt.data ->> 'discount')::int4,
           (dt.data ->> 'amount')::float4
    from jsonb_array_elements(json_detail) as dt(data);
  return invoice_number;
end;  

$body$;

Answer 1

Because the function increments number in a single UPDATE instead of using a separate SELECT to get the current value followed by an UPDATE, concurrent requests will not have the same invoice numbers regardless of the transaction isolation level. When run with PostgreSQL's default READ COMMITTED isolation mode, each request will have a different invoice number. When run with the stricter REPEATABLE READ or SERIALIZABLE isolation modes, concurrent requests will either succeed and have different invoice numbers or raise a exception; for example:

ERROR:  could not serialize access due to concurrent update

In the post's function, the statement that sets the invoice number

with x as (update res_invoice_number set number = number + 1 where code = 'PO' and client_id=client_code returning number) select x.number into invoice_number from x;

can be simplified to

update res_invoice_number set number = number + 1 where code = 'PO' and client_id=client_code returning number into invoice_number;

Run the following to establish a demonstration environment:

CREATE TABLE demo_table (
  id integer PRIMARY KEY,
  number integer
);

INSERT INTO demo_table (id, number)
VALUES (1, 0);

CREATE OR REPLACE FUNCTION concurrency_demo_good(id demo_table.id%TYPE, sleep_seconds double precision DEFAULT 0)
  RETURNS demo_table.number%TYPE LANGUAGE PLPGSQL AS
$func$
  DECLARE
    invoice_number demo_table.number%TYPE;
  BEGIN
    UPDATE demo_table t
      SET number = t.number + 1
      WHERE t.id = concurrency_demo_good.id
    RETURNING t.number
    INTO invoice_number;

    PERFORM pg_sleep(sleep_seconds);
    RETURN invoice_number;
  END
$func$;

CREATE OR REPLACE FUNCTION concurrency_demo_bad(id demo_table.id%TYPE, sleep_seconds double precision DEFAULT 0)
  RETURNS demo_table.number%TYPE LANGUAGE PLPGSQL AS
$func$
  <<local>>
  DECLARE
    invoice_number demo_table.number%TYPE;
  BEGIN
    SELECT t.number + 1
      INTO invoice_number
      FROM demo_table t
      WHERE t.id = concurrency_demo_bad.id;

    UPDATE demo_table t
      SET number = local.invoice_number
      WHERE t.id = concurrency_demo_bad.id;

    PERFORM pg_sleep(sleep_seconds);
    RETURN invoice_number;
  END
$func$;

Note that these function definitions insulate the code from column type changes by using %TYPE instead of declaring types directly. Qualifiers are used to protect the function from name conflicts between table columns and variables.

The function, concurrency_demo_good, uses a single UPDATE so that incrementing number is an atomic operation. In concurrency_demo_bad, the new value for invoice_number is established outside of the UPDATE.

Open two sessions. Execute the following in both sessions to ensure that both have READ COMMITTED isolation levels:

SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED;

Next, in the first session run

SELECT concurrency_demo_good(1, 10);

While that query is running, run the following in the second session:

SELECT concurrency_demo_good(1);

The second query won't complete until after the first. The returned values from each query will be different.

Repeat the same query in the first session but this time run the following in the second session before the first session's query completes:

SELECT concurrency_demo_bad(1);

Again, the second query won't complete until after the first. This time both queries will return the same value.

Repeating these steps with the other isolation levels (REPEATABLE READ and SERIALIZABLE) results in the first session returning with an invoice number and the second session reporting an exception.