Solved

MYSQL/PHP -- Server and Nodes grabbing duplicates rarely.

Posted on 2013-05-27
1
465 Views
Last Modified: 2013-05-28
Hello there!

I have a server that holds all the data, and two nodes. The nodes have a similar table to the server that grab data from it and mark that it grabbed the data, so you do a simple

"select * from table where status='new'". from the node to the servers MYSQL. So all the nodes call this and grab new data. This is done using PHP.

from the node to the server (So we are doing remote MYSQL grabbing here of records.)


Then once it grabs it, i t changes the status to 'complete' on the main server. This was meant to prevent another node from grabbing the same data however because of latency sometimes the nodes grab the same record and process it. Both marking it as complete. It is not common but it does happen. So it is checking at about the same time, processing it because the record hasn't been marked 'complete' by the node until it's actually grabbed it.



So picture two node servers grabbing from the main server, at the same time, running the select * where status=new query, and processing/marking the record as status=complete, at similar times, enough that the latency is doing that.



How can I ensure that the nodes aren't grabbing the same records? Does anyone have any idea? I want to be able to use for example 10 node servers, not just 2 if needed without issues. At this time I think it could cause alot of duplicate grabbing and issues through MYSQL.


I understand that the nodes grabbing the data could be a problem. it would prevent it if the main server had a script and handled it, but the nodes are meant to be pluggable without any interaction or changes on the main server side (telling it that theres a new node etc isn't needed.) Few other things, but yeah, script is on the node sides ;)


Thanks guys, any advice is helpful. Even if it reduces it a large amount.
0
Comment
Question by:Valleriani
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 110

Accepted Solution

by:
Ray Paseur earned 500 total points
ID: 39199843
Maybe learm about LOCK TABLES (?)

If you're thinking of using a SELECT * statement, you might want to get a DBA involved early to help you understand why you do not want to use SELECT * and to help you organize the data and queries at the outset.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question