Oracle大批量更新数据操作需要使用COMMIT语句(Use commit statement after updating database)的调试经验分享

今天，发现对oracle进行更新操作时，有必要加上commit语句，否则，会出现以下错误(准确的oracle error id没记下来)：

1. 报错：数据被lock了

2. 报错：资源耗尽

总之，因为更新数据的sql语句块比较多（70段语句），至少要在中途加上两三个commit语句，从而防止上述两个错误。

以下内容摘自oracle database sql language reference_11.2g.pdf

-----------------------------------------------------------------------------------------------------------------------------

commit

purpose

use the commit statement to end your current transaction and make permanent all changes performed in the transaction. a transaction is a sequence of sql statements that oracle database treats as a single unit. this statement also erases all savepoints in the transaction and releases transaction locks.

until you commit a transaction:

■ you can see any changes you have made during the transaction by querying the modified tables, but other users cannot see the changes. after you commit the transaction, the changes are visible to other users' statements that execute after the

commit.

■ you can roll back (undo) any changes made during the transaction with the rollback statement (see rollback on page 18-96).

oracle database issues an implicit commit under the following circumstances:

■ before any syntactically valid data definition language (ddl) statement, even if the statement results in an error

■ after any data definition language (ddl) statement that completes without an error

you can also use this statement to:

■ commit an in-doubt distributed transaction manually

■ terminate a read-only transaction begun by a set transaction statement

oracle recommends that you explicitly end every transaction in your application programs with a commit or rollback statement, including the last transaction, before disconnecting from oracle database. if you do not explicitly commit the transaction and the program terminates abnormally, then the last uncommitted transaction is

automatically rolled back.

a normal exit from most oracle utilities and tools causes the current transaction to be committed. a normal exit from an oracle precompiler program does not commit the transaction and relies on oracle database to roll back the current transaction.

prerequisites

you need no privileges to commit your current transaction.

to manually commit a distributed in-doubt transaction that you originally committed,

you must have force transaction system privilege. to manually commit a distributed in-doubt transaction that was originally committed by another user, you must have force any transaction system privilege.

semantics

commit

all clauses after the commit keyword are optional. if you specify only commit, then the

default is commit work write wait immediate.

work

the work keyword is supported for compliance with standard sql. the statements

commit and commit work are equivalent.

comment clause

this clause is supported for backward compatibility. oracle recommends that you use named transactions instead of commit comments.

specify a comment to be associated with the current transaction. the 'text' is a quoted literal of up to 255 bytes that oracle database stores in the data dictionary view dba_2pc_pending along with the transaction id if a distributed transaction becomes in doubt. this comment can help you diagnose the failure of a distributed transaction.

write clause

use this clause to specify the priority with which the redo information generated by the commit operation is written to the redo log. this clause can improve performance by reducing latency, thus eliminating the wait for an i/o to the redo log. use this clause to improve response time in environments with stringent response time requirements where the following conditions apply:

■ the volume of update transactions is large, requiring that the redo log be written to disk frequently.

■ the application can tolerate the loss of an asynchronously committed transaction.

the latency contributed by waiting for the redo log write to occur contributes significantly to overall response time.

you can specify the wait | nowait and immediate | batch clauses in any order.

wait | nowait use these clauses to specify when control returns to the user.

■ the wait parameter ensures that the commit will return only after the corresponding redo is persistent in the online redo log. whether in batch or immediate mode, when the client receives a successful return from this commit statement, the transaction has been committed to durable media. a crash occurring after a successful write to the log can prevent the success message from returning to the client. in this case the client cannot tell whether or not the

transaction committed.

■ the nowait parameter causes the commit to return to the client whether or not the write to the redo log has completed. this behavior can increase transaction throughput. with the wait parameter, if the commit message is received, then you can be sure that no data has been lost.

caution: with nowait, a crash occurring after the commit message is received, but before the redo log record(s) are written, can falsely indicate to a transaction that its changes are persistent.

immediate | batch use these clauses to specify when the redo is written to the log.

■ the immediate parameter causes the log writer process (lgwr) to write the transaction's redo information to the log. this operation option forces a disk i/o, so it can reduce transaction throughput.

■ the batch parameter causes the redo to be buffered to the redo log, along with other concurrently executing transactions. when sufficient redo information is collected, a disk write of the redo log is initiated. this behavior is called "group commit", as redo for multiple transactions is written to the log in a single i/o operation. if you omit this clause, then the transaction commits with the immediate behavior.

force clause

the force string [, integer] clause lets you manually commit an in-doubt distributed transaction. the transaction is identified by the 'string' containing its local or global transaction id. to find the ids of such transactions, query the data dictionary view dba_2pc_pending. you can use integer to specifically assign the transaction a system

change number (scn). if you omit integer, then the transaction is committed using the current scn.