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
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

THEO R Y OF SPECIAL CREATION - Life originated on the earth due to natural events by the super natural power. The biblical story of creation of world within six days was p

Keys in SQL SQL support for keys in the following respects: SQL does not require at least one key for every base table. If no key is explicitly declared, then KEY {ALL B

Using Host Arrays The Client-side programs can use anonymous PL/SQL blocks to bulk-bind input and output host arrays. However, this is the well-organized way to pass the colle

Example of Alternative formulation as a table constraint Example: Alternative formulation as a table constraint ALTER TABLE EXAM_MARK ADD CONSTRAINT Must_be_enrolled_to_

Committing and Rolling Back The COMMIT and ROLLBACK end the active autonomous transaction but do not exit the autonomous routine. As the figure shows, if one transaction ends,

Project Description: I want a small relational database to be built. I want the database to have the subsequent information tables: Employee Information Document storage

Relational Operators The relational operators permit you to compare randomly complex expressions. The list below provides the meaning of each operator:

Using SET TRANSACTION You use the SET TRANSACTION statement to begin the read-only or read-write transaction, start an isolation level, or assign your present transaction to a

Ending Transactions A good quality programming practice is to commit or roll back every transaction explicitly. Whether you rollback or issue the commit in your PL/SQL program