I posted some questions about how the delete/update is done, those answers would help me understand better what is needed for a solution.
I am going to start a separate thread concentrating on RowLocation guarantees from store. Some other answers below. Andreas Korneliussen wrote: > Mike Matrigali wrote: > >> There are very few cross thread dependencies not managed by locks >> currently. These things add extra complications to current and >> future code. Also I want to understand clearly the new restrictions >> be imposted on the access methods (both current and possible >> future). In the future we would like to do more automatic space >> reclamation as part of the zero-admin goal, the ability to do this >> in the future internal to access methods is probably affected by >> the proposals here. >> > > I think the complexities are there already. It seems very hard to know > under exactly which conditions, a RowLocation remains valid. We have > assumed that the RowLocation will remain valid as long as we hold a > table intent lock (valid means that it either points to the same row or > to a deleted row). > > That is what we concluded from the previous discusssion about validity > of RowLocation. If you in the future need to make code, or there already > is code, which breaks this assumption, we would need to know which other > mechanisms we should use to either verify that the RowLocation is valid, > or to block the system to make it invalid. There is already code that breaks this assumption, the in place compress table. It currently is only executed as a call to a system procedure: http://db.apache.org/derby/docs/dev/ref/rrefproceduresinplacecompress.html But the hope was in the future to do the same kind of work that this procedure does, internally in background. As you have already determined that off-line compress is even more of a problem, but it does run under an exclusive table lock. After it runs the container you were connected to does not even exist any more. > > Locks can be used to manage cross thread dependencies, however they are > bound to the transaction, and therefore does not help very much for > cursors held across commits. So if the only mechanism we can have to > ensure that the RowLocations are valid, is by the means of locks, I > doubt we can support the feature of scrollable insensitive *holdable* > updatable resultset. I agree, I don't believe derby is currently architected to correctly implement "holdable" SUR. I don't think the outside event driven approach is the right way to go. > >> It is true that the current access methods don't reuse row locations >> until a table level lock is granted. But your project would be the >> first dependency on this outside of the access method implementations >> themselves. It is very clear the contract that the access methods >> have with their clients while locks are held on the data they are >> looking at, what you are proposing is a contract on unlocked data. >> > > I guess we are the first to use RowLocation without holding a lock on > the row. This is necessary, unless we simply make SUR cursors set locks > for all rows in the cursor independent from isolation level. > >> Note that the current "in-place" compress will MOVE rows from one >> row location to another if one does not have a row lock on the row. >> This is done in the 2nd phase and only holds an intent lock, and >> exclusive row locks on the rows being moved. >> The off-line compress only does work under an X table lock. >> So the row that you are updating actually will exist in the table, >> but currently you will request the old location and will get back >> a delete row indicator. I think because of this option 1 does not >> work. >> > Are you saing that RowLocations can be invalidated by "in-place" > compress even if we hold a Table intent lock ? The "in-place" compress will do the following take a row a row location N and do the following: insert row at new row location M delete row at old row location N possibly purge old row location I don't understand how this will affect your code, it depends how you search for the row, and what errors are ok to throw. > > How do you call "in-place" compress today ? Does the system use it > automatically, or do the user have to call it manually ? > >> The state of held cursors across commits is very murky in the standards. >> We looked very carefully at forward only held cursors, and the standards >> there are carefully worded to basically not promise anything about the >> rows that were viewed that preceded the commit (clearly since the >> standard says the only thing you can do after the commit is a next to >> get a new row or close - never can access rows looked at before the >> commit). What options are legal >> implementations of updatable scrollable result sets for held cursors >> across commits? Do the standards guarantee anything about data in the >> cursor looked at before the commit? >> > > I looked at the SQL standard, and for cursors held over commit, it says: > > "If the cursor is insensitive, then significant changes are not visible" > > Andreas > >> >> >> Andreas Korneliussen wrote: >> >>> Mike Matrigali wrote: >>> .. >>> >>>> If possible I would like to see a solution that does not require >>>> special >>>> messages sent back and forth between modules about state. >>>> >>> >>> I am not entirely sure what restrictions you want to put on the >>> design, it is a bit unclear to me. >>> >>> I have considered some other solutions: >>> >>> 1. Change the locking behaviour, so that a table intent lock which is >>> set by an updatable cursor, is kept as long as the cursor is open - >>> this will ensure that the RowLocations are valid. >>> >>> 2. After a commit, we could clear all data in the internal table in >>> the SUR. The problem with this approach is that the resultset would >>> not necessarily be repopulated with the same data - it would be >>> sensitive for changes across its own transactions commits, it would >>> be highly ineffecient. >>> >>> 3. Let the cursors notify the OnlineCompress module that it should >>> fail any attempt to compress/defragment or purge the table. >>> >>> More details on what I suggested yesterday: >>> >>> The OnlineCompress class could provide an event mechanism, where >>> subscribers (OnlineCompressListener) register themselves to listen to >>> OnlineCompressEvents. The ScrollInsensitiveResultSet class could then >>> implement the OnlineCompressListener interface, and register itself >>> once it starts populating the table with RowLocations. The >>> OnlineCompress class then simply notifies all listeners once it is >>> doing defragment / compress. >>> The listeners should unregister themselves (i.e >>> ScrollInsensitiveResultSet class could do it once it closes). The >>> OnlineCompress class could use a WeakHashMap to put the listeners >>> into, in case they are not well-behaved. I have not checked if derby >>> already has event manager type of modules, if it does, I would >>> attempt to reuse them. >>> >>> Please also let me know if any of the other alternatives seems better. >>> >>> >>> Andreas >>> >>> >>>> Andreas Korneliussen wrote: >>>> >>>> >>>>> Some context: In scrollable updatable resultsets, we populate an >>>>> internal table with the following data: >>>>> >>>>> <Position> <RowLocation> <RowUpdated> <RowDeleted> [<DataColumn>]+ >>>>> >>>>> Example layeout: >>>>> >>>>> 1 <1,10> false false 1,"a",3 >>>>> 2 <1,11> false false 2,"b",2 >>>>> 3 <1,12> false false 3,"c",9 >>>>> >>>>> >>>>> When doing updateRow(), or deleteRow(), we use the RowLocation to >>>>> navigate to the row being updated. >>>>> >>>>> Problem: >>>>> For holdable cursors, we will release the table intent lock when doing >>>>> commit on the transaction for the cursor. >>>>> >>>>> The table intent lock, prevents the system from doing a compress of >>>>> the >>>>> table, causing all RowLocations to be invalid. In addition, it >>>>> prevents >>>>> reuse of RowLocation for deleted + purged rows. >>>>> >>>>> In order to support holdable scrollable updatable cursors, we consider >>>>> having a service which allows the system to notify subscribers (i.e >>>>> cursors) that it has executed i.e a compress. >>>>> >>>>> If the user then calls updateRow() or deleteRow(), we can then give an >>>>> exception like: >>>>> >>>>> "The row could not be updated, because its location has been >>>>> updated by >>>>> the system" >>>>> >>>>> In addition, we consider having a reclaim of locks, so that immediatly >>>>> after a commit, the new transaction with the holdable cursor, may >>>>> reclaim the table intent lock. This will reduce the time period which >>>>> the system may compress the table, however not completely remove the >>>>> possibility of a compress. >>>>> >>>>> Any comments on implementing such strategy ? >>>>> >>>>> An alternative to this strategy, could be to go the other way: cursors >>>>> notify the system that it should not do compress. >>>>> >>>>> I would appreciate feedback on this topic, especially if you find any >>>>> pitfalls with the proposed strategies, or have better alternatives. >>>>> >>>>> Andreas >>>>> >>> >>> >>> >> > >
