mysqli_fetch_assoc - what happens if the data is changed in the meanwhile?

Question

In PHP I'm using mysqli_fetch_assoc() in a while-loop to get every record in a certain query.

I'm wondering what happens if the data is changed while running the loop (by another process or server), so that the record doesn't match the query any more. Will it still be fetched?

In other words, is the array of records that are fetched fixed, when you do query()? Or is it not?

---

Update:

I understand that it's a feature that the resultset is not changed when the data is changed, but what if you actually WANT that? In my loop I'm not interested in records that are already updated by another server. How do I check for that, without doing a new query for each record that I fetch??

UPDATE:

Detailed explanation:

I'm working on some kind of searchengine-scraper that searches for values in a database. This is done by a few servers at the same time. Items that have been scraped shouldn't be searched anymore. I can't really control which server searches which item, I was hoping I could check the status of an item, while fetching the recordset. Since it's a big dataset, I don't transfer the entire resultset before searching, I fetch each record when I need it...

Answer 1

Introduction

I'm wondering what happens if the data is changed while running the loop (by another process or server), so that the record doesn't match the query any more. Will it still be fetched?

Yes.

In other words, is the array of records that are fetched fixed, when you do query()? Or is it not?

Yes.

A DBMS would not be worth its salt were it vulnerable to race conditions between table updates and query resultset iteration.

Certainly, as far as the database itself is concerned, your SELECT query has completed before any data can be changed; the resultset is cached somewhere in the layers between your database and your PHP script.

---

In-depth

With respect to the ACID principle *:

In the context of databases, a single logical operation on the data is called a transaction.

User-instigated TRANSACTIONs can encompass several consecutive queries, but 4.33.4 and 4.33.5 in ISO/IEC 9075-2 describe how this takes place implicitly on the per-query level:

The following SQL-statements are transaction-initiating SQL-statements, i.e., if there is no current SQLtransaction, and an SQL-statement of this class is executed, then an SQL-transaction is initiated, usually before execution of that SQL-statement proceeds:

• All SQL-schema statements
  • The following SQL-transaction statements:
  • <start transaction statement>.
  • <savepoint statement>.
  • <commit statement>.
  • <rollback statement>.
• The following SQL-data statements:
  • [..]
  • <select statement: single row>.
  • <direct select statement: multiple rows>.
  • <dynamic single row select statement>.
  • [..]
• [..]

In addition, 4.35.6:

Effects of SQL-statements in an SQL-transaction

The execution of an SQL-statement within an SQL-transaction has no effect on SQL-data or schemas [..]. Together with serializable execution, this implies that all read operations are repeatable within an SQL-transaction at isolation level SERIALIZABLE, except for:

1) The effects of changes to SQL-data or schemas and its contents made explicitly by the SQL-transaction itself.

2) The effects of differences in SQL parameter values supplied to externally-invoked procedures.

3) The effects of references to time-varying system variables such as CURRENT_DATE and CURRENT_USER.

---

Your wider requirement

I understand that it's a feature that the resultset is not changed when the data is changed, but what if you actually WANT that? In my loop I'm not interested in records that are already updated by another server. How do I check for that, without doing a new query for each record that I fetch??

You may not.

Although you can control the type of buffering performed by your connector (in this case, MySQLi), you cannot override the above-explained low-level fact of SQL: no INSERT or UPDATE or DELETE will have an effect on a SELECT in progress.

Once the SELECT has completed, the results are independent; it is the buffering of transport of this independent data that you can control, but that doesn't really help you to do what it sounds like you want to do.

This is rather fortunate, frankly, because what you want to do sounds rather bizarre!

---

^{* Strictly speaking, MySQL has only partial ACID-compliance for tables other than those with the non-default storage engines InnoDB, BDB and Cluster, and MyISAM does not support [user-instigated] transactions. Still, it seems like the "I" should remain applicable here; MyISAM would be essentially useless otherwise.}