How do I use LockService properly every time in Google Apps Script?

Question

I am trying to avoid concurrent process with LockService on my project.

In test runs, LockService works for me without any problems.

But when I roll my project out, sometimes LockService doesn't seem to work properly and overwrite spreadsheet values.

I used tryLock() and hasLock() methods to acquire a lock.

function test(array) {
  var lock = LockService.getScriptLock();
  if (lock.tryLock(500)) {
    if (!lock.hasLock()) {
      var message = "Another user is submitting form data. Please try again later.";
      return message;
    }
    var ss = SpreadsheetApp.openById(outputId);
    var sheet = ss.getSheetByName("sheet1");
    var lastRow = sheet.getLastRow() + 1;
    var range = sheet.getRange(lastRow, 1, 1, array[0].length);
    range.setValues(array);
    lock.releaseLock();
  }
  else {
    var message = "Another user is submitting form data. Please try again later.";
    return message;
  }
}

I would like to avoid to set values in the same last row when users simultaneously submit data. But sometimes it seems to overwrite the values in the last row and one of the arrays' values are just gone.

Could you point out my code's error?

Thank you.

Answer 1

There are various behaviors of Lock Service that you should understand.

• tryLock(milliseconds to Wait) and waitLock(milliseconds to Wait) are very similar. One difference is, that waitLock() will throw an error, but tryLock() will not. The implications of this, is how you want your program flow to work. Do you want to catch an error immediately? Do you want no error? (tryLock() doesn't create an error) Do you intentionally want an error? Do you want a fatal error that triggers withFailureHandler() in the client side? Do you want the entire stack to fail? Do you want this function to stop completely, but just this function to fail? Do you want just one line to fail, and the rest of the function to keep running?

In your situation, there is no point in having the remainder of the lines in the function run.

If you use waitLock(milliseconds to Wait) then you should have something to catch the error. Your choices are:

• Catch the error on that line
• Catch an error anywhere in that function
• Don't catch the error in that function

In your case, you wouldn't want to have the remainder of the function run.

A reason for isolating an error to just one line, is to allow the rest of the code to run, because the result from the rest of the code could still provide some useful output to the user. But this isn't your case. Having the remainder of that function run, provides no extra value to the user.

In fact, if any part of your function fails, then it should stop running. For that reason, I would wrap all the lines in the function in a try/catch.

What if the line: var lock = LockService.getScriptLock(); fails?

If that line fails, then there will be no value for lock. If there is no value for lock then you can't release the lock. If var lock = LockService.getScriptLock(); fails, then lock.releaseLock(); will fail.

That's why I use:

LockService.getScriptLock().releaseLock();

That's why I don't use a variable for the lock.

There are two situations where you should release the lock.

• The code completed successfully
• The code failed

If the code fails when a lock was acquired, then you wouldn't want to have the code wait for the time-out to expire before the lock is released. If the expiration isn't very long, then it might not be much of an issue. But if the lock wait time was long, then it would be waiting for no good purpose. If you have a short wait time, then you run the risk of the lock expiring before the code completes in a situation where the servers are running slow. So, I'd give your wait time just a little padding, but nothing extreme. If your lock is released correctly, then having the wait time a little longer than you need shouldn't have any bad effect. If the lock is acquired with a long wait time, but somehow doesn't get released, that's as much of a problem as not getting the lock.

function myLockFunction() {
try{
  //Your entire code inside the try block
  LockService.getScriptLock().waitLock(milliseconds);//Throws exception if fail

}catch(e){
  LockService.getScriptLock().releaseLock();

}
}

Answer 2

The reason why that's happening is the changes in the spreadsheet can take time to be made by the Spreadsheet Service. Your script will not wait for the Spreadsheet Service to commit the changes and could release the lock before this happens. You should call SpreadsheetApp.flush() before you release the lock to make sure all changes you made to the spreadsheet are committed before a new instance of your script is allowed to make changes to your sheet.

Since what you're doing is appending rows to the end of the sheet, you could also use the appendRow() method, as someone else suggested, and you wouldn't need to use the Lock Service. But I thought you'd like to understand what was going on and learn how to use the Lock Service properly together with Spreadsheets since you could need to do it in the future.