mysql delayed insert timestamp

Question

I have a table with a field:: ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP

My question is, if I use delayed insert on this table, will the timestamp be the time when the request is queued or the time when the insert is actually made?

Austral

Answer 1

The answer is when the request is queued, but that's not necessarily right when the request is made, as the request is queued after the thread for the table is established if there is not already one.

From the mysql 5.1 dev docs:

The thread executes the INSERT statement, but instead of writing the row to the table, it puts a copy of the final row into a queue that is managed by the handler thread. Any syntax errors are noticed by the thread and reported to the client program.

The order of events when the delayed statement executes:

1. a handler thread for the table is created if there isn't one already
2. the handler checks for or waits to obtain a DELAYED lock
3. the handler executes the INSERT and put the final row into a queue
4. when the row is actually inserted, the binary log is updated
5. the handler writes delayed_insert_limit rows at a time and executes any pending SELECTS between writes
6. when the queue is empty, the DELAYED lock is released

Depending on whether a thread needs to be created or not and how long it takes to check or obtain a DELAYED lock, the time between the execution of the statement (step 0) and the execution of the statement (step 3) will vary. Then, depending on how large the queue is (particularly if it's over delayed_insert_limit rows), and whether any pending SELECTS happen, the write will be delayed by some unpredictable amount of time.

Stenographer

I think you are right (but not sure). Because, if the thread executes the statement to report syntax errors if any, it will probably also "expand" CURRENT_TIMESTAMP to the actual timestamp. – Austral

Answer 2

Regardless if INSERT DELAYED is used, or if the table is locked due to another thread or update or whatnot, the value ts will take is equal to the time the INSERT was issued.

Ride

Do you mean the "dummy" INSERT made by the handler thread or the INSERT that is actually done in the database. – Austral

Answer 3

It should take the time of the actual insert

Tessera