Tom Lane wrote:
> "D. Dante Lorenso" <dante@lorenso.com> writes:
>> Douglas McNaught wrote:
>>> How quickly after you update the row status are you comitting (and
>>> releasing locks)?
>
>> I am calling a stored proc from PHP. Since I do not begin a
>> transaction, I assume that my call is automatically committed
>> immediately after invocation.
>
> Have you actually verified that, or are you just assuming it?
Just assuming. I'm not really sure HOW to verify it, though.
> I believe that PHP has some sort of autocommit on/off option,
> which might possibly be doing things behind your back.
I am using PHP / PDO and all my statements are prepared. Sometimes I
will begin a transaction using PDO and do either commit or rollback, but
I don't explicitly use transactions if I don't intend to exec more than
one statement. Here is what PHP says about auto-commit in PDO:
-------- 8< ---------------- 8< ---------------- 8< --------
http://www.php.net/manual/en/ref.pdo.php
Unfortunately, not every database supports transactions, so PDO needs to
run in what is known as "auto-commit" mode when you first open the
connection. Auto-commit mode means that every query that you run has its
own implicit transaction, if the database supports it, or no transaction
if the database doesn't support transactions.
If you need a transaction, you must use the PDO->beginTransaction()
method to initiate one. If the underlying driver does not support
transactions, a PDOException will be thrown (regardless of your error
handling settings: this is always a serious error condition). Once you
are in a transaction, you may use PDO->commit() or PDO->rollBack() to
finish it, depending on the success of the code you run during the
transaction.
-------- 8< ---------------- 8< ---------------- 8< --------
So, I feel safe enough with my assumption. I'm not entirely sure about
the stored procedure, though. I've recently rewritten the procedure as
separate queries, but don't know if that will help until I hit a high
peak load again.
-- Dante