PDO
If you are using PDO::SQLITE and need to support a high level of concurrency with locking, try preparing your statements prior to calling beginTransaction() and you may also need to call closeCursor() on SELECT statements to prevent the driver from thinking that there are open transactions.
Here's an example (Windows, PHP version 5.2.8). We test this by opening 2 browser tabs to this script and running them at the same time. If we put the beginTransaction before the prepare, the second browser tab would hit the catch block and the commit would throw another PDOException indicating that transactions were still open.
<?php
$conn = new PDO('sqlite:C:\path\to\file.sqlite');
$stmt = $conn->prepare('INSERT INTO my_table(my_id, my_value) VALUES(?, ?)');
$waiting = true; // Set a loop condition to test for
while($waiting) {
try {
$conn->beginTransaction();
for($i=0; $i < 10; $i++) {
$stmt->bindValue(1, $i, PDO::PARAM_INT);
$stmt->bindValue(2, 'TEST', PDO::PARAM_STR);
$stmt->execute();
sleep(1);
}
$conn->commit();
$waiting = false;
} catch(PDOException $e) {
if(stripos($e->getMessage(), 'DATABASE IS LOCKED') !== false) {
// This should be specific to SQLite, sleep for 0.25 seconds
// and try again. We do have to commit the open transaction first though
$conn->commit();
usleep(250000);
} else {
$conn->rollBack();
throw $e;
}
}
}
?>
PDO::beginTransaction
(PHP 5 >= 5.1.0, PECL pdo >= 0.1.0)
PDO::beginTransaction — トランザクションを開始する
説明
bool PDO::beginTransaction
( void
)
オートコミットモードをオフにします。オートコミットモードがオフの間、 PDO オブジェクトを通じてデータベースに加えた変更は PDO::commit() をコールするまでコミットされません。 PDO::rollback() をコールすると、 データベースへの全ての変更をロールバックし、 オートコミットモードに設定された接続を返します。
MySQL を含むいくつかのデータベースでは、DROP TABLE や CREATE TABLE のようなデータベース定義言語 (DDL) ステートメントがトランザクション中に 発行される場合、暗黙的なコミットが自動的に発行されます。 この暗黙的なコミットにより、そのトランザクション境界で 他のあらゆる変更をロールバックすることができなくなるでしょう。
返り値
成功した場合に TRUE を、失敗した場合に FALSE を返します。
例
例1 トランザクションをロールバックする
以下の例は、トランザクションを開始し、 変更をロールバックする前にデータベースを修正する 2 つのステートメントを発行します。 しかしながら MySQL では、DROP TABLE ステートメントは 自動的にトランザクションをコミットするので、 トランザクション中のどの変更もロールバックされません。
<?php
/* トランザクションを開始する。オートコミットがオフになる */
$dbh->beginTransaction();
/* データベーススキーマとデータを変更する */
$sth = $dbh->exec("DROP TABLE fruit");
$sth = $dbh->exec("UPDATE dessert
SET name = 'hamburger'");
/* ミスに気づき、変更をロールバックする */
$dbh->rollBack();
/* データベース接続はオートコミットモードに戻る */
?>
add a note
User Contributed NotesPDO::beginTransaction
rjohnson at intepro dot us
11-Apr-2009 09:27
11-Apr-2009 09:27
dbeecher at tekops dot com
20-Aug-2008 04:21
20-Aug-2008 04:21
// If you need to set an ISOLATION level or LOCK MODE it needs to be done BEFORE you make the BeginTransaction() call...
//
// **note** you should always check result codes on operations and do error handling. This sample code
// assumes all the calls work so that the order of operations is accurate and easy to see
//
// THIS IS using the PECL PDO::INFORMIX module, running on fedora core 6, php 5.2.4
//
// This is the correct way to address an informix -243 error (could not position within table) when there
// is no ISAM error indicating a table corruption. A -243 can happen (if the table/indexes, etc., are ok)
// if a row is locked. The code below sets the LOCK MODE to wait 2 minutes (120 seconds) before
// giving up. In this example you get READ COMMITTED rows, if you don't need read committed
// but just need to get whatever data is there (ignoring locked rows, etc.) instead of
// "SET LOCK MODE TO WAIT 120" you could "SET ISOLATION TO DIRTY READ".
//
// In informix you *must* manage how you do reads because it is very easy to trigger a
// lock table overflow (which downs the instance) if you have lots of rows, are using joins
// and have many updates happening.
//
// e.g.,
$sql= "SELECT FIRST 50 * FROM mytable WHERE mystuff=1 ORDER BY myid"; /* define SQL query */
try /* create an exception handler */
{
$dbh = new PDO("informix:host=......");
if ($dbh) /* did we connect? */
{
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$dbh->query("SET LOCK MODE TO WAIT 120")
# ----------------
# open transaction cursor
# ----------------
if ( $dbh->beginTransaction() ) # explicitly open cursor
{
try /* open exception handler */
{
$stmt = $dbh->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_SCROLL));
$stmt->execute();
while ($row = $stmt->fetch(PDO::FETCH_NUM, PDO::FETCH_ORI_NEXT))
{
$data = $row[0] . "\t" . $row[1] . "\t" . $row[2] . "\t" . $row[3] . "\t" . $row[4] . "\t" . $row[5] . "\t" . $row[6] . "\t" . $row[7] . "\n" . $row[8] ;
//print $data;
print_r($row);
};
$stmt = null;
}
catch (PDOException $e)
{
print "Query Failed!\n\n";
print "DBA FAIL:" . $e->getMessage();
};
$dbh->rollback(); # abort any changes (ie. $dbh->commit()
$dbh = null; # close connection
}
else
{
# we should never get here, it should go to the exception handler
print "Unable to establish connection...\n\n";
};
};
}
catch (Exception $e)
{
$dbh->rollback();
echo "Failed: " . $e->getMessage();
};
brian at diamondsea dot com
25-Mar-2008 03:18
25-Mar-2008 03:18
Here is a way of testing that your transaction has started when using MySQL's InnoDB tables. It will fail if you are using MySQL's MyISAM tables, which do not support transactions but will also not return an error when using them.
<?
// Begin the transaction
$dbh->beginTransaction();
// To verify that a transaction has started, try to create an (illegal for InnoDB) nested transaction.
// If it works, the first transaction did not start correctly or is unsupported (such as on MyISAM tables)
try {
$dbh->beginTransaction();
die('Cancelling, Transaction was not properly started');
} catch (PDOException $e) {
print "Transaction is running (because trying another one failed)\n";
}
?>
drm at melp dot nl
11-Feb-2008 02:37
11-Feb-2008 02:37
In response to "Anonymous / 20-Dec-2007 03:04"
You could also extend the PDO class and hold a private flag to check if a transaction is already started.
class MyPDO extends PDO {
protected $hasActiveTransaction = false;
function beginTransaction () {
if ( $this->hasActiveTransaction ) {
return false;
} else {
$this->hasActiveTransaction = parent::beginTransaction ();
return $this->hasActiveTransaction;
}
}
function commit () {
parent::commit ();
$this->hasActiveTransaction = false;
}
function rollback () {
parent::rollback ();
$this->hasActiveTransaction = false;
}
}
Anonymous
20-Dec-2007 03:04
20-Dec-2007 03:04
beginTransaction will through a PDOException if you execute it while a PDO transaction is already active. Additionally the PDO engine doesn't seem to provide any way of determining if there is a transaction "in flight" so if you might be calling a function from within another function that starts a transaction you'll have to wrap the beginTransaction () call in a try .. catch block.