Synchronize simultaneous client calls to azure mobile service

Question

I have a basic question about simultaneous client calls to the azure mobile service functions. How to prevent that multiple clients/users read and update variables and db tables at the same time?

I want to build a leaderboard with AZURE Mobile Service, and for the client/game code i am using Marmalade SDK. I have created a db table called "highscore" for the service, where "track_id" is the primary key. The db table is empty when i start the test.

On the client side:

From client i make "HTTP post" calls to my AZURE Mobile Service The call contains data for "TrackId" and "Score". I simulate multiple clients by making several calls one after eachother.

Pseudocode:

Http1.Post(trackId = 1, trackscore =100) -> "https://game_name.azure-mobile.net/api/test"
Http2.Post(trackId = 1, trackscore =200) -> "https://game_name.azure-mobile.net/api/test"
Http3.Post(trackId = 1, trackscore =300) -> "https://game_name.azure-mobile.net/api/test"
Http3.Post(trackId = 1, trackscore =400) -> "https://game_name.azure-mobile.net/api/test"

In AZURE Mobile Service code:

  exports.post = function(request, response) {
    var mssql = request.service.mssql;
    var trackId = request.param('trackId');
    var trackscore = request.param('trackscore');

    var sql1 = "SELECT * FROM highscore WHERE track_id = '" + trackId + "'";
    mssql.query(sql1, {
      success: function(results) {
        console.log("Length1:" + results.length + ", score:" + trackscore);
        if (results.length == 0) {
          console.log("Create new score" + ", score:" + trackscore);
          var sql2 = "INSERT INTO highscore (track_id, track_score) VALUES ('" + trackId + "','" + trackScore + "')";
          mssql.query(sql2);
        } else if (results.length == 1) {
          console.log("Update score" + ", score:" + trackscore);
          var sql2 = "UPDATE highscore SET track_score = '" + trackScore + "' WHERE track_id = '" + trackId + "'";
          mssql.query(sql2);
        } else {
          console.log('Error. Number of trackId: %s = %d', trackId, results.length);
        }
        mssql.query(sql1, {
          success: function(results) {
            console.log("Length2:" + results.length + ", score:" + trackscore);
          }
        });
      },
      error: function(err) {
        console.log("Error:" + err);
        response.send(statusCodes.OK, {
          message: err
        });
      }
    });
  };

AZURE LOG:

The "LOGS" for the service shows that all the client calls is inside the server function at the same time. This results in the "ERROR MESSAGE" below, when some client threads try to INSERT an item that already exists.

1.  Information     Length2:1, score:400     api/test.js                          Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
2.  Error        {ERROR MESSAGE}           api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
3.  Information  Length2:1, score:400          api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
4.  Information  Length2:1, score:200          api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
5.  Information  Length2:1, score:300          api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
6.  Error        {ERROR MESSAGE}               api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
7.  Information  Update score, score: 200      api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
8.  Information  Length1:1, score: 200         api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
9.  Information  Create new score, score: 100  api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
10. Information  Length1:0, score: 100         api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
11. Information  Create new score, score:400   api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
12. Information  Length1:0, score 400          api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎03 
13. Information  Create new score, score:300   api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎02 
14. Information  Length1:0, score:300          api/test.js                        Thu Jan 29 2015, ‎21‎:‎37‎:‎02

{ERROR MESSAGE} = "Error occurred executing query: Error: [Microsoft] [SQL Server Native Client 10.0]
                   [SQL Server]Violation of PRIMARY KEY constraint 'PrimaryKey_3e234221-4811-48a7-bc09-2dac9a666d37'.
                   Cannot insert duplicate key in object 'G.Highscore'. The duplicate key value is (1)."

The propblem

The problem is that the different calls is inside the server code at the same time, making sql queries, update and read variables in unpredictable order. How should this be handled?

carlosfigueira carlosfigueira · Accepted Answer · 2015-01-28T23:16:05

Azure mobile services implements optimistic concurrency, in a way that it doesn't use "locks" to prevent concurrency issues, but instead it lets clients send updates to the service, but if another client had updated the same item before, then the last update will fail (with an appropriate response indicating that). If the client receives such "conflict" response, then it can try to handle the conflict (e.g., letting the server win, resending the update with a new version indicating that it wants to override the previous update, let the user choose, etc.).

This post talks about how the service implements optimistic concurrency. This post shows how you can use the managed client SDK to use that feature (at the time it was written the support for other client platforms wasn't there, but they have since been updated).

Synchronize simultaneous client calls to azure mobile service

On the client side:

In AZURE Mobile Service code:

AZURE LOG:

1 Answers