21.06.2014 0:52, Nikolay Samofatov wrote: > Hello, All! > > We have encountered subtle errors and data corruptions when using complex > triggers/stored procedures in READ COMMITTED > transactions.
I assume you tell about logical data corruptions, not physical, correct ? > Most applied programmers don't think that while their SP/trigger is executing > the world is changing underneath them. > So lost updates and inconsistent data changes are happening during concurrent > operation. I.e. no real data corruptions, just from application POV ? > To address this problem, in our engine builds we changed behavior of READ > COMMITTED + REC_VERSION transactions to ensure > cursor stability. There are two close but very different things about cursors. Let me first speak about both to avoid confusing: a) cursor stability, when cursor could see changes made but cursor itself or by statements running at nested level. It have nothing common with transaction isolation modes and, iirc, standard describes such behavior as vendor-specific. It is addressed in fb3, btw. b) cursor sensitivity, when cursor could see changes made by statements running in separate transactions, committed while cursor reads data. Of course it is impossible to have sensitive cursor in snapshot transaction. IIRC, standard allows both types of cursors (sensitive and insensitive) and default behavior is vendor-specific. I assume you speak about (b), so lets name things correctly, please. I've read your patch not applying it, so i could miss something - please correct me, if i write stupid things below. > Each request is executed in its own snapshot, that is released when execution > of request ends. Could you explicitly describe behavior of sub-requests, dynamic request in the same transaction (EXECUTE STATEMENT) and requests, running in autonomous transactions ? > We tried to be extra careful not to hurt performance while establishing these > snapshots. > We do it much more efficiently than done for isc_tpb_concurrency. AFAIU, you maintain list of active transactions at top-level request. You create this list using new Lock Manager call which iterates thru LCK_tra locks and calls callback which fills list (actually BePlusTree) of active transactions. Later reader consult this list (i.e. search BePlusTree) to check transaction state. Is it really faster than copy part of (already cached) TIP contents into transaction and then test a bit in a plain vector ? Also, it allows to reuse existing code. Next, i see that you trying to adjust transaction lock data to change garbage collection threshold. At first look it is correct and highly necessary, but i think it can't work as it can't change values already cached by concurrent transactions. I.e. you can't make OST less than it was known to someone (except of yourself). Therefore i consider that new transaction mode should set its lock data not to the self number but to the OAT value found at transaction start (i.e. same as snapshot transaction). > This mimics Oracle's behavior in that regard but Oracle doesn't always > implement nesting correctly, we do. Could you explain, please ? > I attach patch for this functionality to give you an idea of implementation. > It depends on a couple other changes so it > doesn't apply to FB2.5 cleanly. > > Any objections if I start porting this functionality to FB3? The principal thing there is how new isolation mode is introduced. You introduced a new setting in firebird.conf, i.e. ANY read-committed transaction will have new behavior. I think it will be better to introduce new option for read-committed mode (probably make it default) to allow user to have a choice. Regards, Vlad ------------------------------------------------------------------------------ HPCC Systems Open Source Big Data Platform from LexisNexis Risk Solutions Find What Matters Most in Your Big Data with HPCC Systems Open Source. Fast. Scalable. Simple. Ideal for Dirty Data. Leverages Graph Analysis for Fast Processing & Easy Data Exploration http://p.sf.net/sfu/hpccsystems Firebird-Devel mailing list, web interface at https://lists.sourceforge.net/lists/listinfo/firebird-devel
