Record locking - Invoicenumbers

[Stef]

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]

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

Paul

[patrick]

Couldn't you do something with your own query like

Code: Select all

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]

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]

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]

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:

Code: Select all

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]

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]

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]

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]

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]

I tried to do it this way:

var success = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
if (!success)
{
application.sleep(2000);
}
var successs = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
if (!successs)
{
application.sleep(2000);
}
var successss = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
if (!successss)
{
application.sleep(2000);
}
var successsss = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
if (!successsss)
{
application.sleep(2000);
}
var successssss = databaseManager.acquireLock(invoice_to_invoice_yearnumbers,-1);
if (!successssss)
{
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();


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

TIA
Stef

[Jan Blok]

//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:

Code: Select all

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]

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]

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:

Code: Select all

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]

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

Thanks to the Servoy Dev Team!

Stef