This is mostly a theoretical question, and it's mostly about MySQL. Can I write a single query that will give me a number of records inserted from the time the query started to run until it ended, assuming the table has not timestamps etc, so this info cannot be inferred from the data in the table.
I tired this (and maybe it'll clarify the above):
select -(count(*) - (sleep(300) + count(*)) from my_table;
But it doesn't seem to do the job. I know I can write a stored procedure to do it, but I'm just curious if there's a way to do it in a single query, without writing a new function/stored procedure.
No, you really cannot, at least in theory. Databases support the ACID properties of transactions. The "I" in ACID stands for isolation, which specifically means that two queries do not interfere with each other. In other words, a query should not see inserts that happen after the query begins.
In practice, depending on settings, SELECT does not necessarily behave as its own transaction. However, it only sees the database as it is at any given instant, rather than knowing when particular changes occur.
There are proper ways to accomplish what you want. One simple method is to completely lock the table for the SELECT (in MySQL you can can do that with the for update directive). The query still will not be able to count the number of new rows. But, it will know the answer anyway: 0.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments