By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,246 Members | 1,515 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,246 IT Pros & Developers. It's quick & easy.

PDO Error: Already Active Transaction -- Help

P: n/a
I have the code below. First there is a transaction where I select
data. I wrapped it in an explicit transaction because in my real
program I run a couple different selects. Nevertheless, the
transaction should be closed with the commit command.

Next, I there is a separate transaction where I update data. But I get
the error: Uncaught exception 'PDOException' with message 'There is
already an active transaction'.

I don't understand why the first transaction is still considered
active.

$dbh = new PDO('sqlite:Movies.sqlite');
$movieID = 1; // or whatever, just for testing

$dbh->beginTransaction();
$selectMovie = $dbh->prepare('
SELECT movie_title
FROM movies
WHERE movie_id = :movieID
');
$selectMovie->bindParam(':movieID', $movieID);
$selectMovie->execute();
$movieResult = $selectMovie->fetch(PDO::FETCH_ASSOC);
$dbh->commit();
var_dump($movieResult);

$movieTitle = 'Some Other Blah Title';
$dbh->beginTransaction();
$updateMovie = $dbh->prepare('
UPDATE movies
SET movie_title = :movieTitle
WHERE movie_id = :movieID
');
$updateMovie->bindParam(':movieTitle', $movieTitle);
$updateMovie->bindParam(':movieID', $movieID);
$updateMovie->execute();
$dbh->commit();
Jun 2 '08 #1
Share this Question
Share on Google+
3 Replies


P: n/a
On 24 Apr, 12:24, FeelLikeA...@gmail.com wrote:
I have the code below. First there is a transaction where I select
data. I wrapped it in an explicit transaction because in my real
program I run a couple different selects. Nevertheless, the
transaction should be closed with the commit command.

Next, I there is a separate transaction where I update data. But I get
the error: Uncaught exception 'PDOException' with message 'There is
already an active transaction'.

I don't understand why the first transaction is still considered
active.

$dbh = new PDO('sqlite:Movies.sqlite');
$movieID = 1; // or whatever, just for testing

$dbh->beginTransaction();
$selectMovie = $dbh->prepare('
SELECT movie_title
FROM movies
WHERE movie_id = :movieID
');
$selectMovie->bindParam(':movieID', $movieID);
$selectMovie->execute();
$movieResult = $selectMovie->fetch(PDO::FETCH_ASSOC);
$dbh->commit();
var_dump($movieResult);

$movieTitle = 'Some Other Blah Title';
$dbh->beginTransaction();
$updateMovie = $dbh->prepare('
UPDATE movies
SET movie_title = :movieTitle
WHERE movie_id = :movieID
');
$updateMovie->bindParam(':movieTitle', $movieTitle);
$updateMovie->bindParam(':movieID', $movieID);
$updateMovie->execute();
$dbh->commit();
You don't check if the commit was successful but more probably, its
because you haven't closed the cursor before executing the next exec.
Also why do you think you need a transaction when you're not doing any
DML/DDL updates?

C.
Jun 2 '08 #2

P: n/a
On Apr 24, 8:09 am, "C. (http://symcbean.blogspot.com/)"
<colin.mckin...@gmail.comwrote:
On 24 Apr, 12:24, FeelLikeA...@gmail.com wrote:
I have the code below. First there is a transaction where I select
data. I wrapped it in an explicit transaction because in my real
program I run a couple different selects. Nevertheless, the
transaction should be closed with the commit command.
Next, I there is a separate transaction where I update data. But I get
the error: Uncaught exception 'PDOException' with message 'There is
already an active transaction'.
I don't understand why the first transaction is still considered
active.
$dbh = new PDO('sqlite:Movies.sqlite');
$movieID = 1; // or whatever, just for testing
$dbh->beginTransaction();
$selectMovie = $dbh->prepare('
SELECT movie_title
FROM movies
WHERE movie_id = :movieID
');
$selectMovie->bindParam(':movieID', $movieID);
$selectMovie->execute();
$movieResult = $selectMovie->fetch(PDO::FETCH_ASSOC);
$dbh->commit();
var_dump($movieResult);
$movieTitle = 'Some Other Blah Title';
$dbh->beginTransaction();
$updateMovie = $dbh->prepare('
UPDATE movies
SET movie_title = :movieTitle
WHERE movie_id = :movieID
');
$updateMovie->bindParam(':movieTitle', $movieTitle);
$updateMovie->bindParam(':movieID', $movieID);
$updateMovie->execute();
$dbh->commit();

You don't check if the commit was successful but more probably, its
because you haven't closed the cursor before executing the next exec.
Also why do you think you need a transaction when you're not doing any
DML/DDL updates?

C.
Yup, closing the cursor was the trick. Thanks.

About checking the return value of commits... can I configure the PDO
instance to throw an exception if the operation failed rather than
checking return values?
Jun 2 '08 #3

P: n/a
On Thu, 24 Apr 2008 21:17:43 +0200, <Fe**********@gmail.comwrote:
On Apr 24, 8:09 am, "C. (http://symcbean.blogspot.com/)"
<colin.mckin...@gmail.comwrote:
>On 24 Apr, 12:24, FeelLikeA...@gmail.com wrote:
I have the code below. First there is a transaction where I select
data. I wrapped it in an explicit transaction because in my real
program I run a couple different selects. Nevertheless, the
transaction should be closed with the commit command.
Next, I there is a separate transaction where I update data. But I get
the error: Uncaught exception 'PDOException' with message 'There is
already an active transaction'.
I don't understand why the first transaction is still considered
active.
$dbh = new PDO('sqlite:Movies.sqlite');
$movieID = 1; // or whatever, just for testing
$dbh->beginTransaction();
$selectMovie = $dbh->prepare('
SELECT movie_title
FROM movies
WHERE movie_id = :movieID
');
$selectMovie->bindParam(':movieID', $movieID);
$selectMovie->execute();
$movieResult = $selectMovie->fetch(PDO::FETCH_ASSOC);
$dbh->commit();
var_dump($movieResult);
$movieTitle = 'Some Other Blah Title';
$dbh->beginTransaction();
$updateMovie = $dbh->prepare('
UPDATE movies
SET movie_title = :movieTitle
WHERE movie_id = :movieID
');
$updateMovie->bindParam(':movieTitle', $movieTitle);
$updateMovie->bindParam(':movieID', $movieID);
$updateMovie->execute();
$dbh->commit();

You don't check if the commit was successful but more probably, its
because you haven't closed the cursor before executing the next exec.
Also why do you think you need a transaction when you're not doing any
DML/DDL updates?

C.

Yup, closing the cursor was the trick. Thanks.

About checking the return value of commits... can I configure the PDO
instance to throw an exception if the operation failed rather than
checking return values?
Probably, I'm not sure how to make a commit fail save for not starting a
transaction, that works though:
<?php
$db = new PDO('mysql:host=localhost;dbname=test','******','* *****');
//the magic:
$db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEP TION);
//test:
$db->commit();
?>
Results in:
PDOException: There is no active transaction in ....
--
Rik Wasmus
Jun 2 '08 #4

This discussion thread is closed

Replies have been disabled for this discussion.