SQL As Understood By SQLiteBEGIN TRANSACTION
No changes can be made to the database except within a transaction.
Any command that changes the database (basically, any SQL command
other than ) will automatically start a transaction if
one is not already in effect.
Automatically started transactions
are committed when the last query finishes.
Transactions can be started manually using the BEGIN
Such transactions usually persist until the next
COMMIT or ROLLBACK command.
But a transaction will also
ROLLBACK if the database is closed or if an error occurs
and the ROLLBACK conflict resolution algorithm is specified.
See the documentation on the
clause for additional information about the ROLLBACK
conflict resolution algorithm.
END TRANSACTION is an alias for COMMIT.
Transactions created using BEGIN...COMMIT do not nest.
For nested transactions, use the
The "TO SAVEPOINT name" clause of the ROLLBACK command shown
in the syntax diagram above is only applicable to
transactions.
An attempt to invoke the BEGIN command within
a transaction will fail with an error, regardless of whether
the transaction was started by
or a prior BEGIN.
The COMMIT command and the ROLLBACK command without the TO clause
work the same on
transactions as they do with transactions
started by BEGIN.
Transactions can be deferred, immediate, or exclusive.
The default transaction behavior is deferred.
Deferred means that no locks are acquired
on the database until the database is first accessed.
Thus with a
deferred transaction, the BEGIN statement itself does nothing to the
filesystem.
are not acquired until the first read or write operation.
The first read
operation against a database creates a
lock and the first
write operation creates a
Because the acquisition of
locks is deferred until they are needed, it is possible that another
thread or process could create a separate transaction and write to
the database after the BEGIN on the current thread has executed.
If the transaction is immediate, then
are acquired on all databases as soon as the BEGIN command is
executed, without waiting for the
database to be used.
After a BEGIN IMMEDIATE,
will be able to write to the database or
do a BEGIN IMMEDIATE or BEGIN EXCLUSIVE.
Other processes can continue
to read from the database, however.
An exclusive transaction causes
locks to be acquired on all databases.
After a BEGIN
EXCLUSIVE, no other
except for
connections will be able to read the database and no other connection without
exception will be able to write the database until the transaction is
An implicit transaction (a transaction that is started automatically,
not a transaction started by BEGIN) is committed automatically when
the last active statement finishes.
A statement finishes when its
prepared statement is
incremental BLOB I/O counts as an unfinished statement.
finishes when it is .
The explicit COMMIT command runs immediately, even if there are
statements.
However, if there are pending
write operations, the COMMIT command
will fail with an error code .
An attempt to execute COMMIT might also result in an
return code
if an another thread or process has a
on the database
that prevented the database from being updated.
When COMMIT fails in this
way, the transaction remains active and the COMMIT can be retried later
after the reader has had a chance to clear.
In very old versions of SQLite (before version 3.7.11 circa )
the ROLLBACK will fail with an error code
if there are any pending queries.
In more recent
versions of SQLite, the ROLLBACK will proceed and pending statements
will often be aborted, causing them to return an
In SQLite version 3.8.8 and later, a pending read will continue functioning
after the ROLLBACK as long as the ROLLBACK does not modify the database
is set to OFF (thus disabling the rollback journal
file) then the behavior of the ROLLBACK command is undefined.
Response To Errors Within A Transaction
If certain kinds of errors occur within a transaction, the
transaction may or may not be rolled back automatically.
errors that can cause an automatic rollback include:
: database or disk full
: disk I/O error
: database in use by another process
: out or memory
For all of these errors, SQLite attempts to undo just the one statement
it was working on and leave changes from prior statements within the
same transaction intact and continue with the transaction.
depending on the statement being evaluated and the point at which the
error occurs, it might be necessary for SQLite to rollback and
cancel the entire transaction.
An application can tell which
course of action SQLite took by using the
C-language interface.
It is recommended that applications respond to the errors
listed above by explicitly issuing a ROLLBACK command.
transaction has already been rolled back automatically
by the error response, then the ROLLBACK command will fail with an
error, but no harm is caused by this.
Future versions of SQLite may extend the list of errors which
might cause automatic transaction rollback.
Future versions of
SQLite might change the error response.
In particular, we may
choose to simplify the interface in future versions of SQLite by
causing the errors above to force an unconditional rollback.Keyboard Shortcuts?
Next menu item
Previous menu item
Previous man page
Next man page
Scroll to bottom
Scroll to top
Goto homepage
Goto search(current page)
Focus search box
Change language:
Brazilian Portuguese
Chinese (Simplified)
PDO::beginTransaction
PDO::beginTransaction &
Initiates a transaction
Description
public bool PDO::beginTransaction
Some databases, including MySQL, automatically issue an implicit
COMMIT when a database definition language (DDL) statement such as
DROP TABLE or CREATE TABLE is issued within a transaction. The implicit
COMMIT will prevent you from rolling back any other changes within the
transaction boundary.
Return Values
Returns TRUE on success or FALSE on failure.
Example #1 Roll back a transaction
The following example begins a transaction and issues two statements
that modify the database before rolling back the changes. On MySQL,
however, the DROP TABLE statement automatically commits the
transaction so that none of the changes in the transaction are rolled
&?php/*&Begin&a&transaction,&turning&off&autocommit&*/$dbh-&beginTransaction();/*&Change&the&database&schema&and&data&*/$sth&=&$dbh-&exec("DROP&TABLE&fruit");$sth&=&$dbh-&exec("UPDATE&dessert&&&&SET&name&=&'hamburger'");/*&Recognize&mistake&and&roll&back&changes&*/$dbh-&rollBack();/*&Database&connection&is&now&back&in&autocommit&mode&*/?&
- Commits a transaction
- Rolls back a transaction
You can generate problems with nested beginTransaction and commit calls.
example:
beginTransaction()
do imprortant stuff
call method
& & beginTransaction()
& & basic stuff 1
& & basic stuff 2
& & commit()
do most important stuff
commit()
Won't work and is dangerous since you could close your transaction too early with the nested commit().
There is no need to mess you code and pass like a bool which indicate if transaction is already running. You could just overload the beginTransaction() and commit() in your PDO wrapper like this:
&?php
class Database extends \\PDO
{
& & protected $transactionCounter = 0;
& & function beginTransaction()
& & {
& & & & if(!$this-&transactionCounter++)
& & & & & & return parent::beginTransaction();
& & && return $this-&transactionCounter &= 0;
& & }
& & function commit()
& & {
& & && if(!--$this-&transactionCounter)
& & & & && return parent::commit();
& & && return $this-&transactionCounter &= 0;
& & }
& & function rollback()
& & {
& & & & if($this-&transactionCounter &= 0)
& & & & {
& & & & & & $this-&transactionCounter = 0;
& & & & & & return parent::rollback();
& & & & }
& & & & $this-&transactionCounter = 0;
& & & & return false;
& & }
}
?&
The nested transaction example here is great, but it's missing a key piece of the puzzle.& Commits will commit everything, I only wanted commits to actually commit when the outermost commit has been completed.& This can be done in InnoDB with savepoints.&?phpclass Database extends PDO{& & protected $transactionCount = 0;& & public function beginTransaction()& & {& & & & if (!$this-&transactionCounter++) {& & & & & & return parent::beginTransaction();& & & & }& & & & $this-&exec('SAVEPOINT trans'.$this-&transactionCounter);& & & & return $this-&transactionCounter &= 0;& & }& & public function commit()& & {& & & & if (!--$this-&transactionCounter) {& & & & & & return parent::commit();& & & & }& & & & return $this-&transactionCounter &= 0;& & }& & public function rollback()& & {& & & & if (--$this-&transactionCounter) {& & & & & & $this-&exec('ROLLBACK TO trans'.$this-&transactionCounter + 1);& & & & & & return true;& & & & }& & & & return parent::rollback();& & }& & }
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; 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) {& & & & & & $conn-&commit();& & & & & & usleep(250000);& & & & } else {& & & & & & $conn-&rollBack();& & & & & & throw $e;& & & & }& & }}?&
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.
In response to "Anonymous / 20-Dec-"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 =&& function beginTransaction () {& & & if ( $this-&hasActiveTransaction ) {& & & &&& & & } else {& & & && $this-&hasActiveTransaction = parent::beginTransaction ();& & & && return $this-&hasActiveT& & & }&& }&& function commit () {& & & parent::commit ();& & & $this-&hasActiveTransaction =&& }&& function rollback () {& & & parent::rollback ();& & & $this-&hasActiveTransaction =&& }}
// 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 $& & & & & & & & & & print_r($row);& & & & & & & & & & };& & & & & & & & & & & & & & & & $stmt =& & & & & & & & }& & & & & & catch (PDOException $e)& & & & & & & & {& & & & & & & & print "Query Failed!\n\n";& & & & & & & & & & & & & & & & print "DBA FAIL:" . $e-&getMessage();& & & & & & & & };& & & & & & & & & & & & $dbh-&rollback();& & & & & & & & & & & & & & & & & & & & & & & & & & && # abort any changes (ie. $dbh-&commit()& & & & & & $dbh =& & & & & & & & & & & & & & & & & & & & & & & & & & & & & & # 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();& & };
be aware that you also can not use TRUNCATE TABLE as this statement will trigger a commit just like CREATE TABLE or DROP TABLEit is best to only use SELECT, UPDATE and DELETE within a transaction, all other statements may cause commits thus breaking the atomicity of your transactions and their ability to rollbackobviously you can use DELETE FROM &table& instead of TRUNCATE TABLE but be aware that there are differences between both statements, for example TRUNCATE resets the auto_increment value while DELETE does not.
after TRUNCATE TABLE `table`& just as DELETE FROM `table`, so if whole table was deleted, aborts the transaction. And the rollback will not be passible.
With Oracle, any structure statement will do an implicit commit.So : ALTER TABLE "my_table" DROP COLUMN "my_column";Can't be rolled back !Hope this will save time for others
Strange behavior with pdo_firebird driver:&?php$dbh = new PDO ('','','',array( PDO :: ATTR_PERSISTENT=&true));$dbh-&setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION ); $dbh -& exec ( "select 1;" ); try{ && $dbh -& beginTransaction (); $dbh -& exec ( "select 1;" ); && $dbh -& commit (); }catch ( Exception $e ){ & $dbh -& rollBack ();
The example is misleading, Typically data definition language clauses (DDL) will trigger the database engine to automatically commit. It means that if you drop a table, that query will be executed regardless of the transaction.Ref-Mysql:& &
For the PDO driver for the& Firebird server, you have to explicitly disable autocommit to initiate the& transaction as well as explicitly enable autocommit to commit it.$dbh-&setAttribute( PDO::ATTR_AUTOCOMMIT, 0 );$dbh-&beginTransaction();/** Query block */$dbh-&commit();$dbh-&setAttribute( PDO::ATTR_AUTOCOMMIT, 1 );revolurion begin是什么意思_百度知道
revolurion begin是什么意思
提问者采纳
革命开始revolution[英] [ˌrevəˈlu:ʃən] [美] [ˌrɛvəˈluʃən] n. 革命；彻底改变；旋转；运行，公转；begin[英] [biˈɡin] [美] [ˈbeɡɪn] vt.& vi. 开始；着手；创始；创办； vi. （从……）开始；起始；起初是；开始讲话；
提问者评价
太给力了，你的回答完美地解决了我的问题，非常感谢！
其他类似问题
为您推荐：
其他1条回答
等待您来回答
下载知道APP
随时随地咨询
出门在外也不愁}