Transactions in sql, PL-SQL Programming

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 transactions and cannot be given when a transaction has been started and not completed. Inner transactions are started using a SAVEPOINT statement, giving a name-a savepoint name-that identifies the database state at the time of execution. If SAVEPOINT SN1 has been given, for example, then RELEASE SAVEPOINT SN1 has the same effect as a Tutorial D COMMIT for all updates performed since savepoint SN1 was established-it merely relinquishes the possibility of cancelling just those updates and does not make their effects visible to other users.

To cancel those updates ROLLBACK TO SAVEPOINT SN1 is given, but then the savepoint name SN1 remains in existence. In both cases, any further existing savepoints, established after SN1, are destroyed. If an attempt is made to update the database when no transaction has been explicitly started, then a transaction is implicitly started. When no transaction has been started, a SET TRANSACTION statement can be given to specify various options to override the defaults that otherwise apply to the next transaction.

The options can alternatively be specified in a START TRANSACTION statement. The options in effect will apply when a transaction is implicitly started or when it is started by a START TRANSACTION statement that does not override them. One of the options for SET/START TRANSACTION is the so-called isolation level, which applies to the whole of the outermost transaction. The default isolation level is SERIALIZABLE, this being the only one that enforces all of the normally defined properties of transactions. The weakest level, READ UNCOMMITTED allows other concurrent users to see the effects of updates that have not yet been committed (and might never be, of course). Intermediate levels, READ COMMITTED and REPEATABLE READ, as well as UNCOMMITTED, allow a transaction to perceive changes to the database that have been effected by other, committed transactions (for example, by evaluating the same table expression more than once, without updating the database betweentimes, and getting different results).

Posted Date: 1/18/2013 11:43:07 PM | Location : United States







Related Discussions:- Transactions in sql, Assignment Help, Ask Question on Transactions in sql, Get Answer, Expert's Help, Transactions in sql Discussions

Write discussion on Transactions in sql
Your posts are moderated
Related Questions
Magento change address format depending on store Project Description: What I need is that depending on the store in which the customer bought the address should change the fo

IN Mode An IN parameter pass the values to the subprogram being called. Within the subprogram, an IN parameter acts like a constant. And hence, it cannot be assigned a value.

V ariables and Constants in PL/SQL The PL/SQL permits you to declare constants and variables, and then use them in SQL and procedural statements anywhere in the expression. Th

The Package Specification The package specifications contain the public declarations. The scopes of these declarations are local to your database representation and global to t

Loop Labels Like the PL/SQL blocks, loops can also be labeled. The label, an undeclared identifier enclosed by double angle brackets, should appear at the beginning of the LOOP

Overriding Default Locking By default, the Oracle locks the data structures for you automatically. Though, you can request exact data locks on rows or tables when it is to you

Architecture The PL/SQL run-time system and compilation is a technology, not an independent product. Consider this technology as an engine that compiles and executes the PL/SQL

Data Types in SQL - Decimal DECIMAL, NUMERIC, REAL, FLOAT and various other terms for various sets of rational numbers. When these key words are specified for the declared typ

Inner Join We have learned how to retrieve data from one table by using SELECT statement. But, as we have learned, normalized relational databases mean the data is spread betw

%TYPE: This attribute gives the datatype of a formerly declared collection, cursor variable, object, field, record, database column, or variable. Datatype: This is simply