Creating children records with transactions locking tables / failing #4745
Description
Hi!
I am a bit going out of my mind now. I've tried various implementations to try and tackle the problem, but with no success. I am currently running version 3.12.2 of Sequelize.
My problem is the following:
I am trying to create one record and then children records that belong to the created record by using transactions. I have a Document model which has many Revisions. The code for the transaction currently looks like this:
function (req, res) {
var onRevisionCreated = function(revision){
console.log("REVISION CREATED");
return revision;
}
var onDocumentCreated = function(document,req,transaction){
console.log("DOCUMENT CREATED");
console.log(req.body.revision);
return Promise.map(req.body.revisions, function (rev) {
rev.documentID = newDoc.documentID;
return models.Revision.create(rev, {transaction: transaction}).then(function (newRev) {
return onRevisionCreated(newRev);
})
})
}
models.sequelize.transaction().then(function (t) {
return models.Document.create(req.body.document, {transaction: t}).then(function (newDoc) {
return onDocumentCreated(newDoc,req,t);
}).then(function(obj){
return t.commit();
}).catch(function(err){
return t.rollback();
})
}).then(function () {
res.status(201).send({message: "Document Created"});
});
}
I am not sure if I made any progress with any of the implementations I used. Sometimes I would get an error stating that the table was locked. And other times it will just generate a SELECT query for the revisions table.
Here are my model definitions:
DOCUMENT:
module.exports = function (sequelize, DataTypes) {
var Document = sequelize.define("Document", {
documentID: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
documentTypeID: {
type: DataTypes.INTEGER,
allowNull: false
},
authorID: {
type: DataTypes.INTEGER,
allowNull: false
},
classificationID: {
type: DataTypes.INTEGER,
allowNull: false
},
folderID: {
type: DataTypes.INTEGER,
allowNull: false
},
name: {
type: DataTypes.STRING(50),
unique: true,
validate: {
len: {
args: 3,
msg: "The document name should be at least 3 characters long."
}
}
},
description: {
type: DataTypes.TEXT
},
documentNumber: {
type: DataTypes.STRING(50),
unique: true
},
tags: {
type: DataTypes.STRING(255)
},
lastUpdatedByID: {
type: DataTypes.INTEGER
},
bulkUploadFlag: {
type: DataTypes.BOOLEAN
},
createdBy: {
type: DataTypes.INTEGER
}
}, {
classMethods: {
associate: function (models) {
Document.belongsTo(models.Folder, {
foreignKey: "folderID"
});
Document.belongsTo(models.Classification, {
foreignKey: "classificationID"
});
Document.belongsTo(models.DocumentType, {
foreignKey: "documentTypeID"
});
Document.hasMany(models.Revision, {
foreignKey: "documentID"
});
}
}
});
return Document;
}
REVISION:
module.exports = function (sequelize, DataTypes) {
var Revision = sequelize.define("Revision", {
revisionID: {
type: DataTypes.INTEGER,
primaryKey: true,
autoIncrement: true
},
documentID: {
type: DataTypes.INTEGER
},
revisorID: {
type: DataTypes.INTEGER
},
revisionNumber: {
type: DataTypes.DECIMAL,
validate: {
isUnique: function (value, next) {
Revision.find({
where: {
documentID: this.documentID,
revisionNumber: value
}
})
.then(function (revision) {
if (revision) {
return next("Duplicate revision number for specified document.");
}
})
.error(function (error) {
return next(error);
});
}
}
},
authorisedDate: {
type: DataTypes.DATE
},
releaseDate: {
type: DataTypes.DATE
},
implementationDate: {
type: DataTypes.DATE
},
expiryDate: {
type: DataTypes.DATE
},
notifyPersonnel: {
type: DataTypes.BOOLEAN
}
}, {
classMethods: {
associate: function (models) {
Revision.belongsTo(models.Document, {
foreignKey: "documentID"
});
Revision.hasMany(models.SoftCopy, {
foreignKey: "revisionID"
})
Revision.belongsToMany(models.Revision, {
as: "LinkedRevisions",
through: models.Link,
foreignKey: "revisionAID"
})
Revision.hasOne(models.Workflow,{
foreignKey:"revisionID"
});
}
}
});
return Revision;
}
These are the queries being generated:
Executing (8b047009-f47f-43d1-a4bd-64a671505702): START TRANSACTION;
Executing (8b047009-f47f-43d1-a4bd-64a671505702): SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
Executing (8b047009-f47f-43d1-a4bd-64a671505702): SET autocommit = 1;
Executing (8b047009-f47f-43d1-a4bd-64a671505702): INSERT INTO `Documents` (`documentID`,`documentTypeID`,`authorID`,`classificationID`,`folderID`,`name`,`description`,`documentNumber`,`tags`,`bulkUploadFlag`,`createdBy`,`updatedAt`,`createdAt`) VALUES (DEFAULT,2,'6773',3,5,'This is a duplicate Test','asdf','SP324','asdf,wer,xcb',false,'1494','2015-10-27 20:42:51','2015-10-27 20:42:51');
Executing (default): SELECT `revisionID`, `documentID`, `revisorID`, `revisionNumber`, `authorisedDate`, `releaseDate`, `implementationDate`, `expiryDate`, `notifyPersonnel`, `createdAt`, `updatedAt` FROM `Revisions` AS `Revision` WHERE `Revision`.`documentID` = 125 AND `Revision`.`revisionNumber` = 1 LIMIT 1;
Thank you in advance!
Regards.