Re: [GENERAL] An example needed for Serializable conflict...
Durumdara wrote: Please send me an example (pseudo-code) for Serializable conflict. And I wanna know, if possible, that if more transactions only read the tables in Serializable mode, and one or others write to it, can I got some conflicts in read operation? You get a serialization conflict if you try to modify a row in a serializable transaction T1 that has been changed by a second transaction T2 after T1 started. Sample 1: T1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; T1: SELECT * FROM t; id | val +-- 1 | test (1 row) T2: DELETE FROM t WHERE id=1; T1: UPDATE t SET val='new' WHERE id=1; ERROR: could not serialize access due to concurrent update Sample 2: T1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; T1: SELECT * FROM t; id | val +-- 1 | test (1 row) T2: UPDATE t SET val=val WHERE id=1; T1: DELETE FROM t; ERROR: could not serialize access due to concurrent update Yours, Laurenz Albe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] An example needed for Serializable conflict...
Hi! Thanks for your help! Another question if I use only SELECTS can I get some Serialization Error? For example: I need a report tool that can show the actual state of the business. Because of I use value-dependent logic, I MUST use consistent state to preserve the equality of many values (sums, counts, etc.). So some (Read Committer) threads are update/delete/insert (sum modify) rows, but this report tool only READ the tables, and only works for temp tables. Can I get some S. error from this transaction? Or can I get some error from modifier threads if this (serializer report) thread actually read the rows that they are want to modify? This is the main question about it. Thanks for your read/answer! dd 2009.07.07. 11:36 keltezéssel, Albe Laurenz írta: Durumdara wrote: Please send me an example (pseudo-code) for Serializable conflict. And I wanna know, if possible, that if more transactions only read the tables in Serializable mode, and one or others write to it, can I got some conflicts in read operation? You get a serialization conflict if you try to modify a row in a serializable transaction T1 that has been changed by a second transaction T2 after T1 started. Sample 1: T1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; T1: SELECT * FROM t; id | val +-- 1 | test (1 row) T2: DELETE FROM t WHERE id=1; T1: UPDATE t SET val='new' WHERE id=1; ERROR: could not serialize access due to concurrent update Sample 2: T1: START TRANSACTION ISOLATION LEVEL SERIALIZABLE; T1: SELECT * FROM t; id | val +-- 1 | test (1 row) T2: UPDATE t SET val=val WHERE id=1; T1: DELETE FROM t; ERROR: could not serialize access due to concurrent update Yours, Laurenz Albe
Re: [GENERAL] An example needed for Serializable conflict...
Hi durumdara, On Tuesday 07 July 2009 12:10:52 durumdara wrote: Another question if I use only SELECTS can I get some Serialization Error? No. For example: I need a report tool that can show the actual state of the business. Because of I use value-dependent logic, I MUST use consistent state to preserve the equality of many values (sums, counts, etc.). So some (Read Committer) threads are update/delete/insert (sum modify) rows, but this report tool only READ the tables, and only works for temp tables. Can I get some S. error from this transaction? Or can I get some error from modifier threads if this (serializer report) thread actually read the rows that they are want to modify? You can get errors between the writers but not between a writer and a reader. It probably would be a good idea to read the documentation about mvcc ( http://www.postgresql.org/docs/current/static/mvcc.html) to understand the possibilities/constraints better. Andres -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] An example needed for Serializable conflict...
Hi! Please send me an example (pseudo-code) for Serializable conflict. And I wanna know, if possible, that if more transactions only read the tables in Serializable mode, and one or others write to it, can I got some conflicts in read operation? c = client t = transaction c1t1 begin serial c1t1 select sum(netto) from order_items where order_code = 1 c2t2 begin c2t2 insert into order_items c3t3 begin serial c3t3 select sum(netto) from order_items where order_code = 2 c2t2 commit c4t4 begin serial c4t4 select sum(netto) from order_items where order_code = 1 c1t1 select count(order_items) c4t4 rollback c1t1 rollback c3t3 rollback I wanna understand, which limitations I need to see in my code to avoid conflicts on important queries where my calculated items must be equal (like count(*) = len(fetched(*)). Sorry for the rtfm like question, but is rather DSFE like (Do and See the F*ed Experience). So your experience needed. Thanks for your help: dd
