Google Apps Script locking and optimum wait times and the golden ratio

You are probably familiar with the Google Apps Script Lock Service, which is a way of preventing concurrent access to sections of code. It works well, but the problem is that it’s a fairly blunt instrument.

Let’s say that you want to use the same code to deal with multiple resources – say a spreadsheet tab. With lock service, you’d lock all spreadsheet tabs that were accessed by that code.  This is especially a problem if you are using shared libraries which may be accessed by many people accessing many resources.

Named Locks

In Database abstraction with Apps Script I introduced the concept of Named locks. These work by maintaining a cache entry with a particular key that indicates a particular resource (it could apply to anything you want to limit singular access to for which you can come up with a unique key).

They do need to use the Google Apps Script Lock Service of course, since the ownership of a named lock needs to be guaranteed to be singular, but the time for which it holds it is just as long as it takes to write something to cache – <10 ms – and is going to be a lot less disruptive than locking a section of code to all resources to do some kind of operation.

Protecting code is straightforward, with either the Named locks library or a forked copy of your own.

  var result = new cNamedLock.nameLock()
                 .setKey("your resourceKey")
                 .protect("some debug message", function (lock) {

       .. the code you want to protect


Testing it was never that straightforward though, since simulating intensive and concurrent enough multi-user activity is never an easy thing, especially with Apps Script. However in simulating multi-user testing with Google Apps Script I used the technique described in running things in parallel with HTMLservice to launch as many simultaneous testing threads as necessary.

To cut a long story short, the good news is that it all works. A typical test gives me a process timetable that looks like this

This test runs 30 threads in parallel, each using the same library and trying to access 10 different resources at the same time.

Obviously, this is an extreme example, with many threads trying to access the same resource repeatedly and simultaneously, but nevertheless, it does throw up an interesting dilemma – what’s the optimum time to wait before trying to get a lock again if an attempt was unsuccessful. So I did some tests on that too.

The objective here is to get stuff finished as soon as possible and I found a pattern I couldn’t really figure out.

Each set (Lock-0..9) represents a different wait time between attempts. Set 3 (650ms) was always a lot faster than any other. This seemed to be the case regardless of the average processing time which I varied between an average of 1000 and 4000 ms.

To simulate real life, the actual running time is random based on a target, and the minimum wait time also has a small random factor to avoid collisions, but in principle, 650ms seems to be the best wait time.  In the example on the left, each task has an average run time of 1500ms, but you see the same result regardless of the run times.

Performing the test with fixed run times is more articifial, but it produces the same 650ms pattern – although overall run times are longer because everything finishes and need the same resource at the same time.

The Golden ratio

As a pure coincidence,I couldn’t help noticing that the reciprocal of the golden ratio is not far from.65 seconds, so I as an experiment, I ran all the tests again – this time using .618 as the wait time and got slight improvements for them all. So 618 milliseconds it is. I have no idea why but I like the idea.

The code for the test

Here’s the code. It doesn’t do anything except wait for random amounts of time, with that wait being protected by a named lock. If it all works then when I examine the start and finish times, there should be no overlap between activities using the same key. The test runs this a number of times in parallel

function locktest(options,reduceResults) {
  var results = [];
  for (var i = 0; i < options.repeat ; i++) {
    new cNamedLock.NamedLock(undefined, undefined, undefined, options.minWait)
      .setKey (options.keyName)
      .protect('locktest'+options.index, function(lock) {
        var log  = { 
          start:new Date().getTime(),
          sleep: Math.floor(options.sleep * Math.random()),
          index: options.index,
          minWait: options.minWait
        Utilities.sleep(options.sleep * Math.random());
        log.finish = new Date().getTime();
  return results;

The run is controlled by this profile, and consists of a map process (running the test multiple times in parallel), a reduce (combing and sorting the results), and a log (writing the results to a spreadsheet)

function lockProfile() {
  var profile = [];

  // get and process all the messages
  var CHUNKS = 3;
  var SETS = 8;
  var profileLocks = [];
  for (var j =0; j < SETS;j++) {
    for (var i =0; i < CHUNKS;i++ ) {
      profileLocks.push ({
          "name": "Lock-"+j+'-'+i,
            index: i,
            minWait:200 + j*150,
  // next reduce the messages to one
  var profileReduction = [];
    "name": "reduction",
  // finally log the results
  var profileLog = [{
    "name": "LOG",
    "functionName": "logTheResults",
    "skip": false,
    "options": {
      "driver": "cDriverSheet",
      "clear": true,
      "parameters": {
        "siloid": "locker",
        "dbid": "1yTQFdN_O2nFb9obm7AHCTmPKpf5cwAd78uNQJiCcjPk",
        "peanut": "bruce"
  // put it all together
  profile.push (
  return profile;

If you want to read more about this, or join the community please see the desktop liberation site

About brucemcp 225 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.