provided I have following update statement using Sequelize
return models.UnitPlant.findOne(
{
where:
{
id: unitPlantToUpdate.id
},
include: [
{ model: models.ProductType },
{ model: models.VehiclePlant, as: 'Customers' }
]
})
.then(function (unitPlantFromDb) {
return models
.sequelize
.transaction(
{
isolationLevel: models.sequelize.Transaction.ISOLATION_LEVELS.READ_COMMITTED
},
function (t) {
return unitPlantFromDb.update(unitPlantToUpdate, { transaction: t })
.then(function (unitPlantFromDb) {
return unitPlantFromDb.setProductType(unitPlant.ProductType.id, { transaction: t })
})
.then(function (unitPlantFromDb) {
return unitPlantFromDb.setCustomers(unitPlant.customerIds, { transaction: t });
});
})
.then(function (result) {
return output.getSuccessResult(titles.SUCCESS, validationMessages.SUCCESS_ON_UPDATE, UNIT_PLANT)
})
.catch(function (error) {
console.log(error);
return output.getErrorResult(titles.ERROR_ON_UPDATE, validationMessages.ERROR_ON_UPDATE, error.message);
});
});
Noting that I have a one to many relation on Unitplant to Customer.
The code above works fine but only as long as - when updating - I either only delete or only insert to customers.
Whenever both happen, I get following error:
Unhandled rejection SequelizeDatabaseError: Requests can only be made in the LoggedIn state, not the SentClientRequest state
From the console this happens:
Executing (783e1de2-5013-43b9-80e9-248dca6d83e1): BEGIN TRANSACTION;
Executing (783e1de2-5013-43b9-80e9-248dca6d83e1): SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Executing (783e1de2-5013-43b9-80e9-248dca6d83e1): UPDATE [UnitPlants] SET [expiresAt]='2016-06-22',[updatedAt]='2016-06-03 12:57:50.000 +00:00' OUTPUT INSERTED.* WHERE [id] = 15
Executing (783e1de2-5013-43b9-80e9-248dca6d83e1): UPDATE [UnitPlants] SET [ProductTypeId]=1,[updatedAt]='2016-06-03 12:57:50.000 +00:00' OUTPUT INSERTED.* WHERE [id] = 15
Executing (783e1de2-5013-43b9-80e9-248dca6d83e1): SELECT [VehiclePlant].[id], [VehiclePlant].[code], [VehiclePlant].[name], [VehiclePlant].[expiresAt], [VehiclePlant].[createdAt], [VehiclePlant].[upoCustomer], [VehiclePlant].[isExport], [VehiclePlant].[updatedAt], [UnitPlantCustomers].[createdAt] AS [UnitPlantCustomers.createdAt], [UnitPlantCustomers].[updatedAt] AS [UnitPlantCustomers.updatedAt], [UnitPlantCustomers].[UnitPlantId] AS [UnitPlantCustomers.UnitPlantId], [UnitPlantCustomers].[VehiclePlantId] AS [UnitPlantCustomers.VehiclePlantId] FROM [VehiclePlants] AS [VehiclePlant] INNER JOIN [UnitPlantCustomers] AS [UnitPlantCustomers] ON [VehiclePlant].[id] = [UnitPlantCustomers].[VehiclePlantId] AND [UnitPlantCustomers].[UnitPlantId] = 15;
Executing (783e1de2-5013-43b9-80e9-248dca6d83e1): DELETE FROM [UnitPlantCustomers] WHERE [UnitPlantId] = 15 AND [VehiclePlantId] IN (4); SELECT @@ROWCOUNT AS AFFECTEDROWS;
Executing (783e1de2-5013-43b9-80e9-248dca6d83e1): INSERT INTO [UnitPlantCustomers] ([createdAt],[updatedAt],[UnitPlantId],[VehiclePlantId]) VALUES ('2016-06-03 12:57:50.000 +00:00','2016-06-03 12:57:50.000 +00:00',15,3);
Executing (783e1de2-5013-43b9-80e9-248dca6d83e1): ROLLBACK TRANSACTION;
Note the INSERT and DELETE there.
Why does this happen only when I have both? And how do I tackle this problem?
As a sidenote still, the whole thing works when I do not use the transaction which leaves me wondering if I chose a wrong isolation level or my whole setup for the transaction is just wrong.
By default tedious (the node mssql driver) only supports one concurrent request, while other db libs manage query queueing automagically. Should be fixed in sequelize version 3.22