Tamino version 4 introduces the _isolationLevel transaction level parameter, in addition to the _lockmode parameter from Tamino version 3 (formerly called _isolation). Concerning the interaction of these two parameters, the documentation (Documentation/xprog/xprspstr.htm#xprstran) leaves some questions unanswered, and I wanted to ask those questions now.
The isolationLevel parameter is fairly straightforward, and seems to correspond to isolation principles taught in database courses and documented in such places as
Postgres doc
or
SQL Server doc
An additional stableCursor mode has also been added, which is between the traditional READ COMMITTED and REPEATABLE READ levels.
The lockmode parameter of version 3 was also straightforward to understand, where protected locks corresponded to SQL’s “SELECT FOR UPDATE”, and shared locks correspond to SQL’s “SELECT” (in a normal READ COMMITTED isolation level database such as ORACLE or SQL*SERVER).
Although not explicitly stated, Tamino appears to enforce isolation levels via locking, rather than by another mechanism, such multi-versioning like Oracle. So, specifying an _isolationLevel in effect more or less specifies what locks to obtain and obey during reading. Assuming this is true, my questions are:
1. committedCommand
If isolationLevel >= committedCommand, reading with lockMode unprotected has no meaning. committedCommand means “read only committed documents”, but lockMode unprotected means “read uncommitted changes”.
2. stableDocument
Using isolationLevel >= stableDocument implies that all documents read are locked with a shared read lock (like lockmode=shared). So specifying lockmode=shared would have no effect, and specifying lockmode=unprotected is a contradiction.
Is this right?
Thanks,
Bill
Forgot #3 and #4:
3. Stable Cursor
The point of “stable cursor” isolation level is to lock the object only for the duration that it is accessible from the cursor. But in order to get this functionality, which lockmode should I specify? If I specify lockmode “shared”, the object will be locked for the duration of the transaction (too long). If I specify lockmode “unprotected”, it seems to imply no locking at all. How do I get the object to be locked precisely for the duration of the cursor?
4. update
lockWait=yes can be used to block reads until other transactions commit or abort. What about updates? Will updates block until other transactions commit or rollback?
Special case to consider is isolationLevel=sequential. In this mode, if I query a count of all objects, other transactions will be blocked from inserting any records until my transaction finishes. What will happen if other transactions try to insert a record? Will they block and then succeed?
Hi Bill,
you are right, isolation levels correspond to those taught in database courses and they are enforced via locking. Normally, the _isolationLevel parameter determines the types (none/shared/exclusive) and granularities(collection/doctype/document) of locks Tamino needs to ensure correct transaction behavior. The _lockMode parameter is an additional parameter for advanced users who exactly know what type of document locks their applications need. The _lockMode parameter overrides the document lock types as they would have been requested for the specified _isolationLevel.
Answering your questions in detail:
1. Running a query with _lockMode=unprotected means that Tamino does not acquire locks on documents, independent of the transaction’s _isolationLevel. As a user of this feature you must be aware, that Tamino in this case no longer ensures that documents read in the query are committed. However the application might know that it is committed/reliable, e.g. catalogue data that is not updated at all. Running a query with _lockMode=unprotected can result in better performance.
2. It is true, _lockMode=shared has no effect with _isolationLevel>=stableDocument. _lockMode=unprotected is not treated as a contradiction, see answer above.
3. It is possible to combine _isolationLevel=stableCursor with _lockMode. However, when using the _lockMode parameter, you switch off the “stableCursor” for that request. The _lockMode parameter always specifies transaction-wide locks on documents (or to acquire no document locks at all).
Only if you do not specify the _lockMode then a document lock can be released as soon as the document is no longer accessible from the cursor. Of course, this will only be done if the document is not locked by another cursor and if no other query with _lockMode=shared/protected had locked the document for the entire transaction.
Please note, with XQuery cursors “stableCursor” behaves like “stableDocument” in V411. In V414 this applies only for sensitive XQuery cursors.
4. _lockWait=yes is not only to block readers but also to block writers. Every writer needs exclusive locks on documents to be updated/inserted/deleted. Respective lock requests experience locking conflicts if concurrent transactions already hold shared locks on these documents.
In general, the _lockWait parameter specifies what Tamino shall do if a lock (shared or exclusive) cannot be granted immediately. In case of _lockWait=no Tamino returns an error (9155) otherwise Tamino waits until the lock will be granted.
For _isolationLevel=serializable Tamino acquires shared locks on entire doctypes. In this case it is not possible to modify the doctype extent, i.e. it is not possible to update any document, to delete any document or to insert a new document. With _lockWait=yes such modifying requests will wait until the shared lock on the affected doctype has been released.
Hope this helps,
Michael
Hi Michael. Thanks for you help!! I think the key fact is that:
However, according to your message on this BBS:
Please note, with XQuery cursors “stableCursor” behaves like “stableDocument” in V411. In V414 this applies only for sensitive XQuery cursors.
Bill
Hi Bill,
with V414 and sensitive XQuery cursors, we calculate cursor results on demand with every fetch-request. We have to make sure that document contents do not change between fetch-requests. Therefore, we acquire locks on all documents that are read in from external memory into the server. Because of an implementation limitation, these locks are not released before transaction end. This is currently independent of the isolationLevel settings. This holds for V411 and V414 but is subject to change in future versions.
Opposite to sensitive cursors, insensitive xquery cursors do not reflect changes in documents. In case of _isolationLevel >= stableDocument, we hold locks as with any other xquery until end of transaction. In all other cases we acquire document locks during cursor open processing as needed and we release such locks at end of open processing. Subsequent fetch operations only reflect content as it was valid during open-processing (the result set remains unchanged, not necessarily the underlying documents/document content).
Michael
Just looked at the 4.1.4 manual; thanks for fixing the documentation issues listed above! (lockmode default value, lockmode overriding isolation level, ANSI isolation level correspondence, etc.)
Bill