Node-mssql: SQL deadlocks

[chetanku]

I am using nodejs async module to execute SP's in Sql Server using mssql.
I sometimes get sql deadlocks. This is a local DB and there are no sql processes running on the table.
{
"data": "ERROR: Dude 500 : RequestError: Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."
}

Did anyone face this issue? Any suggestions will be greatly helpful..

[patriksimek]

Closing this one because it is not mssql realted. Try stack overflow with more detailed explanation.

[mcdado]

Just for the record, I had the same error message. Thanks in part to the Profiler in SQL Server I found out that I was improperly using this library.

In my code I was looping through objects and creating multiple PreparedStatements objects with the same identical UPDATE query (different input values of course). While developing on a VM with SQL Server Express I never faced any issues, but when I put it on our production database, I found out that running four UPDATE queries on a small table would case deadlocks on Page items. From my understanding the different PreparedStatement objects were being run as separate processes on the server (they had different SPIDs), and thereby deadlocking each other on page writing.

I solved the issue by executing the same PreparedStatement with different values. Reusing the single PreparedStatement runs those queries on the same SPID sequentially. I image I could catch the error and rerun the statement, but upon reading more on PreparedStatements I knew that my pattern wasn't ideal. Funny enough the same pattern wasn't showing any issues when updating records close to each other in a massive table with almost 200 columns. My guess is that those UPDATEs were not locking the same pages in the database.

I hope this helps anybody looking for help.

[hosseinGanjyar]

I've same problem within loop of update:

I fire _updateChannel_ in callback for many times.

function updateChannel(id) {
    const pool2 = new sql.ConnectionPool(config, err => {
        // ... error checks

        pool2.request()
            .query('update Table set flag=1 where ..., (err, result) => {
                // ... error checks

                //everythings
            })
    })
    pool2.on('error', err => {
        // ... error handler
    })
}

Error:

{ RequestError: Transaction (Process ID 58) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

[mcdado]

Try to initialize the connection pool outside the callback and reuse it at

each call. It should have an internal queue.

--
David Gasperoni

[hosseinGanjyar]

@mcdado How? please show me a sample code...
thanks

[mcdado]

This is how I've done it:

const mssql = require('mssql');
const bluebird = require('bluebird');

const connectionPool = new mssql.ConnectionPool(configuration);
const connectionPromise = this.connectionPool.connect();

If I need to do sequential updates, I follow this pattern:

const statement = new mssql.PreparedStatement(connectionPool);
const query = 'UPDATE…'; // omitted

connectionPromise.then(() => {
  statement.prepare(query)
});

connectionPromise.then(() => {
  return bluebird.map(arrayOfThings, (thing) => {
    const values = {
      fieldName: thing,
    };

    return statement.execute(values);
  }, { concurrency: 1 })
});

When I was looking at my code to pull this example, I realized that since 4.0 there are no more internal queues, so I switched to Bluebird and used Bluebird.map() to execute a callback on each element of an array of data. This callback runs and returns the executing statement, which itself is a promise. This allows to use the concurrency option and limit it to 1, which basically translates to sequential execution.

[zbcarter3]

I am having a similar issue but not sure how to solve it in my particular case. I am not the OG developer so I'm trying to piece things together.

I have a db.js file where a connection is created.

var db, env, knex, runsettings, settings;
settings = global.apps[global.namespace];
env = settings.general.env;
db = settings.general.db;
runsettings = settings.db[db][env];
config = global.apps[global.namespace].db.mssql.prod;
sql = require('mssql');
knex = require('knex');

knex.mssql = require('knex')({
    client: 'mssql',
    connection: {
        user: runsettings.user,
        password: runsettings.password,
        server: runsettings.server,
        database: runsettings.database,
        connectionTimeout: 300000,
        requestTimeout: 300000
    },
    pool: {
        idleTimeoutMillis: 300000,
        max: 100
    },
    useNullAsDefault: true
});

config["options"] = { encrypt: false };
config["requestTimeout"] = 300000;
config["connectionTimeout"] = 300000;

var connection  = new sql.ConnectionPool(config, function(err) {
    // ... error checks 
    settings.connection = connection;
});

connection.on('error', function(err) {
    connection.close();
    console.log(err);
});

module.exports = runsettings;

Then in one of the routes i loop through various conditions to insert or update records accordingly

knex        = require('knex').mssql;

handler: function(request, reply) {
     var fields = request.params.fields
     for(var key in fields) {
          if(fields[key].apiKey) {          
               knex('dbo.MatchupTableRecords').update(recordToUpdate).where(key).then(function() {
            if(key > fields.length-1) {
                               return reply('1')
                         }
        });
             }
     }
     else {
           knex('dbo.MatchupTableRecords').insert(recordToUpdate).then(function() {
        if(key > fields.length-1) {
                               return reply('1')
                         }
        });
}