Categories:
.NET (357)
C (330)
C++ (183)
CSS (84)
DBA (2)
General (7)
HTML (4)
Java (574)
JavaScript (106)
JSP (66)
Oracle (114)
Perl (46)
Perl (1)
PHP (1)
PL/SQL (1)
RSS (51)
Software QA (13)
SQL Server (1)
Windows (1)
XHTML (173)
Other Resources:
If locking is not implemented what issues can occur
If locking is not implemented what issues can occur?
✍: Guest
IFollowing are the problems that occur if you do not implement locking properly in SQL
SERVER.
Lost Updates
Lost updates occur if you let two transactions modify the same data at the same time, and
the transaction that completes first is lost. You need to watch out for lost updates with
the READ UNCOMMITTED isolation level. This isolation level disregards any type of
locks, so two simultaneous data modifications are not aware of each other. Suppose that
a customer has due of 2000$ to be paid. He pays 1000$ and again buys a product of
500$. Lets say that these two transactions are now been entered from two different counters
of the company. Now both the counter user starts making entry at the same time 10:00
AM. Actually speaking at 10:01 AM the customer should have 2000$-1000$+500 =
1500$ pending to be paid. But as said in lost updates the first transaction is not considered
and the second transaction overrides it. So the final pending is 2000$+500$ = 2500$.....I
hope the company does not loose the customer.
Non-Repeatable Read
Non-repeatable reads occur if a transaction is able to read the same row multiple times
and gets a different value each time. Again, this problem is most likely to occur with the
READ UNCOMMITTED isolation level. Because you let two transactions modify data
at the same time, you can get some unexpected results. For instance, a customer wants to
book flight, so the travel agent checks for the flights availability. Travel agent finds a seat
and goes ahead to book the seat.While the travel agent is booking the seat, some other
travel agent books the seat. When this travel agent goes to update the record, he gets
error saying that “Seat is already booked”. In short the travel agent gets different status at
different times for the seat.
Dirty Reads
Dirty reads are a special case of non-repeatable read. This happens if you run a report
while transactions are modifying the data that you're reporting on. For example there is a
customer invoice report which runs on 1:00 AM in afternoon and after that all invoices
are sent to the respective customer for payments. Let us say one of the customer has
1000$ to be paid. Customer pays 1000$ at 1:00 AM and at the same time report is run.
Actually customer has no money pending but is still issued an invoice.
Phantom Reads
Phantom reads occur due to a transaction being able to read a row on the first read, but
not being able to modify the same row due to another transaction deleting rows from the
same table. Lets say you edit a record in the mean time somebody comes and deletes the
record, you then go for updating the record which does not exist....Panicked.
Interestingly, the phantom reads can occur even with the default isolation level supported
by SQL Server: READ COMMITTED. The only isolation level that doesn't allow
phantoms is SERIALIZABLE, which ensures that each transaction is completely isolated
from others. In other words, no one can acquire any type of locks on the affected row
while it is being modified.
2007-10-25, 7029👍, 0💬
Popular Posts:
What is DAR (Decision Analysis and Resolution) ? Decision Analysis and Resolution is to analyze poss...
What Is a CAPTION Tag/Element? - XHTML 1.0 Tutorials - Understanding Tables and Table Cells A "capti...
How to convert a Unix timestamp to a Win32 FILETIME or SYSTEMTIME? The following function converts a...
.NET INTERVIEW QUESTIONS - Is versioning applicable to private assemblies? Versioning concept is onl...
How To Dump the Contents of a Directory into an Array? - PHP Script Tips - Working with Directoris a...