This corresponds to SQL_TXN_READ_UNCOMMITTED. A read is never prevented by locking, nor do read rows stay locked. The data being read may or may not be committed, hence there is no guarantee of transaction integrity.

Historical Read Committed

Starting with release 5.0, Virtuoso has a non-locking, versioned read committed transaction mode. This is similar to Oracle's default isolation.

If a locked row is read without FOR UPDATE being specified and another transaction owns the lock, the reading transaction will see the row in the state it had before being modified by the transaction owning the lock. There will be no wait. If a row has been inserted but the insert not committed, the row will not be seen by the read committed transaction. If a row has been updated or deleted, the row will be seen as it was before the uncommitted modifying transaction.

If a row is read in read committed mode with FOR UPDATE specified or as part of a searched update or delete statement, the read committed transaction will wait for a locked row and will set an exclusive lock on the row if the row matches the search criteria. This exclusive lock will be held until the read committed transaction terminates.

Hence, if FOR UPDATE is specified, a read committed transaction will have repeatable read semantics, otherwise it guarantees no repeatable read but does guarantee that uncommitted data are never seen.

To make this the default mode, set DefaultIsolation in the Parameters section of virtuoso.ini to 2.

This transaction mode causes all DML statements to commit after every modified row. This is useful for single user situations where one does large batch updates on tables. For example, an update of every row of a multi gigabyte table would be likely to run out of rollback space before completing. In practice, one can end up in a thrashing situation where a large transaction is in progress, is interrupted by a checkpoint which must temporarily roll back the changed pages, then again resume the transaction etc., leading to serious server unavailability. Note that normally the ini parameter TransactionAfterImageLimit places a cap on transaction size, catching situations of this type before they lead to thrashing.

The row by row autocommit mode prevents this from happening by committing each updated, inserted or deleted row as soon as all the indices of the row are updated. This mode will still maintain basic row integrity, i.e. if the row's data is in one index, it will be in all indices.

This mode is good for any batch operations where concurrent updates are not expected or are not an issue. Examples include bulk loading of data, materialization of RDF inferred data etc.

This mode is enabled with the log_enable function. If the bit of 2's is set in the argument, row-by-row autocommit is enabled and the setting will persist until modified with log_enable or the calling connection is disconnected or the calling web request terminates. Thus, an argument of 2 enables row-by-row autocommit and disables logging. An argument of 3 enables row-by-row autocommit and enables logging. This will cause every updated row to be logged in the transaction log after it is updated, which is not very efficient.

Since transaction-by-transaction recovery is generally not an issue in batch updates, a value of 2 is usually better. If the server is killed during the batch operation, it may simply be restarted and the operation redone. Losing the first half through no logging will not be an issue since the operation will anyway have to be redone.

There is a slight penalty to row-by-row autocommit in comparison with making updates in larger batches but this is under 10%.

The transaction will wait for access to exclusively locked rows and will lock all rows it reads. The locking of read rows can be shared or exclusive depending on the FOR UPDATE clause in the SELECT or the SQL_CONCURRENCY statement option. In the case of a select over a range of rows where not all rows match selecting criteria, only matching rows are locked. This mode guarantees that any row locked by the reading transaction can be re-read on the basis of its identity (primary key) and will not have been changed by any other transaction while the locking transaction is in progress. This mode does not prevent another transaction from inserting new rows (phantoms) between rows locked by the original transaction.

This mode guarantees that concurrent transactions will look as if the next transaction started only after the previous terminated. This is like repeatable read but prevents phantoms. Space found to be empty in one read will remain empty in the next read while the transaction is ongoing.

Serializable isolation is implemented by locking all ranges of rows matching criteria pertaining to the ordering index in a select. The range here includes the last row before the first in the range. An empty range can be locked by locking the row before the range by a special follow lock, which prevents insertions to the right of the locked row. A by-product of this is that serializable locking guarantees that a select count will give the same result repeatedly unless the transaction itself affect the rows counted.

Serializable isolation is slower than repeatable read and not required by most applications.

All insert, delete and update operations make an exclusive row lock on the rows they operate on, regardless of specified isolation.