Re: Help on: locking, isolation levels, transactions

Complex question you are asking. I'll give some insight but you had better learn this stuff inside out before you do any real implementation. I'll start with transactions. You MUST have a logging database to even be able to use transactions. Transactions are enclosed within a BEGIN/COMMIT or BEGIN/ROLLBACK pair in your code. If you just have a BEGIN you get an automatic rollback. By default Informix will make every SQL insert, update , and delete its own transaction. In order to define a more complex unit of work you need BEGIN WORK...COMMIT WORK. In order to activate the isolation levels (other than dirty read) you need to be in an explicit transaction with Informix. Also, all locks that are on modified data items will remain until the transaction is committed or rolled back. You need to be aware of the granularity of locking in your database. Tables can be created with row or page level locking. Page level locking can lead to contention in an OLTP multi user system. Informix support 4 isolation levels. ------------------------------------ 1) Dirty Read - Does not place locks or honor any. This means that you can access data rows that are locked by others; or even rows that have been inserted but not yet committed. 2) Committed Read - Does not place locks either. But only returns committed data. 3) Cursor Stability - If in a cursor each row fetched will get a lock. The lock is released when the next row is fetched (if it doesn't get updated). 4) Repeatable Read - If in a cursor each row fetched gets a lock and the lock is kept until the end of the transaction. The only isolation level that obeys the two phase locking rule is cursor stability. This means that if you need to guarantee a serailizable transaction you need to use it. Repeatable read locks an extreme amount of data and should be used sparingly. Unless you declare a cursor WITH HOLD it will be closed when the transaction is committed/rolledback. UPDATE cursors require a transaction in progress in order to fetch. Also, if you issue an update where current of you need to e within the same transaction as you were when you fetched that row. Locking types ------------- Shared locks can coexist on a data item. A shared lock will be denied if a data item already has an exclusive lock on it. Exclusive locks are granted only when they are the only current lock on a data item. > > Does someone has an understandable overview about > > locking (shared, exclusive, promotable, ...) > isolation levels > transactions > > and how they all work hand-in-hand ?? > > We are running Online 7.12 on HP-UX. > > Thank you, > Jens > > -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net