> the first table (for the select) is a look-up "types" table. Only > types that do not have addresses assigned to that type can be deleted. > As soon as there is another record that assigned to that type, the type > can't be deleted.
I guess I don't see a problem. Assuming you have declarative referential integrity - a defined relationship - no one should be able to delete records in one table if their deletion would create orphaned records in another table. Perhaps I'm missing something, though. > the other side is that another thread wanting to assign an address to > a type will check first if the type exists (it won't be there: the thead > before deleted the type so throw an error instead). > > in other words, ref integrety of the db is now encroaching within the > code layer. I don't really understand what you're saying here. If you're using declarative referential integrity, it doesn't really matter (from the DBA's perspective) whether your application code reinforces that or not - the point of DRI is to prevent applications from being able to screw up relations. > (1) everyone is using SProcs for data integrety, using the db (and not > cftransaction) for table and row locking. Damnatation! we can't (a > ruling from the boss - portability of code to diff db vendors) Well, frankly, that shouldn't matter. Performing a transaction within a stored procedure is equivalent to doing it with CFTRANSACTION, from the perspective of declarative referential integrity concerns. > (2) no one is going to this level of data locking and integrity (ie: > glossing over what's actually happening and hoping for the best). I say > this because associated techniques of re-read before write (to see if > another thread has changed the record while it's being edited) or using > timestamps (last updated by...) are not commonly covered in listserv > posts (yeah, I know lots of people do this or similar but you don't hear > much about it). Honestly, most people don't worry about this for most systems, in my experience, based on the minimal likelihood for collisions between requests (or the expectations of those people concerning the likelihood of those collisions). However, we've been doing those sorts of things in web applications since CF 2.0 at least - we even cover the use of timestamps for this purpose in some of our classes! > but this gets back to the initial observation that no one is explicitly > locking tables or rows. That's true. In the vast majority of cases, it's enough to let the database do this for you, or to set the isolation level appropriately for your transaction. The database does the locking for you. > Every method using more than one query needs at least a transaction > over them (if they are dependent on each other, that is), and if that's > the case, why aren't table and row locks used as well during the > transaction? Every query, within a larger transaction or just by itself, causes the database to lock records. In most cases, it's simply not necessary to do this yourself explicitly, since the database is doing it for you. When you place multiple queries within a transaction, the isolation level of that transaction will control the breadth and duration of those locks. > A cftransaction on one thread will NOT stop other threads accessing > the tables - it only places restrictions on the thread that it is on > (my understanding) I think your understanding is incorrect. To the extent that a transaction locks records, other threads/process/database connections will have limits placed on their ability to access those records. Again, that's something the database does for you automatically. It doesn't matter whether the transaction is within an SQL batch, stored procedure or CFTRANSACTION tag. > yes, you're 100% right, but that's during the query, not the whole > transaction (which is where isolation="SERIALIZABLE" comes in). In many cases, it's simply not necessary to lock records for the duration of a transaction. If it is, though, you'd want your transaction to be serializable, and you'd set the isolation level accordingly. > > It's my understanding that SERIALIZABLE is the default ISOLATION > > value for CFTRANSACTION in CFMX, > > is this really the case? It is according to the CFMX release notes, if I recall correctly. > To be honest, I thought default cftransaction isolation was > optomistic locking... I believe that in CF 5 and earlier versions, it was dependent on the specific database and its drivers, which in most cases would give you an isolation level of "read committed". > I really do need to find out what CFTRANSACTION is actually doing > under the hood. Turn on your database log/trace functionality, and find out! Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ phone: 202-797-5496 fax: 202-797-5444 ---------------------------------------------------------- You are subscribed to cfcdev. To unsubscribe, send an email to [EMAIL PROTECTED] with the words 'unsubscribe cfcdev' in the message of the email. CFCDev is run by CFCZone (www.cfczone.org) and supported by Mindtool, Corporation (www.mindtool.com). An archive of the CFCDev list is available at www.mail-archive.com/[EMAIL PROTECTED]
