Just going a bit off topic but..... we had a batch process that processed lots of data and I mean lots, but it didn't have any transaction boundaries. Someone was asked to add them. So what did they do? yes you got it. At the start of the routine they started a transactions and at the end of the routine they ended the transactions. Guess what? the lock table filled up and the whole thing fell over.
The fun you have with transactions is endless :-) Les -----Original Message----- From: Stevenson, Charles [mailto:[EMAIL PROTECTED] Sent: 22 June 2005 18:50 To: [email protected] Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START & COMMIT/RO... From: Les Hewkin > > What would you call a really big transaction? A transaction should encompass all updates that should happen (or be abandoned) as a group in order to insure logical data integrity at the application layer. Let me use an example, keeping the preceding statement in mind. (Please don't belabour the details. This is just to get the broad idea.) Suppose out in the real world, a car gets repaired. This is one chunk of work that the customer typically signs off on and pays as a whole. In a database application representing this bit of reality, at some point there is a work order that needs to be posted. Maybe - ~maybe~if you design it right - pieces of it can be posted as separate transactions in an attempt to keep transactions as small as possible, but to completely post the work order: - multiple parts each need to be moved from, inventory to car. - associated double entry journal entries need to happen to keep accountants happy. - some low-stock condition might trigger automatic re-order of some parts. (one could argue this is separate, but depending on how it is programmed this may end up nested in this Transaction) - some parts might be consignment and vendor info needs to be updated. - serial numbered parts might require special treatment. - mechanic's (plural?) hours need to be recorded in timekeeping system. - associated accounting journal entries. - Invoice needs to be created. - customer info updated. - maybe immediate payment: associated accounting updates. - Car history needs updating (let's say this is a regular customer & we track his cars) - warranty or rebate info needs to be sent or queued for auto manufacturer. - more accounting journal entries? - Multiple user-defined cross-reference files (if/when native indexing not used) Under normal circumstances all that happens as one big lump (at least your business user hopes so. But one can imagine scenarios where part of the repair is in dispute, the customer agreed to part of the work, or is unhappy, etc. ) so normally a given repair is either Open or Closed. One might program it so that this post is one TRANSACTION, maybe with nested TRANSACTIONs within it. These would be "really big" transactions. There may be a lot of processing that happens between TRANSACTION START and COMMIT. Anywhere along the line some abnormality might cause a TRANSACTION ABORT. All of these would have to happen as a unit, or be rejected as a unit in order for the DB (at an application level) to remain self-consistent. If one were smart, one could invent stand-alone partial posts where one transaction involved one part or one labour entry, etc.. But even in those cases, each transaction would have to update part, or labor info, the repair record, and accompanying accounting journal entries all as one unit to maintain application integrity. These would still be "big" if not "really big" transactions. In either case, each transaction would involve multiple updates to files. More than one file &/or record may involve sequential ids. Going into the transaction one might not know all that will be needed. Pessimistic and optimistic locking methods changes what one knows as a transaction begins. If you place a readu lock as soon as you read the record, then do the processing and decision making within the transaction, the transaction takes longer and locks remain set longer. If you do the processing first, then you know the updates required before you TRANSACTION START and re-read, placing readu locks, and compare what you expected with what you got. Almost all applications have one central document (like the repair order above) that everything revolves around. It usually defines the relationships to most of the other major entities in the db. There are usually updates that involve that central document and the entities connected to it. Those updates usually have to happen as a group, one logical transaction, for the sake of application data integrity. This is the sort of thing I was thinking about when I posed the question of how to control sequential ids, releasing that control ASAP within big transactions. ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/ This message has been comprehensively scanned for viruses, please visit http://virus.e2e-filter.com/ for details. This e-mail and any attachments are confidential and intended solely for the use of the addressee only. If you have received this message in error, you must not copy, distribute or disclose the contents; please notify the sender immediately and delete the message. This message is attributed to the sender and may not necessarily reflect the view of Travis Perkins plc or its subsidiaries (Travis Perkins). Agreements binding Travis Perkins may not be concluded by means of e-mail communication. E-mail transmissions are not secure and Travis Perkins accepts no responsibility for changes made to this message after it was sent. Whilst steps have been taken to ensure that this message is virus free, Travis Perkins accepts no liability for infection and recommends that you scan this e-mail and any attachments. Part of Travis Perkins plc. Registered Office: Lodge Way House, Lodge Way, Harlestone Road, Northampton, NN5 7UG. ------- u2-users mailing list [email protected] To unsubscribe please visit http://listserver.u2ug.org/
