ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Evo-ERP and DBA Classic >> Suggestions for Updates >> Better database concurrency
http://www.istechforum.com/YaBB.pl?num=1169050396

Message started by kkmfg on 01/17/07 at 08:13:16

Title: Better database concurrency
Post by kkmfg on 01/17/07 at 08:13:16

This would probably require a change away from my "favorite" database server (Pervasive) but I'm a glutton for punishment so I'll try anyway:

It's annoying that changing defaults tends to get denied because there are other users logged into the company. I really don't care who is logged into the company, I'd like to change the settings. These settings should immediately take effect for all new operations. It shouldn't be that hard to make it change the settings on the fly and have all programs run after that point use the new settings.

Title: Re: Better database concurrency
Post by kevind on 01/17/07 at 09:28:31

I believe most of these denials are coded in the tas applications to protect
us from ourselves.

Having said that, I would like an Administrative option to turn off those
denials so that I can change things that I know are not effected by others
logged in.

Title: Re: Better database concurrency
Post by kkmfg on 01/17/07 at 10:56:49

That is probably true but changing most of the defaults will not cause any havoc for the logged in users. You can't even change the message to print on the bottom of sales orders while someone is logged in. That's only used by the sales order printing program and could EASILY be changed on the fly.

In reality I suppose it's not a huge deal but it is annoying and totally unnecessary.

Title: Re: Better database concurrency
Post by NovaZyg on 01/17/07 at 10:59:21

Bottom line, it is poor design. all the settings (DBA's that is) are located in two files. BKSYMSTR and BKYSMSTR.. and almost every program opens these files. So when you are trying to make a change to settings, if you lock the one and only record in the file, then everyone else would be locked out... this is the file contains info like next SO# WO# PO# check # etc... not something you want to mess with. That being said, a savvy user could look at the DBA source code and figure out what field it is they need to change and use maintain database to do so. but be quick, as that will also lock the record for as long as you have the file open.

Title: Re: Better database concurrency
Post by kkmfg on 01/17/07 at 11:09:38

Hmm, well at least that makes sense. Let me guess, PervasiveSQL doesn't support updating a record that is locked... Like even PostgreSQL will let you do. And that is free. (I believe in Postgre that is called MVCC).

Is it possible to seperate all of the settings into different rows and use row level locks so that only users that are really affected can block you? Maybe it's not worth the time to do that though... :(

Title: Re: Better database concurrency
Post by kevind on 01/22/07 at 12:26:53

Allen,
Are you saying that it would not be possible to show
the current settings, allow a change to the field on the screen,
re-read the record with a lock, apply the change and write the
new record back? (quick read-modify-write)

Title: Re: Better database concurrency
Post by NovaZyg on 01/22/07 at 13:01:35

Kevin,

That is JIT locking (Just In Time) and we have done that to a number of places in the system already. That is how I have fixed most of the PO, SO, and Inventory locks. BUT I would not do that with these files, as all WO# and SO# , next checking numbers etc.. come from these files.. Lynn and I have talked about moving those numbers out of the BKSYMSTR table and into their own tables, but that is a big undertaking, for very little gain. We may still go there, it all depends on you the users, if you get enough support for this, then we will do it.

Title: Re: Better database concurrency
Post by kevind on 01/22/07 at 13:13:18

Allen,
... and we thank you for the JIT Locking!!!!!!
( http://www.istechforum.com/cgi-bin/YaBB.pl?board=General;action=display;num=1101845646;start=2#2 )

I understand and appreciate any efforts to keep people from screwing things up.

As the system Administrator, I would like the ability to override the protection on the settings stored in these two files.