Advice required about RDBMS & locking

[SpenBabe]

We have recently converted some MFC-based / ADO Visual C++ programs that used Transoft U/SQL middleware to get at the data, which was fine because it allowed pessimistic locking.
We are now "playing around" with RDMS, such as InterBase & MS-SQL, which do not support pessimistic locking.

I understand that we can use BeginTrans/Rollback/Commit around the updates & catch exceptions on the updates if someone else has already updated the record. But this software has 27+ pages of user input & 40+ updates on related files to do when user clicks 'Save'. (The time between beginning a tran & committing it could be several hours!)

As short term thing, we have created an extra table that holds the user, the table & the record key & are using this to block access to 40 associated records. Obviously, crashes leave records in this file & it is up to someone to "clean up". (Not good in 200+ user system)

I was wondering how anyone else may have tackled this in the past. Spencer Window (not a joke name)

 

[DLanouette]

First, it shouldn't take hours to update your tables - even if it is 40 tables. Are there some COMPLEX triggers or other things running at the same time? How long did it take in MFC?

Next, you could issue table locks, but with 200 concurrent users, it would take hours to commit the trans.

I'd recommend doing all the updates/adds via a sp. Take all the data as parameters and start a trans, add the data, commit the trans. The trans will increase preformance, not hurt it (if its done right)

But, the biggest thing I'd look at is having the user fill out 27 pages of info without saving any of it. try breaking the info into logical groups and saving it in steps. That way, you have smaller transactions and if something happens (and it always does the user could pickup where the left off.

HTH


______________________________
- David Lanouette
- Lanouette Consulting, LLC
- DLanouette@Computer.org