Is it possible to INSERT and then SELECT the inser

Question

I execute two queries one after another: one is INSERT another one is SELECT that selects the inserted row. Although the row was inserted successfully (i can see it in the db) the select query fails to return the row.

When I execute the SELECT query again it returns the correct result.

Insert:

    $stmt = $pdo->prepare('INSERT INTO user (id ,name, lastname ,birthday, social_type, social_id) VALUES(NULL, :name, :lastname, :birthday, :social_type, :social_id)');
    $success=$stmt->execute(array(
        ':name' => $user['name'],
        ':lastname' => $user['lastname'],
        ':birthday' => $user['birthday'],
        ':social_type' => $user['social_type'],
        ':social_id' => $user['social_id']      
    ));

Select

    $stmt = $pdo->prepare('SELECT * FROM user WHERE social_id = :social_id AND social_type = :social_type LIMIT 1');
    $stmt->execute(array(
        'social_id' => $user['social_id'],
        'social_type' => $user['social_type'] ));
    $result = $stmt->fetch(PDO::FETCH_ASSOC);

Answer 1

IF YOU ARE USING INNODB:

If you are using INNODB, since you verified the row was inserted, it should have been returned with the SELECT, as long as the SELECT was querying the key of the actual row that was inserted. (Are you sure you aren't using a feature like INSERT DELAYED? That could prevent the row from being returned.)

IF YOU ARE USING MYISAM:

Since MyISAM doesn't support transactions, the SELECT should return the insert, but I cannot find anything that states this is actually guaranteed.

NOTE: The first URL listed below states if you are using MYISAM (the default according to this link), INSERTS will lock the table. However, the second URL states that the lock placed by an insert is a readable lock, so that should not have prevented the table from being read.

http://www.sitepoint.com/mysql-mistakes-php-developers/

http://aarklondatabasetrivia.blogspot.com/2009/04/how-to-lock-and-unlock-tables-in-mysql.html

IF YOU ARE USING INNODB (CONTINUED):

If AUTOCOMMIT is in use in your system (I am not sure) you should have seen the selected row (this question states the row inserted was verified as having been added to the database).

If a transaction is in use, the transaction must have been committed (this question states the row inserted was verified as having been added to the database).

Are you sure the SELECT query that is executed the first time is the same as the one the second time?

Are you sure $user['social_id'] is the same value after the INSERT and at the time of the SELECT?

If you are referring to a row inserted in another transaction, instead of in the session that is doing the insert, then this URL:

http://blogs.innodb.com/wp/2011/04/get-started-with-innodb-memcached-daemon-plugin/

states "you will need to do “read uncommitted” select to find the just inserted rows:"

I.E. set session TRANSACTION ISOLATION LEVEL read uncommitted;

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html

(This feature may be dependent on the version of MYSQL in use)

If, for some reason, you are using INSERT DELAYED, the row may not be returned

---

NOTES: According to this URL, IF you have started a transaction, the selected rows are shown in the next SELECT statement (not in PHP):

http://zetcode.com/databases/mysqltutorial/transactions/

This statement implies that if you begin a transaction, you don't need to set AUTOCOMMIT:

"MySQL also automatically commits statements that are not part of a transaction."

This URL describes how to start a transaction in PHP:

PHP + MySQL transactions examples