StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAbsurd MySQL behavior over same data set but different threads
primarykey
Id
9705300
data
AcceptedAnswerId
9706325
AnswerCount
1
ClosedDate
CommentCount
18
CommunityOwnedDate
CreationDate
2012-03-14T15:50:39.437
FavoriteCount
0
LastActivityDate
2012-03-14T16:50:20.367
LastEditDate
2012-03-14T16:16:44.920
LastEditorUserId
1678010
OwnerUserId
1678010
ParentId
0
PostTypeId
1
Score
2
ViewCount
134
LastEditorDisplayName
text
Body
I'm connecting to MySQL db from two different PHP instances. The first thread adds a new row, passes the 'id' of new row to the second thread via a queue. Sometimes the second thread is unable to find the new row data, even though theoretically it should hit the db after first threads has finished its job. The simplified pseudocode looks something like Thread 1 <pre><code>$db = get_mysql_connection(); $db->beginTransaction(); $rowid = $db->query("insert data.."); $db->commit(); //For Debugging purposes only $db->check_if_row_exists($rowid); //Always returns true send_to_queue($rowid); </code></pre> Thread 2 <pre><code>$rowid = fetch_from_queue(); $db = get_mysql_connection(); $db->check_if_row_exists($rowid); //Sometimes returns false; usleep(1000000); $db->check_if_row_exists($rowid); //Always returns true. </code></pre> I cannot understand, why thread 1 shows that data has a valid entry, while thread 2, which certainly makes a query sometime after thread 1, is unable to find the data. I'm using transactions to commit the data, is that doing something weird? I'm using Gearman as the queue. Thread 1 is run through Apache, while Thread 2 simply runs as a standalone process. Edit 1: This happens when thread 2 is running concurrently to thread 1. Apparently it reaches some race condition with Thread 1, but I can't figure out why. Edit 2: As pointed out by N.B., Innodb delays writing the data to disk, and hence it is not visible to second thread. How should I handle this scenario? Sleep/Usleep is nearly always a suboptimal solution, as under heavy load conditions, the disk i/o time may increase. Is there some way to 'notify' the second thread that Innodb has finished its disk i/o?
Tags
<php><mysql><mysqli>
Title
Absurd MySQL behavior over same data set but different threads
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USNeo
UserOwnerUserId
1. USNeo
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POAbsurd MySQL behavior over same data set but different threads
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
2. VO
 singulars
 PostPostId
 POAbsurd MySQL behavior over same data set but different threads
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.