RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
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: u2-users@listserver.u2ug.org 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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
In my opinion... NO : Retrofitting TRANSACTIONs into existing programs would be a nightmare. YES: Definitely use them when writing a new application from scratch and even a new sub-system enhancement if it is pretty much self-contained with limited, well-defined interfaces that write into the parent system. NO : I would caution against writing them into new programs that are enhancements to existing (sub-)systems. cds From: Les Hewkin 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 :-) --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
That could have been simply overcome by locking the file instead of individual records. I have done very large transaction boundaries for a specific type of system and they worked very well. Saves a lot of leg work and programming to manually create a recovery process for a major batch updated. Regards David Jordan 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 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Hi, Just got back from a short break camping so sorry for the late reply. What would you call a really big transaction? I look at it like this. You have to write the data away, you need the next seq number, so everyone else can wait. How long does it take to write away data to disk? We split lots of the seq numbers down. We use the cost center that the user works in as part of the record key. This means lots of control records but not many problems. It all revolves around getting the lock on the control record first. Think of how a manual system would work. Someone wants to put a file in a filling cabinet but someone else is already there, they wait for the other person to finish what they are doing. The amount of time you have to wait is minimal. The only problem you get is when someone new comes along and doesn't understand about locking the control record first. Hope this helps Les. (only another 150 EMails to read) -Original Message- From: Stevenson, Charles [mailto:[EMAIL PROTECTED] Sent: 16 June 2005 04:54 To: u2-users@listserver.u2ug.org Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... Ah-HA! Some real world transaction experience! Just what I wanted, Les. From: Les Hewkin Start trasnactions Readu control record then record.ID = control record write record control record += 1 write control record end end transaction This seems to work for us. We do have loads of control records for generating ID and we never have any missing numbers. And for really big 'trasnactions', where there is a lot of other writes mixed in, having the control record locked for the duration of the transaction is not a bottleneck for you? That was my main concern. cds --- u2-users mailing list u2-users@listserver.u2ug.org 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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
I came here for a flogging. No you didn't. Couldn't resist. Sorry. Serious reply to follow. -- Regards, Clif On Jun 16, 2005, at 12:31 PM, Bill Haskett wrote: However, it's always possible I could use a good flogging. :-) --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Yes, that was the point. A record counter contains no information about the record; it is an arbitrary pointer to the information. Our U2 (and other MV) databases do not provide for a key that is, to use some SQL implementation terms, AUTO_INCREMENT or INTEGER GENERATED AS IDENTITY. So we fake it with a NEXT.AVAILABLE style of counter. These should not be considered information, nor should they be used to verify during an audit. Think of them as disk addresses in the old sequential sector address scheme. Saying that transaction key 12345 has meaning is about as meaningful as an auditor saying, Why isn't there a transaction record at track 99AEF? (Yes, I meant the double meaning of meaning in that last sentence. This is English, a language that makes up in obscurity what it lacks in style.) So I maintain that a sequentially assigned RECORD COUNTER, is not something an auditor should ascribe any meaning to. In fact, record ID's used in this manner should be able to change from day to day, assuming referential integrity is maintained; and the auditor should not have an issue with that. It just a disk address, after all. Control numbers, on the other hand, are a completely different issue. Even though they may be sequentially assigned, they contain information about the record. Check number was a good example. These may be used as the key to a file. But the absence or presence of a single record in a single file should not be the sole auditing criteria. Just because there is a record with key 2045 in the CHECKS file doesn't mean squat. I could have put that record in there with the Editor. Any auditor who simply accepts that the record proves check 2045 was issued is the one to be flogged. Conversely, the absence of record with key 2045 cannot be construed to mean the check was not issued. I am not an accountant, but I think this falls under the category of single entry vs double entry accounting. That being said, we also need to recognize differences in the term audit. Accounting audits are different from information system audits. So when we use the term the Auditors we really should specify whether we are talking about accounting audits with an emphasis on fraud or information systems audits with an expanded scope of correctness. As always, just my opinion. Anyone want to discuss why most MultiValue systems don't know the concept of check digits on control numbers as a way to assist in information correctness? evil grin -- Regards, Clif ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Jun 16, 2005, at 12:50 PM, Drew Henderson wrote: I would think the key phrase is non-information content. Sequential numbers that have meaning, such as check numbers, would not, in my mind, fall under this category. Sequential numbers used for assigning ids to people, however, would likely fall into this category. Of course, a lot of it depends on the organization's use of that number, as to whether it is informational or not. Drew Bill Haskett wrote: --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Fine by me, but get your own thread; this one's taken. (Or at least add another sentence to the subject line.) From: Clifton Oliver ...Control numbers, on the other hand, are a completely different issue Anyone want to discuss why most MultiValue systems don't know the concept of check digits on control numbers as a way to assist in information correctness? evil grin --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
FYI. I took the liberty of forwarding this to the U2UG Enhancements Committee. -- Regards, Clif On Jun 15, 2005, at 10:01 PM, Steve Johnson wrote: This would be a very worthwhile enhancement IMO. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Charles: No, you guys aren't the only ones. This is a pretty miserable hole in the mvDbms functionality. However, since we've been writing this stuff for years the routines are already built to solve our problems. I just didn't know TRANSACTION bracketing was going to break them. Unknowns like this are the reason we haven't implemented transactions, as we haven't been eager to rework some core functions in our application. Bill -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stevenson, Charles Sent: Wednesday, June 15, 2005 8:53 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... Would this be a worthwhile enhancement request through U2UG? Are Ken and I the only ones whining about this: --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Clif: Since I've been in both the accounting and information systems fields for over 25 years, I can't say as I understand the term ...non-information content, sequentially assigned, record counter. I'm sure it doesn't mean what I think it does as I've found sequential counters to be an invaluable tool in a number of auditing scenarios. However, it's always possible I could use a good flogging. :-) Bill -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Clifton Oliver Sent: Wednesday, June 15, 2005 10:49 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... I just can't keep quiet any more. Any auditor who depends on a non-information content, sequentially assigned, record counters as an audit trail ought to be flogged and sent back to Auditing For Dummies. If your auditor demands that, I'd suspect they are a paper-based bean counter, not an Information Systems Auditor. -- Regards, Clif Member ISACA www.isaca.org ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Jun 15, 2005, at 8:47 PM, Stevenson, Charles wrote: Missing sequential numbers isn't important to me, but it's a good point for general discussion. cds From: Bruce Nichol This is all well and good if the commit goes ahead, but if rollback is the action, don't you lose a supposedly sequential root key into the vapours? Never to be seen again? That'd make an auditor go spare. 61,62,63,65,66... Hang on! Where's 64? Stop the presses! Everybody down and look for 64... --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
I would think the key phrase is non-information content. Sequential numbers that have meaning, such as check numbers, would not, in my mind, fall under this category. Sequential numbers used for assigning ids to people, however, would likely fall into this category. Of course, a lot of it depends on the organization's use of that number, as to whether it is informational or not. Drew Bill Haskett wrote: Clif: Since I've been in both the accounting and information systems fields for over 25 years, I can't say as I understand the term ...non-information content, sequentially assigned, record counter. I'm sure it doesn't mean what I think it does as I've found sequential counters to be an invaluable tool in a number of auditing scenarios. However, it's always possible I could use a good flogging. :-) Bill -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Clifton Oliver Sent: Wednesday, June 15, 2005 10:49 PM To: u2-users@listserver.u2ug.org Subject: Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... I just can't keep quiet any more. Any auditor who depends on a non-information content, sequentially assigned, record counters as an audit trail ought to be flogged and sent back to Auditing For Dummies. If your auditor demands that, I'd suspect they are a paper-based bean counter, not an Information Systems Auditor. -- Regards, Clif bscribe please visit http://listserver.u2ug.org/ -- -- Drew Henderson There are two types of people - Dir. for Computer Center Operations those who do the work and those [EMAIL PROTECTED] who take the credit. Try to be in the first group, there is 110 Ginger Hall less competition. Morehead State University Indira Ghandi Morehead, KY 40351 Phone: 606/783-2445 Fax: 606/783-5078 -- --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Well, you raise a point, but not the one I think you intend. Transactions should be designed to be active for the minimum span of time to avoid the bottleneck mentioned. I would think very carefully about any transaction the cached a large group of updates for a long period of time, say all postings of a select list of transaction. Instead I would put the transaction inside the loop unless there was a very good reason to be able to back out the entire batch of selected updates. In either case the assignment of a sequential id from a control record is, in and of itself, a data change that should be cached. Rich Taylor | Senior Programmer/Analyst| VERTIS 250 W. Pratt Street | Baltimore, MD 21201 P 410.361.8688 | F 410.528.0319 [EMAIL PROTECTED] | http://www.vertisinc.com Vertis is the premier provider of targeted advertising, media, and marketing services that drive consumers to marketers more effectively. The more they complicate the plumbing the easier it is to stop up the drain - Montgomery Scott NCC-1701 -Original Message- From: [EMAIL PROTECTED] [mailto:owner-u2- [EMAIL PROTECTED] On Behalf Of Stevenson, Charles Sent: Sunday, June 12, 2005 4:26 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... Charlie, I don't see how that addresses the TRANSACTION problem. If the call to your NEXT.AVAIL.ID routine happens after TRANSACTION START, the id control item will not be updated nor will its lock be released until TRANSACTION COMMIT (or rollback) is executed, even though the NEXT.AVAIL.ID routine says to write and release it. This is a potential bottleneck if other concurrent processes are wanting to do the same, waiting for that control item. cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
If more people care, they whould weigh in. Clif moved it into the U2UG Enhancement committee's queue. I'm on that committee so I'll take it up there, but the more voices the better. -Original Message- From: Bill Haskett Charles: No, you guys aren't the only ones. This is a pretty miserable hole in the mvDbms functionality. However, since we've been writing this stuff for years the routines are already built to solve our problems. I just didn't know TRANSACTION bracketing was going to break them. Unknowns like this are the reason we haven't implemented transactions, as we haven't been eager to rework some core functions in our application. Bill Would this be a worthwhile enhancement request through U2UG? Are Ken and I the only ones whining about this: --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
I'm reading through all these responses and probably won't respond to each individually. But I do thank you all for your input. There are several suggestions like the one below that involve incrementing the control record BEFORE the transaction starts. Either grab a block, or grab a master number and start incrementing sub-numbers as needed. Maybe go back fix the control record after transaction is ROLLBACKed or otherwise doesn't go as expected. I like the '*':SUB.KEY idea below. Ken Wallis had a similar method. This approach of claiming the sequential-id key (or block of keys) before the transaction starts assumes one has a fair idea of what will be needed before TRANSACTION START is executed. But one may not know that until deep into processing. Or - even better! - maybe your code is modular enough that these kinds of updates are going to happen inside blackbox external utility subroutines, where the outer process does not know or care about the kind of updates that might happen inside those calls. -Original Message- From: Bob Woodward But his comments have generated a modification idea to my earlier suggestion. If, before the TRANSACTION START command, you get a next available ID then use that as a root value, you can then create a sequential number with an internal sequential number. IE: ROOT.KEY='' LOOP UNTIL ROOT.KEY RELEASE ;* make sure all locks are released READU ROOT.KEY FROM CF, NEXT.ROOT.KEY THEN READ JUNK FROM OUTFILE, ROOT.KEY:*1 THEN WRITE ROOT.KEY+1 ON CF, NEXT.ROOT.KEY ROOT.KEY='' END END REPEAT WRITE ROOT.KEY+1 ON CF, NEXT.ROOT.KEY TRANSACTION START SUB.KEY = 1 EXIT.FLAG='' LOOP OUTRCD = '' [do whatever builds OUTRCD] WRITE OUTRCD ON OUTFILE, ROOT.KEY:*:SUB.KEY SUB.KEY += 1 [whatever to determine if time to exit] UNTIL EXIT.FLAG REPEAT TRANSACTION COMMIT ;* or whatever logic to rollback. This is actually a modification of another suggestion to lock a block of numbers, too. Difference is a block of numbers would be a maximum number of records that could be created, like 123400 to 123499 if blocking to a factor of 100. With a multi-part key, using the asterisk, you no longer have the maximum number block. It could go 10, 100, or even 10,000 if need be. You could even use something like ROOT.KEY:*0 if you like as a sort of header record to keep track of how many records there should be for the ROOT.KEY family. This should keep the auditors happy, Transactions sequential, and cross-transaction locking to an extremely short interval. Also, by using READU without the LOCKED clause, the program will wait that short interval until the record is available. As a side comment, this method will also make it easy to keep records in a sorted, associated order. You could even get a count of process runs by selecting @ID = [*1 or SUB.KEY = 0 if you set the dict record. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Missing sequential numbers isn't important to me, but it's a good point for general discussion. cds From: Bruce Nichol This is all well and good if the commit goes ahead, but if rollback is the action, don't you lose a supposedly sequential root key into the vapours? Never to be seen again? That'd make an auditor go spare. 61,62,63,65,66... Hang on! Where's 64? Stop the presses! Everybody down and look for 64... This is actually a modification of another suggestion to lock a block of numbers, too. and From: Steve Johnson But this would not keep those pesky auditors happy. In the following sequence, how could they know that item 1*4 was missing? 1*1 1*2 1*3 2*1 etc. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Ah-HA! Some real world transaction experience! Just what I wanted, Les. From: Les Hewkin Start trasnactions Readu control record then record.ID = control record write record control record += 1 write control record end end transaction This seems to work for us. We do have loads of control records for generating ID and we never have any missing numbers. And for really big 'trasnactions', where there is a lot of other writes mixed in, having the control record locked for the duration of the transaction is not a bottleneck for you? That was my main concern. cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Would this be a worthwhile enhancement request through U2UG? Are Ken and I the only ones whining about this: From: [EMAIL PROTECTED] Ideally of course, the ID would be allocated by the DBMS transparently when the record is inserted into the file, but neither U2 product supports that concept as far as I know I don't know why it isn't already there. (perhaps you could use a trigger, but ...). h... so the next best would be simply to move the ID allocation to be the very last step in the transaction. Process everything under a dummy key, unless this new foreign key is needed to be stored in another file. then when all is ready and complete, do the ID allocation. If using explicit TRANSACTION boundaries then do this as the last IO inside the transaction and the issue of locks being held until the COMMIT/ROLLBACK should cease to be significant. unless modular coding (which is generally a good idea) has these buried in external blackbox subroutines. cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
From: Brian Leach ...Windows service app) in Delphi... Mutex wrapper... thread safe. ... Socket API or SOAP API... smart aleck. Seriously, if you are going to use transactions I would guess the only way to avoid maintaining the key for the duration of the transaction (to go back to the original post) would be to take it out of the transaction space I don't like it, but it's better than your previous idea. This seems to be an emerging theme: From: [EMAIL PROTECTED] Could you call NEXT.AVAIL.ID before TRANSACTION START, and again after the transaction rollback, assuming you can detect if the transaction was rolled back? If the transaction committed, then no further action would be required. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
This would be a very worthwhile enhancement IMO. SQL Server calls this an IDENTITY column, and, IIRC, it can be any column (aka attribute), not only the primary key. That is also a useful feature when the record key contains useful data but you still need to have sequentially assigned numbers for SOX or some other reason. I'm thinking of an additional keyword for CREATE.FILE like IDENTITY n where n attribute number in which to place the sequential number, n=0 means the key and this is the default. There are some implementation wrinkles to work out such as the syntax of WRITE in basic when the OS will assign the key value; what happens if a program changes the value of the IDENTITY attribute; and so on. Steve Johnson FXA Group Ltd Bangkok Original message from Stevenson, Charles on 6/16/2005 10:52 AM: Would this be a worthwhile enhancement request through U2UG? Are Ken and I the only ones whining about this: From: [EMAIL PROTECTED] Ideally of course, the ID would be allocated by the DBMS transparently when the record is inserted into the file, but neither U2 product supports that concept as far as I know snip --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
I just can't keep quiet any more. Any auditor who depends on a non-information content, sequentially assigned, record counters as an audit trail ought to be flogged and sent back to Auditing For Dummies. If your auditor demands that, I'd suspect they are a paper-based bean counter, not an Information Systems Auditor. -- Regards, Clif Member ISACA www.isaca.org ~~~ W. Clifton Oliver, CCP CLIFTON OLIVER ASSOCIATES Tel: +1 619 460 5678Web: www.oliver.com ~~~ On Jun 15, 2005, at 8:47 PM, Stevenson, Charles wrote: Missing sequential numbers isn't important to me, but it's a good point for general discussion. cds From: Bruce Nichol This is all well and good if the commit goes ahead, but if rollback is the action, don't you lose a supposedly sequential root key into the vapours? Never to be seen again? That'd make an auditor go spare. 61,62,63,65,66... Hang on! Where's 64? Stop the presses! Everybody down and look for 64... --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
What was the question? If I remember correctly it was about the best way to allocate sequential numbers for record ID's? Yes? Well here goes Start trasnactions Readu control record then record.ID = control record write record control record += 1 write control record end end transaction This seems to work for us. We do have loads of control records for generating ID and we never have any missing numbers. We have an internal audit department that goes around checking these things. Just had a quick check and there are 3140 users logged on to one of our systems and we don't have locking problems. Nice, neat and simple. If anything does go wrong then it is due to a major system problem, but we don't have them anymore!!! Les -Original Message- From: Bruce Nichol [mailto:[EMAIL PROTECTED] Sent: 14 June 2005 05:04 To: u2-users@listserver.u2ug.org Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... Goo'day, Bob, At 19:44 13/06/05 -0700, you wrote: Come on, people. A request was made for options. I guess I'm one of those to whom you are referring. Please accept my abject apologies. However, in my defence, and in defence of those others, I'll add that the discourse started with options and then evolved in to SOX and auditors. Your offering came after that, so my (and, I assume, others) response was geared towards the latest level of the discussion. snip Now if you have something better, offer it up! The original poster wants options, so lend a hand instead of a finger. BobW -Original Message- From: [EMAIL PROTECTED] [mailto:owner-u2- [EMAIL PROTECTED] On Behalf Of Steve Johnson Sent: Monday, June 13, 2005 4:29 PM To: U2UG Subject: Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... But this would not keep those pesky auditors happy. In the following sequence, how could they know that item 1*4 was missing? 1*1 1*2 1*3 2*1 etc. Regards, Steve Johnson FXA Group Ltd Bangkok [snip] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.0 - Release Date: 13/06/05 -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.0 - Release Date: 13/06/05 Regards, Bruce Nichol Talon Computer Services ALBURYNSW 2640 Australia http://www.taloncs.com.au Tel: +61 (0)411149636 Fax: +61 (0)260232119 If it ain't broke, fix it till it is! -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.0 - Release Date: 13/06/05 --- u2-users mailing list u2-users@listserver.u2ug.org 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 u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
-Original Message- Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... Come on, people. A request was made for options... Okay so here goes: Write a service application (e.g. Windows service app) in Delphi that maintains a table of sequence numbers. Ensure you use a critical section (semaphore or Mutex wrapper) to make the incrementing routine thread safe. Use the Socket API or SOAP API to request the next number from the service. Have a second routine to place a number back on the stack in the event of a rollback. .. Seriously, if you are going to use transactions I would guess the only way to avoid maintaining the key for the duration of the transaction (to go back to the original post) would be to take it out of the transaction space e.g. outside the database and provide the sort of pop/push sequencing already discussed in this thread. But then that might be overcomplicating matters.. :-) Brian --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Is the audit trail allowed to keep a sorted list of rollback numbers? Open a process Request a number ... If rollback Insert the number into the Rollback List Else save record Close process Or if the goal is to consume all sequential numbers. Open a process Request the lowest number from the Rollback List If none then Request a number ... If rollback Insert the number into the Rollback List Else save record Close process When audited, show the completed items and the rollback list for a complete set of numbers. David Morelli, UIS/Datatel Team -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Brian Leach Sent: Tuesday, June 14, 2005 2:43 AM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... -Original Message- Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... Come on, people. A request was made for options... Okay so here goes: Write a service application (e.g. Windows service app) in Delphi that maintains a table of sequence numbers. Ensure you use a critical section (semaphore or Mutex wrapper) to make the incrementing routine thread safe. Use the Socket API or SOAP API to request the next number from the service. Have a second routine to place a number back on the stack in the event of a rollback. .. Seriously, if you are going to use transactions I would guess the only way to avoid maintaining the key for the duration of the transaction (to go back to the original post) would be to take it out of the transaction space e.g. outside the database and provide the sort of pop/push sequencing already discussed in this thread. But then that might be overcomplicating matters.. :-) Brian --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
But his comments have generated a modification idea to my earlier suggestion. If, before the TRANSACTION START command, you get a next available ID then use that as a root value, you can then create a sequential number with an internal sequential number. IE: ROOT.KEY='' LOOP UNTIL ROOT.KEY RELEASE ;* make sure all locks are released READU ROOT.KEY FROM CF, NEXT.ROOT.KEY THEN READ JUNK FROM OUTFILE, ROOT.KEY:*1 THEN WRITE ROOT.KEY+1 ON CF, NEXT.ROOT.KEY ROOT.KEY='' END END REPEAT WRITE ROOT.KEY+1 ON CF, NEXT.ROOT.KEY TRANSACTION START SUB.KEY = 1 EXIT.FLAG='' LOOP OUTRCD = '' [do whatever builds OUTRCD] WRITE OUTRCD ON OUTFILE, ROOT.KEY:*:SUB.KEY SUB.KEY += 1 [whatever to determine if time to exit] UNTIL EXIT.FLAG REPEAT TRANSACTION COMMIT ;* or whatever logic to rollback. This is actually a modification of another suggestion to lock a block of numbers, too. Difference is a block of numbers would be a maximum number of records that could be created, like 123400 to 123499 if blocking to a factor of 100. With a multi-part key, using the asterisk, you no longer have the maximum number block. It could go 10, 100, or even 10,000 if need be. You could even use something like ROOT.KEY:*0 if you like as a sort of header record to keep track of how many records there should be for the ROOT.KEY family. This should keep the auditors happy, Transactions sequential, and cross-transaction locking to an extremely short interval. Also, by using READU without the LOCKED clause, the program will wait that short interval until the record is available. As a side comment, this method will also make it easy to keep records in a sorted, associated order. You could even get a count of process runs by selecting @ID = [*1 or SUB.KEY = 0 if you set the dict record. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Stevenson, Charles Sent: Sunday, June 12, 2005 1:26 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... Charlie, I don't see how that addresses the TRANSACTION problem. If the call to your NEXT.AVAIL.ID routine happens after TRANSACTION START, the id control item will not be updated nor will its lock be released until TRANSACTION COMMIT (or rollback) is executed, even though the NEXT.AVAIL.ID routine says to write and release it. This is a potential bottleneck if other concurrent processes are wanting to do the same, waiting for that control item. cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
But this would not keep those pesky auditors happy. In the following sequence, how could they know that item 1*4 was missing? 1*1 1*2 1*3 2*1 etc. Regards, Steve Johnson FXA Group Ltd Bangkok Original message from Bob Woodward on 6/14/2005 12:39 AM: But his comments have generated a modification idea to my earlier suggestion. If, before the TRANSACTION START command, you get a next available ID then use that as a root value, you can then create a sequential number with an internal sequential number. IE: ROOT.KEY='' LOOP UNTIL ROOT.KEY RELEASE ;* make sure all locks are released READU ROOT.KEY FROM CF, NEXT.ROOT.KEY THEN READ JUNK FROM OUTFILE, ROOT.KEY:*1 THEN WRITE ROOT.KEY+1 ON CF, NEXT.ROOT.KEY ROOT.KEY='' END END REPEAT WRITE ROOT.KEY+1 ON CF, NEXT.ROOT.KEY TRANSACTION START SUB.KEY = 1 EXIT.FLAG='' LOOP OUTRCD = '' [do whatever builds OUTRCD] WRITE OUTRCD ON OUTFILE, ROOT.KEY:*:SUB.KEY SUB.KEY += 1 [whatever to determine if time to exit] UNTIL EXIT.FLAG REPEAT TRANSACTION COMMIT ;* or whatever logic to rollback. This is actually a modification of another suggestion to lock a block of numbers, too. Difference is a block of numbers would be a maximum number of records that could be created, like 123400 to 123499 if blocking to a factor of 100. With a multi-part key, using the asterisk, you no longer have the maximum number block. It could go 10, 100, or even 10,000 if need be. You could even use something like ROOT.KEY:*0 if you like as a sort of header record to keep track of how many records there should be for the ROOT.KEY family. This should keep the auditors happy, Transactions sequential, and cross-transaction locking to an extremely short interval. Also, by using READU without the LOCKED clause, the program will wait that short interval until the record is available. As a side comment, this method will also make it easy to keep records in a sorted, associated order. You could even get a count of process runs by selecting @ID = [*1 or SUB.KEY = 0 if you set the dict record. --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
You're right, it would. Depending on the requirements, you could very easily create a log file of just the ROOT.KEY values that were rolled back and force an entry of a reason code, time/date stamp, user information, all kinds of stuff, but I think this solution gives the original requestor an option to consider for whatever their needs are, which is all I was attempting to do, not a finished product. Without keeping the lock inside the Start/Commit/Rollback, you're either going to have to keep track of numbers skipped, or a log of why they were skipped. That's a given if one of the requirements is to account for all key values. What more can you expect than a synopsis in this forum? After all, it is expert advice, and sometimes a lot of prewritten programming, for free. BobW -Original Message- From: [EMAIL PROTECTED] [mailto:owner-u2- [EMAIL PROTECTED] On Behalf Of Bruce Nichol Sent: Monday, June 13, 2005 3:54 PM To: u2-users@listserver.u2ug.org Subject: RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... [snip] This is all well and good if the commit goes ahead, but if rollback is the action, don't you lose a supposedly sequential root key into the vapours? Never to be seen again? That'd make an auditor go spare. 61,62,63,65,66... Hang on! Where's 64? Stop the presses! Everybody down and look for 64... --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Come on, people. A request was made for options. I guess I wasn't real clear on the use of the *0 possibility. Use 1*0 to store the number of sub key parts, or use just a record with the main key part and no subpart. The task was how to not hold up everyone else with a sequential key counter record. There are a bunch of ways to satisfy the bean counters. You just have to find one that is going to make them happy! KISS. The auditors want a way of knowing what happened. They want someone who is accountable and can say they know this is the way things work. Provide them with that, CYA, and document it. Now if you have something better, offer it up! The original poster wants options, so lend a hand instead of a finger. BobW -Original Message- From: [EMAIL PROTECTED] [mailto:owner-u2- [EMAIL PROTECTED] On Behalf Of Steve Johnson Sent: Monday, June 13, 2005 4:29 PM To: U2UG Subject: Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... But this would not keep those pesky auditors happy. In the following sequence, how could they know that item 1*4 was missing? 1*1 1*2 1*3 2*1 etc. Regards, Steve Johnson FXA Group Ltd Bangkok [snip] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Goo'day, Bob, At 19:44 13/06/05 -0700, you wrote: Come on, people. A request was made for options. I guess I'm one of those to whom you are referring. Please accept my abject apologies. However, in my defence, and in defence of those others, I'll add that the discourse started with options and then evolved in to SOX and auditors. Your offering came after that, so my (and, I assume, others) response was geared towards the latest level of the discussion. snip Now if you have something better, offer it up! The original poster wants options, so lend a hand instead of a finger. BobW -Original Message- From: [EMAIL PROTECTED] [mailto:owner-u2- [EMAIL PROTECTED] On Behalf Of Steve Johnson Sent: Monday, June 13, 2005 4:29 PM To: U2UG Subject: Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO... But this would not keep those pesky auditors happy. In the following sequence, how could they know that item 1*4 was missing? 1*1 1*2 1*3 2*1 etc. Regards, Steve Johnson FXA Group Ltd Bangkok [snip] --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.0 - Release Date: 13/06/05 -- No virus found in this incoming message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.0 - Release Date: 13/06/05 Regards, Bruce Nichol Talon Computer Services ALBURYNSW 2640 Australia http://www.taloncs.com.au Tel: +61 (0)411149636 Fax: +61 (0)260232119 If it ain't broke, fix it till it is! -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.0 - Release Date: 13/06/05 --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
Re: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Chuck, The method I use may work for you, and not waste IDs (accountants hate missing sequential numbers). Call a NEXT.AVAIL.ID routine passing a GET code which reads and locks the ID control item pops an ID off the unused ID stack (explained below) or picks up the next ID and increments it in the control item writes the control item (the ID is now yours only) passes back the ID Verify the ID is not already on file if it is, repeat until you have a good ID Do your thing If the transaction is successful, you're done If not, call the NEXT.AVAIL.ID routine with a PUT code which reads and locks the control item pushes the returned ID onto the unused ID stack writes the control item You're done Regards, Charlie Noah Inland Truck Parts In a message dated 6/10/2005 7:44:58 PM Central Standard Time, [EMAIL PROTECTED] writes: Well, there's no law that says that you can't waste id's. Get the next id update before entering the transaction, I'd say. Or, if your transactions are in batches, get the id, add a hundred(or some other arbitrary but large enough number), and use all of those sequentially in this process. Here's one I encountered recently that I don't recommend: randomly generate each of 8 integers, then read the file to see if it already exists. If it does, generate 8 more... there were times where it took 20 minutes to find an unused one. The idea was to avoid this kind of bottleneck, but once files started to contain millions of records, there was a problem. Solution? prefix with a julian date... Our greatest duty in this life is to help others. And please, if you can't help them, could you at least not hurt them? - H.H. the Dalai Lama When buying selling are controlled by legislation, the first thing to be bought sold are the legislators - P.J. O'Rourke Dan Fitzgerald From: Stevenson, Charles [EMAIL PROTECTED] Reply-To: u2-users@listserver.u2ug.org To: u2-users@listserver.u2ug.org Subject: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/ROLLBACK Date: Fri, 10 Jun 2005 18:39:30 -0400 For files with sequential numbers as record IDs, it is common practice to use a control item (in dictionary or in a special control table) to get the next sequential id counter, then write back the incremented counter ASAP so it is available for the next process that needs to do the same. Some variation on this theme (probably involving a utility subroutine): READVU SEQ.ID FROM ctrl.fvar, ctrl.id, n ... WRITEV SEQ.ID+1 TO ctrl.fvar, ctrl.id, n WRITE NEW.REC TO fvar, SEQ.ID === BUT: === If that happens inside an explicit transaction bounded by TRANSACTION START and TRANSACTION COMMIT (or TRANSACTION ROLLBACK), the Sequential ID control record will not actually be written and/or released until all updates are done/ditched during commit/rollback. If the transaction is extensive and involves many updates, this could be a serious bottleneck. Making sure that you read the next sequential id as close to the moment of executing the commit would be helpful, but that is not always an option in complicated transactions. What is the best practice for handling this? What is your experience? Bottlenecks? Ideally, I know what I'd prefer: I wish U2 would handle sequential ids internally for variants of Type-2 or dynamic SEQ.NUM files. That should have happened a couple decades ago in PI and Pick. But back to the real world. What is the best way to handle sequential IDs when using explicit TRANSACTIONs? cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/ --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
RE: [U2] Best practice for Sequential IDs using TRANSACTION START COMMIT/RO...
Charlie, I don't see how that addresses the TRANSACTION problem. If the call to your NEXT.AVAIL.ID routine happens after TRANSACTION START, the id control item will not be updated nor will its lock be released until TRANSACTION COMMIT (or rollback) is executed, even though the NEXT.AVAIL.ID routine says to write and release it. This is a potential bottleneck if other concurrent processes are wanting to do the same, waiting for that control item. cds --- u2-users mailing list u2-users@listserver.u2ug.org To unsubscribe please visit http://listserver.u2ug.org/
