> Can anybody explain the cftransaction tag isolation parameter. > As I dont seem to be able to find any information other than > very basic explanations. > > What is happeing is if I place a Read_uncommited level on a > DB Read, when I update the same table I get a 'Cannot Update > a table that is Read Only'. > > I tried then putting a Read_Uncommited level on the update, > but the running queries went off the scale. The ISOLATION attribute is used to specify how that transaction will behave with regard to database locking. I'll try to provide a very brief, general (and incomplete) description; the details may vary slightly between database servers. There are four possible values: READ_UNCOMMITTED means that the transaction will not honor locks that it encounters, and it won't lock anything that it touches. This may result in bad things happening, such as "dirty reads" (reads containing data that hasn't been committed to the database yet). On the other hand, in some cases the performance gain may be worth the possibility of retrieving bad data. For example, if you wanted to retrieve aggregate values calculated from half a million rows of data, it probably wouldn't matter too much if one of those rows was being written to by another transaction during the calculation. READ_COMMITTED, the default value, means that the transaction will honor any locks that it encounters, and that it will place locks on data as needed - exclusive locks when writing data, and shared locks when reading data. This is generally the behavior you want when running transactions, at least. Your transaction may still be vulnerable to nonrepeatable reads (if you have multiple statements returning the same row, the data may have changed between those reads) and phantom data (when your transaction tries to select a nonexistent row, but another transaction inserts that row after your first select). REPEATABLE_READ prevents dirty and nonrepeatable reads. SERIALIZABLE prevents dirty and nonrepeatable reads, and phantom data. Serializable transactions can be run in any order, and you'll end up with the same result. These four isolation levels are those specified in ANSI SQL-92, so you can find out more about them from a good SQL reference. In general, the more isolated your transaction is from other transactions, the more locking will be required and they'll be more likely to have to wait in line before they can run. As for your particular problem, you'd have to provide more information to determine what transaction level you should be using. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ voice: (202) 797-5496 fax: (202) 797-5444 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
