Record locking - Invoicenumbers

[tweetie]

I have use a variation on Jan's method. And tranformed it into a global method, becoz we have 4 counters, on different tables. menu_to_menu is a table with only 1 record always.
But some of our client still complain about double numbers.

Code: Select all

var deTeller = arguments[0];
if (deTeller!='dos' && deTeller!='of' && deTeller!='nota' && deTeller!='roy'){
   return false;
}
var aa = {'dos':'dossier nummer','roy':'royementnummer','nota':'notanummer','of':'offertenummer'};
var bb = {'dos':'dossierlaatstenummer','roy':'royaktenlaatstenummer','nota':'factuurlaatstenummer','of':'offertelaatstenummer'};
var theCounter = 1
while(!databaseManager.acquireLock(menu_to_menu,-1))
{
   application.sleep(500)
   theCounter++;
   if(theCounter>60) // stop if 60 times you attempted to lock
   {
      globals.messageWarning('Het is niet gelukt om '+aa[deTeller]+' op te hogen!\nNeem kontakt op met Devoon','OK',null);
      return false;
   }
}
var lastNr = eval('menu_to_menu.'+bb[deTeller]);
eval('menu_to_menu.'+bb[deTeller]+'+=1');
controller.saveData();
databaseManager.releaseAllLocks();
return lastNr;

We have client that complain about double numbers. I have checked if people are messing with the counters, but according to the logfile, they didn't. So somehow this method is failing somewhere.

[Jan Blok]

a variation? "theCounter" is something which does not come from the reocrds you are locking, so this seems a faulty implementation

[tweetie]

a variation? "theCounter" is something which does not come from the reocrds you are locking, so this seems a faulty implementation

theCounter only counts the attemps of the locking mechanism, if it exceeds 60, it will stop. It doesn't do anything else

[Jan Blok]

you are right, I over looked..., but are you sure that all clients are looking at the same records in menu_to_menu related foundset?
BTW why are you locks for sequences? its far more easy to make an sequence table with columns having sequences and use databaseManager.getNextSequence(...) which has far less overhead, all locking is done by Servoy server internally then

[tweetie]

you are right, I over looked..., but are you sure that all clients are looking at the same records in menu_to_menu related foundset?

Actually the menu table only has 1 record, menu_to_menu is a bit overkill

BTW why are you locks for sequences? its far more easy to make an sequence table with columns having sequences and use databaseManager.getNextSequence(...) which has far less overhead, all locking is done by Servoy server internally then

I am not familiar databaseManager.getNextSequence yet...and I saw the method of Jan Aleman a while ago...and implemented it

[Jan Blok]

We fail to explain why double keys could exist, are you sure there is only one servoy server running and no other processes ever acces the data in the table? (assuming you understand that connecting with a servoy developer or test servoy server on same database is an other processes)

[tweetie]

We fail to explain why double keys could exist, are you sure there is only one servoy server running and no other processes ever acces the data in the table? (assuming you understand that connecting with a servoy developer or test servoy server on same database is an other processes)

Yes there is only 1 servoy server running...
I am gonna look into the databaseManager.getNextSequence(...) trick.
It has happened again...between 2 new records there was a time-lapse of 0,2 seconds.

[Harjo]

we sometimes have this issue also, having double invoicenumbers with this mechanism.
(we now have a double-check that checks afterwards, if the invoicenumber is unqiue, if not, we show a warning and stop the method.)

customers do report, that they sometimes see this warning.

I don't think it's the record locking problem, but a broadcasting problem.
sometimes ( I can't reproduce right now) this:

Code: Select all

invoicenumber = invoice_to_invoice_yearnumbers.invoicenumber

is returning the previous value, while another client has already updated the value with 1.

if the broadcasting fails, for whatever reason, could this be the case? does Servoy always re-query this:

Code: Select all

invoicenumber = invoice_to_invoice_yearnumbers.invoicenumber

?

I will try if databaseManager.refreshFromDatabase will tackle this.....

[tweetie]

I also believe it's broadcastproblem.
I have checked the log (becoz it recently happened again).
There are 2 times that 2063761 is changed into 2063762...but there is a period of 45 minutes in between them.
You would also expect somewhere '2063762' is changed into '2063761'' to explain the last 2 rows....there isn't

log_id,server_name,table_name,column_name,log_action,pk_data,old_data,new_data,event_time,user_name
5355320,'assyst','menu','factuurlaatstenummer',3,'1.1;','2063763','2063764','2009-03-17 14:02:35.896','1136'
5355263,'assyst','menu','factuurlaatstenummer',3,'1.1;','2063762','2063763','2009-03-17 14:01:10.336','1136'
5355009,'assyst','menu','factuurlaatstenummer',3,'1.1;','2063761','2063762','2009-03-17 13:47:32.963','1120'
5354454,'assyst','menu','factuurlaatstenummer',3,'1.1;','2063761','2063762','2009-03-17 13:02:13.766','1102'

[tweetie]

Last Friday I was at Servoy.
They told me that it would be fixed in the next version.
It had something to do that the foundset in Servoy is not updated correctly from the database...in some cases...

He also told me that databaseManager.refreshRecordFromDatabase should do the trick....after acquiring the lock