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
Collection Methods:   The collection method is a built-in function or procedure which operates on the collections and is called using the dot notation. The methods like the C

Components of an Object Type: An object type encapsulates the operations and data. Therefore, you can declare the methods and attributes in an object type specification, but no

SELECT a.child_fname,a.child_lname,concat(b.parent_title,b.parent_fname), b.parent_lname,b.parent_tphone FROM child a,parent b WHERE a.parent_id=b.parent_id ORDER BY a.child_fnam

Updating Variables For assignment, SQL uses the key word SET, as in SET X = X + 1 (read as "set X equal to X+1") rather than X: = X + 1 as found in many computer languages.

Using a join on 2 tables, select all columns and 10 rows from the 2 tables without the use of a Cartesian product. Query: SELECT * FROM EMPLOYEE1 E JOIN STAFF S ON E.EMP_

Problem: (a) Define the following terms: (i) data mining. (ii) OLAP. (b) Differentiate between snowflake schema and star schema. Support your answer with appropriate

At times, customers make mistakes in submitting their orders and call to cancel the order. Brewbean’s wants to create a trigger that automatically updates the stock level of all pr

Procedures The procedure is a subprogram which can take parameters and be invoked. Normally, you can use a procedure to perform an action. The procedure has 2 sections: the spe

Set Operators The Set operators combine the results of the two queries into one result. The INTERSECT returns all the distinct rows selected by both queries. The MINUS returns

MECHANISTI S THEORY-HAECKEL (1866) - Haeckel stating that after each catalysm, some new organism suddenly forms as a chance event in one stride from inanimate matter and sub