SQL Transactions: [Essay Example], 609 words GradesFixer
exit-popup-close

Haven't found the right essay?

Get an expert to write your essay!

exit-popup-print

Professional writers and researchers

exit-popup-quotes

Sources and citation are provided

exit-popup-clock

3 hour delivery

exit-popup-persone
close
This essay has been submitted by a student. This is not an example of the work written by professional essay writers.

SQL Transactions

Print Download now

Pssst… we can write an original essay just for you.

Any subject. Any type of essay.

We’ll even meet a 3-hour deadline.

Get your price

121 writers online

blank-ico
Download PDF

The isolation level controls the extent to which a given transaction is exposed to the actions of other transactions executing concurrently. By choosing one of four possible isolation level settings, a user can obtain greater concurrency at the cost of increasing the transaction’s exposure to other transactions’ uncommitted changes. The effect of these levels is summarized The highest degree of isolation from the effects of other transactions is achieved by setting the isolation level for a transaction T to SERIALIZABLE. This isolation level ensures that T reads only the changes made by committed transactions, that no value read or written by T is changed by any other transaction until T is complete, and that if T reads a set of values based on some search condition, this set is not changed by other transactions until T is complete (i.e., T avoids the phantom phenomenon). In terms of a lock-based implementation, a SERIALIZABLE transaction obtains locks before reading or writing objects, including locks on sets of objects that it requires to be unchanged (see Section 17.5.1) and holds them until the end, according to Strict 2PL.

Ensures that T reads only the changes made by committed transactions and no value read or written by T is changed by any other transaction until T is complete. However, T could experience the phantom phenomenon; for example, while T examines all Sailors records with rating=1, another transaction might add a new such Sailors record, which is missed by T.

Ttransaction sets the same locks a’S a SERIALIZABLE transaction, except that it does not do index locking; that is, it locks only individual objects, not sets of objects. Ensures that T reads only the changes made by committed transactions, and that no value written by T is changed by any other transaction until T is complete. However, a value read by T may well be modified by another transaction while T is still in progress, and T is exposed to the phantom problem.

Transaction obtains exclusive locks before writing objects and holds these locks until the end. It also obtains shared locks before reading objects, but these locks are released immediately; their only effect is to guarantee that the transaction that last modified the object is complete. (This guarantee relies on the fact that every SQL transaction obtains exclusive locks before writing objects and holds exclusive locks until the end.)

Transaction T can read changes made to an object by an ongoing transaction; obviously, the object can be changed further while T is in progress, and T is also vulnerable to the phantom problem.

Ttransaction does not obtain shared locks before reading objects. This mode represents the greatest exposure to uncommitted changes of other transactions; so much so that SQL prohibits such a transaction from making any changes itself-a READ UNCOMMITTED transaction is required to have an access mode of READ ONLY. Since such a transaction obtains no locks for reading objects and it is not allowed to write objects (and therefore never requests exclusive locks), it never makes any lock requests.

The isolation level is generally the safest and is recommended for most transactions. Some transactions, however, can run with a lower isolation level, and the smaller number of locks requested can contribute to improved system performance. For example, a statistical query that finds the average sailor age can be run at the READ COMMITTED level or even the level, because a few incorrect or missing values do not significantly affect the result if the number of sailors is large.

The isolation level and access mode can be set using the SET TRANSACTION com~mand.

Remember: This is just a sample from a fellow student.

Your time is important. Let us write you an essay from scratch

100% plagiarism free

Sources and citations are provided

Cite this Essay

To export a reference to this article please select a referencing style below:

GradesFixer. (2018, December, 03) SQL Transactions. Retrived February 26, 2020, from https://gradesfixer.com/free-essay-examples/sql-transactions/
"SQL Transactions." GradesFixer, 03 Dec. 2018, https://gradesfixer.com/free-essay-examples/sql-transactions/. Accessed 26 February 2020.
GradesFixer. 2018. SQL Transactions., viewed 26 February 2020, <https://gradesfixer.com/free-essay-examples/sql-transactions/>
GradesFixer. SQL Transactions. [Internet]. December 2018. [Accessed February 26, 2020]. Available from: https://gradesfixer.com/free-essay-examples/sql-transactions/
close

Sorry, copying is not allowed on our website. If you’d like this or any other sample, we’ll happily email it to you.

By clicking “Send”, you agree to our Terms of service and Privacy statement. We will occasionally send you account related emails.

close

Thanks!

Your essay sample has been sent.

Want us to write one just for you? We can custom edit this essay into an original, 100% plagiarism free essay.

thanks-icon Order now
boy

Hi there!

Are you interested in getting a customized paper?

Check it out!
Having trouble finding the perfect essay? We’ve got you covered. Hire a writer

GradesFixer.com uses cookies. By continuing we’ll assume you board with our cookie policy.