grob/ringo-sqlstore — Issue #9: Transaction fails with parallel uploads and MySQL (Deadlock)
Author: botic
Created: 2011-10-20 19:22:37+00:00
State: open

If the upload in my Stick-based web application is accessed by more XHR-requests simultaneously, it throws a RollbackException:

```javascript
{
"status": "upload_badrequest",
"exception": "JavaException:
com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException:
Deadlock found when trying to get lock; try restarting transaction (ringo-sqlstore/lib/sqlstore/store.js#396)"
}
```

A quick fix was to add a sync() around the upload:

```javascript
/**
* Upload photos via HTTP requests.
*/
app.post("/", function(req) {
// TODO Check if this works fine:
// Synchronize this function --> There can be many parallel AJAX requests on it,
// which lets the sqlstore transaction fail.
return sync(function() {
// Only admins can upload images
if (!helpers.isAdmin(req)) {
return helpers.forbidden({"status": "user_notauthorized" });
}

// Get the current user who creates the image
var creator = User.query().equals("username", helpers.currentUser(req).username).select()[0];

if (req.postParams["photo"] && typeof req.postParams["photo"] == "object") {
var httpPhoto = req.postParams["photo"];

if (!httpPhoto.contentType || !strings.startsWith(httpPhoto.contentType, "image/")) {
return helpers.badRequest({
"status": "upload_badrequest"
});
} else {
try {
// Create the versions and save the uploaded image file
var filePaths = saveFile(httpPhoto);

// Extract the metadata out of the photo and save the entity
var originalPhoto = null;
for (var fsearch = 0; fsearch < filePaths.length; fsearch++) {
if (filePaths[fsearch].version == "cm_original") {
originalPhoto = filePaths[fsearch].path;
break;
}
}
if (!originalPhoto) {
throw "Original photo not found! Something went really wrong.";
}
var photo = extractMetadata(originalPhoto, creator);
photo.public = false;
photo.blocked = false;
photo.save();

// Attach the files to the photo entity
filePaths.forEach(function(fileVersion) {
var file = new File();
file.path = fileVersion.path.substr(config.paths["files"].length);

// Version information
file.version = fileVersion.version;

// Add the size information
var size = pixelpola.getSize(fileVersion.path);
file.width = size[0];
file.height = size[1];

// At the moment only JPEG is supported
switch (pixelpola.getCompression(fileVersion.path).toUpperCase()) {
case "JPEG": file.mimeType = "image/jpeg"; break;
}

// Create the reference to the photo and save the file entity
file.photo = photo;
file.save();
});

log.info(photo.creator.username+ " uploaded photo " + photo.reference);
return response.json({
"status": "upload_success",
"photo": helpers.serialize(photo)
});
} catch (e) {
return helpers.badRequest({
"status": "upload_badrequest",
"exception": e.toString()
});
}
}
}
return helpers.badRequest({
"status": "upload_badrequest"
});
})();
});
```

My configuration:
* Mac OS X 10.7.1
* MySQL 5.5.9
* RingoJS 0.8
* ringo-sqlstore commit a1cff00b9e96503c67f3087e324b909f4964f279