Why use transactions in SE?

I've been asked by a client to examine the perceived need to implement transactions and logging on their SE database. This is a small site (20 users) and a correspondingly small database (40 tables, 100Mb on disk). On the way home, I got to thinking (there's a change :/) about whether or not transactions on small databases are in fact a worthwhile investment on an existing small application w/SE. Firstly, discount the fact that transactions implies logging and that gives you the oppurtunity to rollforward the database in the event of (some types of) failure. This is an obvious plus, and in itself may be justification for the change. However, the following "features" need to be considered; 1) Small systems often only have one disk. Small systems typically have commodity hardware (PCs) and in a lot of cases operate with a singleton disk. If this is the case, then the benefit of maintaining the logging for the purposes of DB recovery is obviously reduced. 2) SE can only do unbuffered logging. If the disk drive(s) in the small system are of the typical sort found in PC's, then they are of a moderate level of performance, and usually unconfigured for mirroring/striping (RAID 0 if you like). The difference in performance between using the operating system buffers (no logging) and maintaining the logs in an unbuffered manner can be anything from insignifigant to massive, depending on the rate of data change and other factors. On a system where performance is not premium, the slow-down may be more of a hinderance than the time lost from failure if the previous night's backup needs to be restored. 3) SE has no isolation level other than "Dirty read/Uncommitted read". I've found one of the most useful features of the On-line engine to be the Cursor Stability and Repeatable Read modes of isolation. These modes guarantee (sort of) your data in a complex change, and is one of the big pluses when puting a transaction into the code. Because SE cant do this, it allows the horrible situation of programs left with phantom rows, because another program rolled back its (insert) work. At the very least, rows can be easily retrieved that have changes subsequently made to them, with the retrieving program left with no idea of what's gone on behind its back (but you know all this, and I'm just waffling). Sure, if you don't have transactions, two or more programs can still go around blissfully modifying each others working set, *and* you dont have the benefit of multiple locks so you *might* say it's all even on this score. So what about the other advantage of transactions, in that you can rollback in the event of an error, or only commit when everything's done? Great when you can guarantee read locks on the transaction, and you know that nobody else has a row of yours, and they're not about to update it with (now) old data. But as above, it could be a bloody nightmare when you can't make such a guarantee and you either rollback an insert, or commit a delete. Unless the programs are well behaved and always reread rows from a previously opened cursor prior to update (thereby rechecking for new locks or changes), you might as well not have the transaction, and save yourself the performance hit of the logging, the very good chance of hitting a number of locks wall, and the recoding expense. In this customer's case, I think I'll check their programs for "select for update"s, look at their error handling, see if the programs reread a record from a cursor prior to change, and recommend *against* implementing transactions and logging. Comments anyone? [dons flak jacket and fire proof suit] Bryan Tonnet batonnet@zeta.org.au