1) Questions on transactions, concurrency control and recovery. Answer each part
ID: 3765440 • Letter: 1
Question
1) Questions on transactions, concurrency control
and recovery. Answer each part. Assume there is a part table given by the following when
relevant.
Also assume, where relevant, that clients have submitted the following sequence of requests
to the database that access and update tuples in the part table.
T1: begin
T1: read the price of part 4
T2: begin
T2: change the color of part 4 to 'yellow'
T3: begin
T3: read the price of part 4
T1: read the color of part 1
T4: begin
T4: change the name of part 1 to 'Block'
T2: change the price of part 5 to 150.00
T3: read the price of part 2
T2: commit
T4: change the department of part 5 to 'Machining'
T1: commit
T3: commit
T4: Commit
Question:
A) Suppose that a system failure occurs after the requests shown above have been processed.
Indicate the fate (commit or abort) of each of the transactions whose requests appear
in the sequence above. Also, show the contents of the part table after the system has
recovered from the failure, and before any new transactions are allowed to run.
B) Briefly explain why the SQL standard enables applications to set isolation levels. Also,
outline an example of a circumstance in which it makes sense for a client on behalf of
some application to set the isolation level to 0.
Explanation / Answer
A)
If a failure happends now the fate will be commit as all the process have been committed and it will go to the last commit point which is
T4:commit.
so everything will be as it is. The contents of the table will be as shown below
B)
Isolation levels are there in SQL to let users get only committed values. If there are some queries which are effecting the table values and are opened by multiple users at the same time then the select query will wait till all the queries are committed to the database. This ensure that in multi-user environment data coherency is maintained among all the users.
Level zero stands for read uncommitted in sql. It allows a task to read uncommitted changes to data in the database. This is also known as a dirty read, since the task can display results that are later rolled back. This will be used in application like Banking where updated balance should be reflected in the customer accounts.
pnum pname color price dept 1 Block Red 200.00 Machining 2 Axle Blue 120.00 Machining 4 Cog Yellow 30.00 Milling 5 Block Green 150.00 MachiningRelated Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.