Re: Share Mode Locking Problem...

>} Oracle works the same way as Sybase and *is* number 1 :-) Wrong ! Sybase uses an optimistic strategy, where requests are optimized for commits, not rollbacks. Oracle uses a pessimistic strategy, where requests are optimized for rollbacks, not commits. This means that Sybase really writes in the database, using the transaction log to save the old values. Writing the commit record in the transaction log enables the transaction to end immediately, so logs may be backed up immediately. Only when a rollback must occur is the process quite long: data preserved in the transaction log must be restored in the database. This technic is fast for requests that are mainly read-only or should complete committed most of the time. However it is not as fast if most of the time requests have to be rolled back. However Sybase still does not have row-level locking mode, but only page-level locking mode. This means that unmodified rows are exclusively locked by the modified rows on the same page. To implement row-level locking (or even column-level locking), Sybase should extend the size of its locks table to store not only the page pointer (a logical page of a given database, converted into a device number and physical page number using the master.dbo.sysusages page-mapping), but also a row index, and may be a column id. Informix uses the optimistic strategy, but allows row-level locking mode (thanks !). However, as opposed to Sybase, it does not process the versioning of modified pages. It could do it while preserving an optimistic strategy most of the time, by linking the modified database page with its associated page in the transaction log which holds the unmodified pages, but row-level locking complicates this process: many rows could fit within one page, and each one may be modified by different concurrent transactions, so the same modified page may have been concurrently modified several times, divided into several pages or compacted with another page of rows, while still holding for these pages the same original page. To manage this, Informix should store in the transaction log the timestamp of the original page, and then keep in the database the timestamp of each row once they have been updated by concurrent transactions. Once a transaction has locked and modified a page, the rows seen by the transaction will be only those having a timestamp less than or equal to the timestamp stored in the transaction log. But what if a transaction C commits another row in the page before transaction B completes ? Committing will have to further search other running transaction to find those that have stored an unlocked row with an old version of the commited page. Rolling back will also only restore in the database those rows of the transaction log that have a timestamp equal to the database row timestamp, not higher. The beginning of the transaction timestamp is always the timestamp to use (with care of multiprocessor servers). This is complicated, but in fact it requires the same work as in the pessismistic strategy which Oracle applies to ALL transactions. Most of the time, pages won't be modified by two concurrent transactions, and most of the time the request will be committed. So the Informix and Sybase strategy run faster than Oracle. Oracle only makes change within the transaction log, and the database is modified after the commit record is wrtten in the transaction log. Then the transaction log contains the records that must be updated in the database, so committing may be long. Because other rows may exist in the same page as the page for modified and commited rows, and because concurrent requests may occur on those extra rows and modify them, it is not possible to locate immediately the pages to be modified by the commit process. All commits must also look at other update locks that are still pending on the same data pages as the currently committed pages, so that the page pointer of their rowid will be updated accordingly. This strategy is best for "what-if" requests that always end in a rollback. Whatever the choice, I think that a RDBMS could implement both strategies by correctly using timestamps and by managing in each transaction if the modification was applied in the database or not (to know where the original data is, in the database or in the transaction log???). And an optimizer could benefit of procedures which use explicit transactions unconditionally ending with ROLLBACK instead of COMMIT. It could also benefit of the implicit AUTOCOMMIT mode for RDBMS that support explicit transactions like Sybase or Informix (not Oracle or ANSI databases). And a user request prior to update requests may help the optimizer such as: SET TRANSACTION TO COMMIT (optimistic model), or SET TRANSACTION TO ROLLBACK (ANSI default mode), which will set a session preference for all requests sent to the server for which a transaction is in progress but no COMMIT or ROLLBACK is found in the query to predict whever the update will be updated or committed. Both modes should allow further COMMIT or ROLLBACK without problem except performance difference.