1

I have following code on server side:

let query = `
       BEGIN TRANSACTION FOO_TRAN
       EXEC sp1_update ...,
       EXEC sp2_insert ...,
       EXEC sp3_update ...,
       EXEC sp4_delete ...,
       ...
       COMMIT TRANSACTION FOO_TRAN
       SELECT 1 as [@@@];
    `;
mssql.query(query, params, {
    success:  function (res) {
        if (res && res.length === 1 && res[0]['@@@'] == 1) {
            response.status(200).send({id: request.body.id});
        }
    }, error: (err)=>response.status(500).send(err)
});

Then client immediately requests modified content using provided id.

Problem: old data is returned for ~2-3 seconds. I tried to specify READ UNCOMMITED in subsequent SELECT, but it didn't help - old rows were mixed with new ones.

Silvia Doomra
  • 947
  • 8
  • 16
Ginden
  • 5,149
  • 34
  • 68
  • Race condition problem. Can you try and see if the SQL Server has the correct content and the Mobile Service is still returning old data? That should help us narrow down what the problem is. Joel's solution is my initial suspicion, so now we'll have to rule out what's wrong. – Chris Anderson Jul 26 '15 at 05:03
  • I'm not sure how can I do it in 2-3 seconds. Some tool for this? – Ginden Jul 27 '15 at 08:22
  • So my suspicion is that your client is querying before that call is finished. The server doesn't cache data, so it's down to your client's timing and SQL being updated. – Chris Anderson Jul 27 '15 at 08:38
  • This was my first suspicion but it was proven false. Client asks server after receiving response. – Ginden Jul 27 '15 at 08:56
  • Can you induce some further latency in your transaction? Use the [WAITFOR](https://msdn.microsoft.com/en-us/library/ms187331.aspx) command, for say 30 seconds? How does your application behave? – Chris Anderson Jul 27 '15 at 23:17

1 Answers1

1

To use transactions with Azure Mobile Services you'll want to use the open method on mssql to get a connection which supports transactions. See documentation of open method here. For example:

request.service.mssql.open({

    success: function(connection) {

        //start a transaction
        connection.beginTransaction(function(errTransaction) {

            if (errTransaction) {
                //handle the error and respond error
                connection.close();
                return;
            }

            //define a queryString and queryParams
            connection.query(queryString, queryParams, function(errQuery, results) {

                if (errQuery) {
                    //handle the error and respond error
                    connection.rollback();
                    connection.close();
                    return;
                }

                //success
                connection.commit();
                connection.close();
                //respond OK
            });                                         

        });
    },

    error: function(errOpen) {
        //handle the error
    }

});
Joel
  • 15,654
  • 5
  • 37
  • 60