Record locking - Invoicenumbers

Stef · January 16, 2005, 1:52pm

Hi all,

I have an invoicing database.
Every invoice has a serial number, a serial number for each period (year or quarter), for example 2005/16, 2005/17, 2004/3,2004/4

In another database these serialnumbers are stored.
There are 2 fields in this database: period and number

To set the number I tried the following method:

//lock the related serial
var success = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
//if record is locked, loop untill lock is released
while ( !success )
{
var success = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
}
//get the number
invoicenumber = invoice_to_invoice_yearnumbers.invoicenumber
//set the serial+1
invoice_to_invoice_yearnumbers.invoicenumber = invoice_to_invoice_yearnumbers.invoicenumber + 1
controller.saveData()
databaseManager.releaseAllLocks()

This does not work properly, sometimes I get a message “Record is locked”. This message should not appear, the system has to wait until the lock of another user is released and than continue.

Any hints? Is there a much better way?

In Filemaker I used to do it this way:
Set Error Capture [ On ]
Allow User Abort [ Off ]
Loop
Go to Field [ nr ]
Exit Loop If [ Status( CurrentError) = 0 ]
End Loop
Set Field [ nr, nr+1 ]

TIA,
Stef

pbakker · January 17, 2005, 7:52am

I would not do this in Servoy, but create a procedure in the DB that does it.

Paul

patrick · January 17, 2005, 8:30am

Couldn’t you do something with your own query like

select max(invoicenumber) + 1 where period = 2005

After that you could use that value in your invoice and write it back to the yearnumbers?

The problem here is, however, that you need to prevent that two people do the same in exactly that second. So you still have to lock the period…

Stef · January 17, 2005, 9:17am

pbakker:
I would not do this in Servoy, but create a procedure in the DB that does it.

Thanks for the advice, Paul.

You know how to do this in Sybase? Is it waterproof?

TIA,
Stef

patrick · January 17, 2005, 9:34am

Stored procedures are safe, but depend on the database. That’s why I don’t use them. Once your customer insists on using Oracle or whatever, you go through all your procedures…

IT2Be · January 17, 2005, 9:40am

Stef,

What you can do is check if there is a lock or not. If the result is true (there is a lock) you can either report this to the user and let the user decide to try again or you do a loop. The check would look like this:

var success = databaseManager.acquireLock(relation_name, 0);

if (!success) {
	plugins.dialogs.showWarningDialog(bla bla);

	return;
}

var invoicenumber = relation_name.number;

relation_name.number += 1;

databaseManager.releaseAllLocks();

return invoicenumber;

Stef · January 17, 2005, 9:46am

Hi Marcel,

I know! But I believe it’s not very professional to show a message. The system should keep on trying until the record is unlocked.

I have this working at the moment (with message):

var success = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
if ( !success )
{
plugins.dialogs.showErrorDialog( “Error”, “The system is busy at the moment. Please try again.”, “OK”)
}
else
{
invoicenumber = invoice_to_invoice_yearnumbers.invoicenumber
invoice_to_invoice_yearnumbers.invoicenumber = invoice_to_invoice_yearnumbers.invoicenumber + 1
controller.saveData()
}
databaseManager.releaseAllLocks()

But your method is slightly different (RETURN).
Maybe it will work with a sleep? Something like:

var success = databaseManager.acquireLock(relation_name, 0);

if (!success) {
application.sleep(2000);

return;
}

var invoicenumber = relation_name.number;

relation_name.number += 1;

databaseManager.releaseAllLocks();

return invoicenumber;

IT2Be · January 17, 2005, 9:52am

Stef, it should work like you suggest.

The only issue is that this could create a continuous loop.

In your case I would suggest to add a maximum time/number of loops this method performs…

Stef · January 17, 2005, 10:09am

No, it does not work properly.
Sometimes the massage “The record is locked” (=system message) appears.

Breaking my head…

For me this is very important

IT2Be · January 17, 2005, 10:19am

You say that the sytem (not you) reports a locked record in a dialog? If so, that shouldn’t happen anyway I think…

Or do you say you get a message without the record being locked?

Stef · January 17, 2005, 1:03pm

I tried to do it this way:

And it works fine!
Servoy crew, is this “Catholic”???

TIA
Stef

Jan_Blok · January 17, 2005, 1:45pm

Stef:
//lock the related serial
var success = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
//if record is locked, loop untill lock is released
while ( !success )
{
var success = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
}

I think “var” in bold is the problem… you redefine success, seems strange to me
suggestion:

var success = false;
for (var t = 0 ; t < 5 ; t++)  //try max 5 times
{
	success = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1); 
	if (success) 
	{
		break;
	}
	else
	{ 
		application.sleep(2000); 
	} 
}
if (!success) //5 times failed
{ 
	plugins.dialogs.showErrorDialog( "Error", "The system is busy at the moment. Please try again.", "OK") 
	return; 
} 
invoicenumber = invoice_to_invoice_yearnumbers.invoicenumber 
invoice_to_invoice_yearnumbers.invoicenumber += 1; 
controller.saveData() 
databaseManager.releaseAllLocks();

Stef · March 5, 2005, 6:12pm

I thought this worked fine, but it does not! Sometimes we still have duplicates, I get a message “Record is locked”.

This is a very important issue for us, I do not know how to fix it.
Serials are VERY important!

Anyone? Please!

TIA
Stef

Jan_Aleman · March 7, 2005, 2:56pm

Issue confirmed, there appears to be a locking problem in some specific situations, we will release a fix asap.
Additionally here’s a shorter way to get your lock:

var theCounter = 1
while(!databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1))
{
   application.sleep(1000)
   theCounter++
   if(theCounter>60) // stop if 60 times you attempted to lock
   {
      //showdialogcode here that says that it's not going to happen this time
      return
   }
}
invoicenumber = invoice_to_invoice_yearnumbers.invoicenumber
invoice_to_invoice_yearnumbers.invoicenumber += 1;
controller.saveData()
databaseManager.releaseAllLocks();

Stef · March 10, 2005, 7:50pm

Just tested it in a new version: works perfect now!

Thanks to the Servoy Dev Team!

Stef

tweetie · November 9, 2007, 10:53am

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.

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 · November 9, 2007, 11:52am

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

tweetie · November 9, 2007, 11:59am

Jan Blok:
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 · November 9, 2007, 12:14pm

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 · November 9, 2007, 12:28pm

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?

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

Jan Blok:
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

Topic		Replies	Views
record locking issue Classic Servoy	5	2149	March 1, 2011
update servoy sequence programatically Classic Servoy	4	2795	March 25, 2009
aquireLock madness Classic Servoy	5	2614	January 31, 2012
how to loop for condition in a function? Classic Servoy	6	2327	November 29, 2010
3.5.1 Documentation on Record Locks Classic Servoy	8	3766	October 20, 2007

Record locking - Invoicenumbers

Related topics