Write operation by transaction

Reference no: EM131304924

Written Assignment

Problem 1.

1. In the following schedules, R_i(A) stands for a Read(A) operation by transaction i and W_i(A) stands for a Write(A) operation by transaction i. For each of the following schedules show if it is conflict-serializable and give a conflict-equivalent serial schedule if it is one. Hint: use its dependency graph.

(a) R₁(A), R₂(B), W₃(A), R₂(A), R1(B), T₁.Commit, T₂.Commit, T₃.Commit

(b) R₁(A), R₂(B), W₁(A), R₃(C), W₂(B), W₃(C), R₄(D),
R₄(A), W₄(D), T1.Commit, T2.Commit, T3.Commit, T4.Commit

2. For the following 2 schedules, show if each is allowed in strict 2PL, and if not, what happens.

An example is given: (the schedule will not be allowed in strict 2PL as it is).

S₁(A), R₁(A), X₂(A), W₂(A), X₁(B), R₁(B), W₁(B), T₁.Commit, X₂(B), W2(B), T₂.Commit

T1	T2
S(A)
R(A)
	X(A), Blocked
X(B)
R(B)
W(B)
Commit
Release S(A)
	W(A)
	X(B), Blocked
Release X(B)
	W(B)
	Commit+Release its locks

T1	T2
S(A)
R(A)
	X(A), Blocked
X(B)
R(B)
W(B)
Commit
Release S(A)
Release X(B)
	W(A)
	X(B)
	W(B)
	Commit+Release its locks

(a) S₁(A), R₁(A), S₂(B), R₂(B), S₃(C), R₃(C), X₃(D), W₃(D), T₃.Commit,
X₂(C), W₂(C), T₂.Commit, X₁(B), W₁(B), T₁.Commit

(b) X₁(A), R₁(A), X₂₍B), R₂(B), X₃(C), R₃(C), S₁(B), R₁(B), S₂(C), R2₍C),

S₃(A), R₃(A), W₁(A), T₁.Commit, W₂(B), T₂.Commit, W₃(C), T₃.Commit

3. For the following schedules, show what happens in each case.

An example of non-strict 2PL is given (either is fine; in fact, there could be even more. But showing one is enough):

S₁(A), R₁(A), X₁(B), X₂(A), W₂(A), R₁(B), W₁(B), T₁.Commit, X₂(B), W₂(B), T₂.Commit

T1	T2
S(A)
R(A)
X(B)
	X(A), Blocked
Release S(A)
	W(A)
R(B)
W(B)
Release X(B)
Commit
	X(B)
	W(B)
	Release X(A), Release X(B)
	Commit

T1	T2
S(A)
R(A)
X(B)
Release S(A)
	X(A)
	W(A)
R(B)
W(B)
Release X(B)
Commit
	X(B)
	Release X(A)
	W(B)
	Release X(B)
	Commit

(a) X₁(B), W₁(B), X₂(A), W₂(A), S₂(B), R₂(B), S₁(A), R₁(A), T₁.Commit, T₂.Commit.
Using strict 2PL

(b) X₁(B), W₁(B), X₂(A), W₂(A), S₂(B), R₂(B), S₁(A), R₁(A), T₁.Commit, T₂.Commit.
Using non-strict 2PL

Using non-strict 2PL

Problem 2

• Database
- Employee-Table(ssn, name, salary)
∗ E1(132, Smith, 20K)
∗ E2(456, Kelley, 40K)
∗ E3(678, Johnson, 400K)
∗ E4(792, Preeston, 40K)
∗ E5(865, Johnson, 60K) ...
- DPT-table(dnumber, dname, budget)
∗ D1(1, Marketing, 1M)
∗ D2(2, Engineering, 2M)
∗ D3(3, R&D , 4M)

∗ D4(4, HR , 1M) ...

Part 1.

Consider the following schedules

1) T₁.R(E₁), T₁.W(E₁), T₂.R(E₂), T₂.W(E₂), T₁.R(D₁), T₁.commit, T₂.commit

2) T₁.R(E₁), T₁.W(E₁), T₂.R(E₂), T₂.W(E₂), T₂.W(E₁), T₁.R(E₁), T₁.commit, T₂.commit

3) T₁.R(E where salary>40K and salary<100k), T₂.Insert(into E, (999, Bob, 50k)), T₂.commit, T₁.R(E where salary>40k and salary<100k), T₁.commit

4) T₁.R(E where salary>40K and salary<100k), T₂.Insert(into E, (999, Bob, 50k)), T₁.R(E where salary>40k and salary<100k), T₂.commit, T₁.commit.

5) T₁.R(E where salary>40K and salary<100k), T₂.Update(set E.name=Alien where salary=60k), T₂.commit, T₁.R(E where salary>40k and salary<100k), T₁.commit.

For each schedule, please explain if it is valid in 1) the serializable isolation level; 2) the repeatable read isolation level.

Part 2.

Consider the following scenario:

Begin Transaction

T1.Read(E1)

T1.Update(set E1.salary=E1.salary*1.10) T1.Read(Select * from Employee)

T1.insert(Into E, (999, Bob, 50k)) T1.Read(Select * from Employee) T1.commit

Begin Transaction

T2.Update(set D4.budget=2M) T2.Delete(E5)

System Crash

Figure 1: Sequence of events

A. Show the content of the table Employee after the system has recovered from the system crash.

B. Show the results of T1.Read(E1), T1.Read(Select * from Employee) in Line 4 and Line 9, suppose we are in the repeatable read isolation level.

Problem 3.

Consider the log below. The records are of the form: (t-id, object-id, old-value, new-value).

Assumptions: a. The PrevLSN has been omitted (it's easy to figure it out yourself); b. for simplicity we assume that A, B, C and D each represents a page;

1. (T1, start)
2. (T1, A, 45, 10)
3. (T2, start)
4. (T2, B, 5, 10)
5. (T2, C, 35, 10)
6. (T1, D, 15, 5)
7. (T1, commit)
8. (T3, start)
9. (T3, A, 10, 15)
10. (T2, D, 5, 20)
11. (begin checkpoint, end checkpoint)
12. (T2, commit)
13. (T4, start)
14. (T4, D, 20, 30)
15. (T3, C, 10, 15)
16. (T3, commit)
17. (T4, commit)

A. What are the values of pages A, B, C and D in the buffer pool after recovery? Please specify which transactions have been ReDo and which transactions have been UnDo, and you are not required to show the details of the intermediate step.
1. if the system crashes just before line 6 is written to the stable storage?
2. if the system crashes just before line 10 is written to the stable storage?
3. if the system crashes just before line 13 is written to the stable storage?
4. if the system crashes just before line 17 is written to the stable storage?

B. Assume only the 3rd crash (as listed in Part A) has really happened and a recovery has then been performed, and the dirty pages caused by T1 have been flushed to disk before line 8, show the details of the Analysis phase (the content of two tables at both the beginning and the end of this phase), ReDo phase and UnDo phase (show the contents of ToUnDoList at each step and CLRs to be written to the LOG). You may assume that both the transaction table and dirty page table are empty at the beginning of line 1 of the log. Finally, show the content of log when the recovery has completed.

Reference no: EM131304924

Questions Cloud

Identify the inherent issues : The organization does have personnel responsible for quality assurance, compliance, and legal or risk management. However, even with internal resources, a hospital may hire an external consultant for another viewpoint and recommendations. Suppose ..

Why are qos features often necessary in wan routers : Some people think that Frame Relay will disappear over time. Do you think that is likely, and if so, why, and what do you think will replace it?

The firm needs to plow back its earnings to fuel growth : Metallica Bearings, Inc., is a young start-up company. No dividends will be paid on the stock over the next nine years because the firm needs to plow back its earnings to fuel growth. The company will pay a $10 per share dividend 10 years from today ..

Explain what is limitation of sentiment analysis application : In your post give an evaluation of each of the sentiment analysis applications. In your opinion, which of the applications that you tested was the most accurate? What are the limitations of sentiment analysis applications?

Write operation by transaction : What are the values of pages A, B, C and D in the buffer pool after recovery - Write(A) operation by transaction i. For each of the following schedules show if it is conflict-serializable and give a conflict-equivalent serial schedule if it is one...

How the literature you identified provides evidence : Discuss your library search strategies and briefly summarize the literature you reviewed. Focus your discussion on the "problem" and how the literature you identified provides evidence of the existence of the problem.Include in your discussion a po..

Calculate the average value of the one year interest rates : Suppose the yield on a five year bond is 1%, and the yield on a ten year bond is 1.5%. Calculate the average value of the one year interest rates prevailing in years 5, 6, 7, 8, and 9, assuming the expectations hypothesis of the term structure hold. ..

How important is the individual in the culture : How important is the individual in the culture? How important is the group? How is space used (e.g., how close should two people who are social acquaintances stand next to one another when they are having a conversation?)

Calculate liabilities-to-assets ratio : Assume you are given the following relationships for the Haslam Corporation: Sales/total assets 2 Return on assets (ROA) 3% Return on equity (ROE) 5%. Calculate Haslam's profit margin. Calculate Haslam's liabilities-to-assets ratio.

User Account

All Pages