Using commit, PL-SQL Programming

Assignment Help:

Using COMMIT

The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot access the changed data; they see the data as it was before you made the changes.

Consider a simple transaction which transfers money from one bank account to the other. The transaction needs two updates as it debits the first account, and then credits the second. In the illustration below, after crediting the second account, you issue a commit that makes the changes everlasting. Only then do other users see the changes.

BEGIN

...

UPDATE accts SET bal = my_bal - debit

WHERE acctno = 7715;

...

UPDATE accts SET bal = my_bal + credit

WHERE acctno = 7720;

COMMIT WORK;

END;

The COMMIT statements release all row and table locks. It also erases any savepoint marked as the last commit or rollback. The elective keyword WORK has no effect other than to get better readability. The keyword END signals the end of the PL/SQL block, not the end of the transaction. Now as a block can span a multiple transactions, the transaction can cover multiple blocks.

The COMMENT clause specifies a comment to be related with the distributed transaction. If you issue a commit, the changes to each database affected by a distributed transaction are made permanent. Though, if a network or machine fails during a commit, the state of the distributed transaction may be unknown or in doubt. In that situation, the Oracle stores the text specified by the COMMENT in the data dictionary beside with the transaction ID. The text should be a quoted literal up to 50 characters in size.  


Related Discussions:- Using commit

Autonomous versus nested transactions, Autonomous versus Nested Transaction...

Autonomous versus Nested Transactions Though an autonomous transaction is started by the other transaction, it is not a nested transaction for the reasons shown below: (i)

Out mode - parameter modes, OUT Mode An OUT parameter returns values t...

OUT Mode An OUT parameter returns values to the caller of a subprogram. Within the subprogram, an OUT parameter act like a variable. That means that you can use an OUT formal

Variable declaration - sql, Variable Declaration - SQL SQL's support f...

Variable Declaration - SQL SQL's support for variables is very similar to Tutorial D's, except that the syntax for creating persistent  variables-base tables-is quite differen

Sql database, SQL Database: So, an SQL database is one whose symbols a...

SQL Database: So, an SQL database is one whose symbols are organized into a collection of tables. Now, shows an SQL table as the current value of an SQL variable, ENROLMENT, b

Assignment of DBMS in SQL server, i have an assignment of DBMS subject, thi...

i have an assignment of DBMS subject, this assignment is based on SQL server and power BI

Using commit, Using COMMIT The COMMIT statements end the present trans...

Using COMMIT The COMMIT statements end the present transaction and make permanent any changes made during that transaction. Till you commit the changes, other users cannot acc

Rollback behavior - bulk bind performance improvement, Rollback Behavior ...

Rollback Behavior When a FORALL statement fails, the database changes are rolled back to an implicit savepoint marked before each of the SQL statement execution. The Changes t

Usefulness of data type, Usefulness of Data Type In SQL, as in most c...

Usefulness of Data Type In SQL, as in most computer languages, a type can be used for constraining the values that are permitted to be used for some purpose. In particular, i

Exit-when - iterative control, EXIT-WHEN The EXIT-WHEN statement permits...

EXIT-WHEN The EXIT-WHEN statement permits a loop to complete conditionally. Whenever the EXIT statement is encountered, the condition in the WHEN clause is computed. When the co

Transactions in sql, Transactions in SQL BEGIN TRANSACTION, COMMIT, an...

Transactions in SQL BEGIN TRANSACTION, COMMIT, and ROLLBACK, SQL has the same syntax except for START in place of BEGIN. However, START TRANSACTION is used only for outermost

Write Your Message!

Captcha
Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd