The returned values from each query will be different. The second query won't complete until after the first. While that query is running, run the following in the second session: SELECT concurrency_demo_good(1) Next, in the first session run SELECT concurrency_demo_good(1, 10) Execute the following in both sessions to ensure that both have READ COMMITTED isolation levels: SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL READ COMMITTED In concurrency_demo_bad, the new value for invoice_number is established outside of the UPDATE. The function, concurrency_demo_good, uses a single UPDATE so that incrementing number is an atomic operation. Qualifiers are used to protect the function from name conflicts between table columns and variables. Note that these function definitions insulate the code from column type changes by using %TYPE instead of declaring types directly. RETURNS demo_table.number%TYPE LANGUAGE PLPGSQL ASĬREATE OR REPLACE FUNCTION concurrency_demo_bad(id demo_table.id%TYPE, sleep_seconds double precision DEFAULT 0) Run the following to establish a demonstration environment: CREATE TABLE demo_table (ĬREATE OR REPLACE FUNCTION concurrency_demo_good(id demo_table.id%TYPE, sleep_seconds double precision DEFAULT 0) 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 Ĭan be simplified to update res_invoice_number set number = number + 1 where code = 'PO' and client_id=client_code returning number into 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 When run with PostgreSQL's default READ COMMITTED isolation mode, each request will have a different invoice number. Insert into res_purchase (id,client_id,supplier_id,datetime,due_date,invoice,converted,reference,note,list_picture,sub_total,discount_percent,discount_amount,ĭiscount_total,total,vat,vat_total,grand_total,amount_paid,amount_left,active,create_by,create_at,modify_by,modify_at)Ĭoncat(invoice,'/' ,trim(to_char(invoice_number,'000000'))),įrom jsonb_array_elements(json_detail) as dt(data) īecause 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. 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 Invoice := (json_main -> 'invoice') :: text Json_detail := (json_main -> 'list_purchase_detail_model') :: jsonb Ĭlient_code := (json_main -> 'client_id') :: text create or replace function public.rpc_purchase_create(json text) What I want to know is, what if the client requests this function at the same time, does it get the same invoice number? I created a function to insert data into 2 tables and update 1 table.ġ.Get latest number invoice and update table res_invoice_number.Ĥ.I use this function in Supabase function.ĥ.I call this function the same time in flutter the invoice number is good.
0 Comments
Leave a Reply. |
Details
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |