Queuing using socket.io
Problem
I am using node.js to query a MySQL database table every 10 seconds. Using socket.io, after the database query every 10 secs, every connected browser will receive a unique batch of rows from that query.
Problem: With the way I implemented this, at every 10 second interval, there is a chance that more than 1 browsers receive the same set of rows. I used some checking out system (as shown below) to mark rows that have been sent to another browser already, but it does not seem to work all the time. How can I guarantee that each browser gets a unique set of rows each time?
In other words, the simultaneous querying of the db by the other browsers are faster than the locking of the rows!
Implementation
Every 10 seconds, the following occurs
- Node.js app querys the MySQL database table
SELECT * FROM table WHERE checkout = 0
- If result is returned, node.js server
io.sockets.send
a message to all connected browser saying that rows are available - All connected clients respond with a
socket.emit('ready')
- Server receives the
ready
emit, querys the database again to select 3 rows, then update tables to set thecheckout
column of these 3 rows to be1
- Server then emits the database query result to the client
Apparently it seems that it takes lesser time for the 2nd browser to cause a SELECT...
query than for the first browser to update the rows with checkout = 1
. Is there another way to do this queueing?
Screenshot
As you can see in the screenshot of 2 browsers open at the same time, often both browsers will get the same set of rows. The ids of the rows are console.log
ed as shown.
CODE
Node.js
io.sockets.on('connection', function(socket) {
// Client ready to take jobs after receiving broadcast
socket.on('ready', function() {
getListings(function(listings) {
socket.emit('job', listings); // send jobs
});
});
});
var getListings = function(callback) {
client.query('SELECT * FROM table ' +
'WHERE job_checkout = 0 ' +
'ORDER BY listing_id ASC ' +
'LIMIT 0, 3',
function(error ,results, fields) {
if (error)
throw error;
// Checkout listing now!
checkoutListings(results);
callback(results);
});
};
var checkoutListings = function(listings) {
for (var i = 0; i
Solution
update the rows before selecting them. eg
update table
set job_checkout = uniqueNumber
where job_checkout = 0
limit 10
uniqueNumber could be a process/thread id, or browser client id, or something suitable in the node.js environment(I'm not familiar with it). Now select the rows with that unique job_checkout.
Discussion
View additional discussion.