Re: Transactions and Record Locking Help Needed

> We understand that if we use transactions and "commit" a detail line, we > will > lose ALL locks, including the header lock, which would then allow other > users at data which we do not want them to get at while its being > updated. Yes, a commit frees all locks. You would have to delay the Commit until after the whole transaction has been written (after all, that's what transaction handling is about conceptually). The problem with this is that if you hold locks for too long, you increase the likelihood of someone else getting tripped up on a lock error on something you're holding, which if you're indexing is less than perfect could mean they get tripped up trying to go past your row(s) on their way to another one. If you were using OnLine, you could defer doing any writes to the database until just before you commit, then at least you'd be holding U-locks, not X-locks. But with SE they're all the same so it doesn't make any difference. > Is there any way to not lose that header lock when we commit detail > until we instruct program to do so ? No. Even if you immediately re-acquired the lock after the first commit, you couldn't guarantee that someone wouldn't nip in and grab it in the meantime. > Is there any way to stop other users from updating the same header and > detail data automatically ? You'd have to maintain your own lock table in the application, which would be a pain. Best thing I think is just to leave the transaction open. When you're adding new rows rather than updating existing ones, you're not really interested in holding a lock on anything so you wouldn't have to start the transaction until just before you write & commit. When you can help it, it's best to avoid having transactions that span user input, in order to reduce the chance of unwanted lock contention. akent@cix.compulink.co.uk (Andy Kent) ------------------------------------------------ Freelance Informix Database Specialist, Redland, Bristol, England