Обсуждение: READ COMMITTED isolevel is implemented ...
and this is now the DEFAULT isolevel. I run some tests to ensure how it works, but not so much. Unfortunately, currently it's not possible to add such tests to regression suit because of they require concurrent transactions. We could write simple script to run a few psql-s simultaneously and than just put queries to them (through pipes) in required order. I have no time for this now... Processing updates in READ COMMITTED isolevel is much complex than in SERIALIZABLE one, because of if transaction T1 notices that tuple to be updated/deleted/selected_for_update is changed by concurrent transaction T2 then T1 has to check does new version of tuple satisfy T1 plan qual or not. For simple cases like UPDATE t ... WHERE x = 0 or x = 1 it would be possible to just ExecQual for new tuple, but for joins & subqueries it's required to re-execute entire plan having this tuple stuck in Index/Seq Scan over result relation (i.e. - scan over result relation will return only this new tuple, but all other scans will work as usual). To archieve this, copy of plan is created and executed. If tuple is returned by this child plan then T1 tries to update new version of tuple and if it's already updated (in the time of child plan execution) by transaction T3 then T1 will re-execute child plan for T3' version of tuple, etc. Handling of SELECT FOR UPDATE OF > 1 relations is ever more complex. While processing tuples (more than 1 tuple may be returned by join) from child plan P1 created for tuple of table A and trying to mark a tuple of table B, updated by T3, T1 will have to suspend P1 execution and create new child plan P2 with two tuples stuck in scans of A & B. Execution of P1 will be continued after execution of P2 (P3, P4 ... -:)). Fortunately, max # of possible child plans is equal to the number of relations in FOR UPDATE clause: if while processing first tuple from Pn T1 sees that tuple stuck in Pm, m < n, was changed, then T1 stops execution of Pn, ..., Pm-1 and re-start Pm execution for new version of tuple. Note that n - m may be more than 1 because of tuples are always marked in the order specified in FOR UPDATE clause and only after transaction ensured that new tuple version satisfies plan qual. Trigger manager is also able to use child plans for before row update/delete triggers (tuple must be marked for update - i.e. locked - before trigger execution), but this is not tested at all, yet. Executor never frees child plans explicitely but re-uses them if needed and there are unused ones. Well, MVCC todo list: -- big items 1. vacuum 2. btree 2.1 still use page locking 2.2 ROOT page may be changed by concurrent insertion but btinsert doesn't checkthis -- small ones 3. refint - selects don't block concurrent transactions: FOR UPDATE must be used in some cases 4. user_lock contrib code: lmgr structures changed Vadim
Hello All, > -----Original Message----- > From: owner-pgsql-hackers@postgreSQL.org > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Vadim Mikheev > Sent: Saturday, January 30, 1999 2:55 AM > To: hackers@postgreSQL.org > Subject: [HACKERS] READ COMMITTED isolevel is implemented ... > > > and this is now the DEFAULT isolevel. > It's different from current(v6.4.2). The way will be provided to upgrade user's current code ? > I run some tests to ensure how it works, but not so much. > Unfortunately, currently it's not possible to add > such tests to regression suit because of they require > concurrent transactions. We could write simple script to > run a few psql-s simultaneously and than just put queries > to them (through pipes) in required order. I have no time > for this now... > > Processing updates in READ COMMITTED isolevel is much > complex than in SERIALIZABLE one, because of if transaction T1 > notices that tuple to be updated/deleted/selected_for_update > is changed by concurrent transaction T2 then T1 has to check > does new version of tuple satisfy T1 plan qual or not. How about UPDATE t set x = x + 1 where .... ? The values of x used for x = x + 1 are at the time when statement started ? It seems that this case also requires re-execution. Thanks. Hiroshi Inoue Inoue@tpf.co.jp
Hiroshi Inoue wrote: > > > Subject: [HACKERS] READ COMMITTED isolevel is implemented ... > > > > and this is now the DEFAULT isolevel. > > > > It's different from current(v6.4.2). First, I think that DEFAULT isolevel must be configure-able. > The way will be provided to upgrade user's current code ? Even SERIALIZABLE isolevel in MVCC is different from one in locking systems. There is only one way to don't change anything in applications - use table level locking. Should we provide ability to turn MVCC off? > > Processing updates in READ COMMITTED isolevel is much > > complex than in SERIALIZABLE one, because of if transaction T1 > > notices that tuple to be updated/deleted/selected_for_update > > is changed by concurrent transaction T2 then T1 has to check > > does new version of tuple satisfy T1 plan qual or not. > > How about UPDATE t set x = x + 1 where .... ? > > The values of x used for x = x + 1 are at the time when statement > started ? > It seems that this case also requires re-execution. x + 1 is in target list of execution plan. And so when child plan is executed, new value of x is used to evaluate target list expressions. Executor uses tuple from child plan as new version of tuple. Vadim
> -----Original Message----- > From: owner-pgsql-hackers@postgreSQL.org > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Vadim Mikheev > Sent: Saturday, January 30, 1999 1:41 PM > To: Hiroshi Inoue > Cc: hackers@postgreSQL.org > Subject: Re: [HACKERS] READ COMMITTED isolevel is implemented ... > > > Hiroshi Inoue wrote: > > > > > Subject: [HACKERS] READ COMMITTED isolevel is implemented ... > > > > > > and this is now the DEFAULT isolevel. > > > > > > > It's different from current(v6.4.2). > > First, I think that DEFAULT isolevel must be configure-able. > > > The way will be provided to upgrade user's current code ? > > Even SERIALIZABLE isolevel in MVCC is different from > one in locking systems. There is only one way to don't > change anything in applications - use table level locking. > Should we provide ability to turn MVCC off? > I think in most cases SEIALIZABLE is sufficient for upgrading. So it is preferable that we can change default isolation level easily. I believe that SET TRANSCTION ISOLATION LEVEL is per transaction command(i.e it is necessary for every transaction which is different from default). Another command to set per connection default is necessary as Thomas Lockhart wrote about "autocommit". We can have the default be "set autocommit on" (probably with an equalssign like our other "set" variables) and we can have it be a run-time option like DATESTYLE and other settable parameters. So you can configure your server or your client environment to always behave the way you prefer. > > > Processing updates in READ COMMITTED isolevel is much > > > complex than in SERIALIZABLE one, because of if transaction T1 > > > notices that tuple to be updated/deleted/selected_for_update > > > is changed by concurrent transaction T2 then T1 has to check > > > does new version of tuple satisfy T1 plan qual or not. > > > > How about UPDATE t set x = x + 1 where .... ? > > > > The values of x used for x = x + 1 are at the time when statement > > started ? > > It seems that this case also requires re-execution. > > x + 1 is in target list of execution plan. And so when child plan > is executed, new value of x is used to evaluate target list > expressions. Executor uses tuple from child plan as new version > of tuple. > Oracle(Version7) seems to work as you mentioned. Sorry. Hiroshi Inoue Inoue@tpf.co.jp
> Handling of SELECT FOR UPDATE OF > 1 relations is ever more > complex. While processing tuples (more than 1 tuple may be > returned by join) from child plan P1 created for tuple of table I don't think Informix allows FOR UPDATE in a multi-table select. -- Bruce Momjian | http://www.op.net/~candle maillist@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
Hiroshi Inoue wrote: > > > > > > > > Subject: [HACKERS] READ COMMITTED isolevel is implemented ... > > > > > > > > and this is now the DEFAULT isolevel. > > > > > > > > > > It's different from current(v6.4.2). > > > > First, I think that DEFAULT isolevel must be configure-able. > > > > > The way will be provided to upgrade user's current code ? > > > > Even SERIALIZABLE isolevel in MVCC is different from > > one in locking systems. There is only one way to don't > > change anything in applications - use table level locking. > > Should we provide ability to turn MVCC off? > > > > I think in most cases SEIALIZABLE is sufficient for upgrading. > So it is preferable that we can change default isolation level > easily. Agreed, but I never worked with configure stuff... > I believe that SET TRANSCTION ISOLATION LEVEL is per > transaction command(i.e it is necessary for every transaction > which is different from default). > Another command to set per connection default is necessary > as Thomas Lockhart wrote about "autocommit". Oracle uses ALTER SESSION command for this. > > > > Processing updates in READ COMMITTED isolevel is much > > > > complex than in SERIALIZABLE one, because of if transaction T1 > > > > notices that tuple to be updated/deleted/selected_for_update > > > > is changed by concurrent transaction T2 then T1 has to check > > > > does new version of tuple satisfy T1 plan qual or not. > > > > > > How about UPDATE t set x = x + 1 where .... ? > > > > > > The values of x used for x = x + 1 are at the time when statement > > > started ? > > > It seems that this case also requires re-execution. > > > > x + 1 is in target list of execution plan. And so when child plan > > is executed, new value of x is used to evaluate target list > > expressions. Executor uses tuple from child plan as new version > > of tuple. > > > > Oracle(Version7) seems to work as you mentioned. > Sorry. Isn't this the same you told in first message? And if so - what "sorry" means? -:) Ok. T1 executes UPDATE t SET x = x + 1 WHERE y = 2 and sees that row (x = 1, y = 2) is updated by T2 to be (x = 3, y = 2). What is the result of T1 update? In postgres the result will be (x = 4, y = 2), not (x = 2, y = 2). Is it ok? Vadim
Bruce Momjian wrote: > > > Handling of SELECT FOR UPDATE OF > 1 relations is ever more > > complex. While processing tuples (more than 1 tuple may be > > returned by join) from child plan P1 created for tuple of table > > I don't think Informix allows FOR UPDATE in a multi-table select. Oracle does. I don't know about SyBase, DB2 etc. In any case - this is implemented already -:) Vadim
Vadim Mikheev wrote: > > Bruce Momjian wrote: > > > > > Handling of SELECT FOR UPDATE OF > 1 relations is ever more > > > complex. While processing tuples (more than 1 tuple may be > > > returned by join) from child plan P1 created for tuple of table > > > > I don't think Informix allows FOR UPDATE in a multi-table select. > > Oracle does. I don't know about SyBase, DB2 etc. > In any case - this is implemented already -:) > When MS Access came out they made a big fuss about this ability, claiming that they were the first ones to implement this. I'm not sure in what category they claimed they were first ;) ------------------- Hannu
> and this is now the DEFAULT isolevel. But it seems that the standard says SERIALIZABLE is the default isolation level (or at least the highest isolation level implemented in the product), doesn't it? I have looked into Japanese transalated version of: "A guide to the SQL standard 4th edition" by C.J.Date "Understanding the new SQL: A complete guide" by J.Melton and A.R.Simon Anyone can confirm this? -- Tatsuo Ishii
Tatsuo Ishii wrote: > > > and this is now the DEFAULT isolevel. > > But it seems that the standard says SERIALIZABLE is the default > isolation level (or at least the highest isolation level implemented > in the product), doesn't it? Yes, it does. But Oracle, Informix, Sybase all use READ COMMITTED as default. Please decide youself - it doesn't matter much to me -:) I would like to see it 1. configure-able; 2. in pg_options; 3. in command line args. I'll do this after beta started, if no one else before. Vadim
>Tatsuo Ishii wrote: >> >> > and this is now the DEFAULT isolevel. >> >> But it seems that the standard says SERIALIZABLE is the default >> isolation level (or at least the highest isolation level implemented >> in the product), doesn't it? > >Yes, it does. Then we should go for the standard way, I think. >But Oracle, Informix, Sybase all use READ COMMITTED as default. >Please decide youself - it doesn't matter much to me -:) >I would like to see it 1. configure-able; 2. in pg_options; >3. in command line args. I'll do this after beta started, >if no one else before. BTW, what is the advantage of READ COMMMITTED in PostgreSQL? I thought the SERIALIZABLE should give us enough concurrency since we are using MVCC. Could you give me some examples? -- Tatsuo Ishii
Tatsuo Ishii wrote: > > BTW, what is the advantage of READ COMMMITTED in PostgreSQL? I thought > the SERIALIZABLE should give us enough concurrency since we are using > MVCC. Could you give me some examples? Yes, but UPDATE/DELETE in SERIALIZABLE mode will cause elog(ERROR, "Can't serialize access due to concurrent update"); in the case of the-same row update. Oracle sets implicit savepoint before executing a statement. In Postgres - entire transaction will be aborted... I have some ideas about savepoints... may be in 6.6 or 6.7... Vadim