record locking issue

Classic Servoy
1

I have a need to generate unique ID’s, it has to fit in with existing data format converted from foxpro. The ID is for character fields that can contain letters and numbers, I can’t use a servoy sequence for it. It’s pretty straightforward - I just keep the last number used in a one-row table, and increment it, but because of multiple users I have to be sure its unique, so I lock it, until I can update it:

function ggentransid(pctransid_type)
{

	var ofsctl = databaseManager.getFoundSet(globals.gcservername,'c6ctl');	
	ofsctl.loadAllRecords();
	ofsctl.setSelectedIndex(1);

	while(!databaseManager.acquireLock(ofsctl,1,'transid_lock'))
	{
		var lnwaittime = Math.floor(Math.random() * 1000) + 1;
		application.sleep(lnwaittime);
	}
	var loctlrec = ofsctl.getRecord(1);

	if (pctransid_type == 'P')
	{	
		loctlrec.c6paytransid += 1;
		databaseManager.saveData(loctlrec);	
		var lctransid = loctlrec.c6paytransid;
		databaseManager.releaseAllLocks('transid_lock');	
	}
	else
	{
		loctlrec.c6chgtransid += 1;
		databaseManager.saveData(loctlrec);			
		var lctransid = loctlrec.c6chgtransid;
		databaseManager.releaseAllLocks('transid_lock');	
	}		
	return lctransid+'' // convert to string
}

It should only be locked a fraction of a second, if its already locked the code waits a random amount of time up to 1 second, and tries again in a loop. But from time-to-time, the record remains locked until it is manually released via the admin page. Anybody see what might cause that, or perhaps has a better way?

2

Could you use a calculation based on another field that is a servoy sequence?

3

One of the problems with servoy sequences is, as far as I know, you cannot manually set the next number to use. I need that capability.

4

Not sure why you would get that problem.

Also, you may want to code in a max amount of time to try anyway. Something like:
var start = new Date()
before you enter the “while loop”, and then in the loop:

//keep looping untile we have a lock, or we've been looping for 2 minutes
while(!databaseManager.acquireLock(ofsctl,1,'transid_lock') && new Date().getTime() - start.getTime() < 120000){
...}

A better approach would be to not use that type of system to hold the next sequence in a table :) Usually in situations like that I would try to come up with a formula using the tenant id or user id. So, obviously, if its just an auto incrementing number, then you can use servoy sequence. If you need something special, then set an onRecordInsert function and come up with a formula concatenating user id with the real id or something.

If you just need auto incrementing value, but want the column to be text, then create another Integer column to be a real servoy sequence, lets call it “id”. Then create another column that is a calculation of type text, that is: return id + “”

5

Yeah, but again, keeping that servoy sequence from resetting to the wrong value is a problem.

6

Michael,

Does your database support db-sequences?
I think they do what you need.

The locking you do should work, if you can reproduce that in a sample (so with a scenario that shows the issue) then file a case in our support system.

Rob