Can I use a database value right after I insert it?

Question

Can I insert something into a MySQL database using PHP and then immediately make a call to access that, or is the insert asynchronous (in which case the possibility exists that the database has not finished inserting the value before I query it)?

Answer 1

Mostly the answer is yes. You would have to do some special work to force a database call to be asynchronous in the way you describe, and as long as you're doing it all in the same thread, you should be fine.

What is the context in which you're asking the question?

Answer 2

If the value is inserted in a transaction, it won't be accessible to any other transaction until your original transaction is committed. Other than that it ought to be accessible at least "very soon" after the time you commit it.

Answer 3

There are normally two ways of using MySQL (and most other SQL databases, for that matter):

1. Transactional. You start a transaction (either implicitly or by issuing something like 'BEGIN'), issue commands, and then either explicitly commit the transaction, or roll it back (failing to take any action before cutting off the database connection will result in automatic rollback).

2. Auto-commit. Each statement is automatically committed to the database as it's issued.

The default mode may vary, but even if you're in auto-commit mode, you can "switch" to transactional just by issuing a BEGIN.

If you're operating transactionally, any changes you make to the database will be local to your db connection/instance until you issue a commit. Issuing a commit should block until the transaction is fully committed, so once it returns without error, you can assume the data is there.

If you're operating in auto-commit (and your database library isn't doing something really strange), you can rely on data you've just entered to be available as soon as the call that inserts the data returns.

Note that best practice is to always operate transactionally. Even if you're only issuing a single atomic statement, it's good to be in the habit of properly BEGINing and COMMITing a transaction. It also saves you from trouble when a new version of your database library switches to transactional mode by default and suddenly all your one-line SQL statements never get committed. :)

Answer 4

What I think the OP is asking is this:

<?
  $id = $db->insert(..);
  // in this case, $row will always have the data you just inserted!
  $row = $db->select(...where id=$id...)

?>

In this case, if you do a insert, you will always be able to access the last inserted row with a select. That doesn't change even if a transaction is used here.