Implement in PL/SQL the following database transactions to demonstrate deadlock.
ID: 3712217 • Letter: I
Question
Implement in PL/SQL the following database transactions to demonstrate
deadlock.
The first transaction supposed to change an applicant 000003 email from NULL to mpoppins612@hotmail.com. Then increase the salary by 1000 for all the positions offered
by the employers located in New South Wales. The second transaction supposed to increase salary 10% for the positions offered by University of Wollongong. Then update an applicant 000003 phone number to 62336145.
Both transactions supposed to run at SERIALIZABLE isolation level.
Explanation / Answer
— Transaction 1
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; GO
BEGIN TRY
BEGIN TRANSACTION
—Suppose table name is EmployeeMaster
UPDATE EmployeeMaster SET email=‘mpoppins612@hotmail.com’
WHERE applicantID=‘000003’
UPDATE EmployeeMaster SET salary=salary + 1000
WHERE employerLocation=‘New South Wales'
COMMIT TRANSACTION
PRINT ’TRANSACTION SUCCEEDED’
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ’TRANSACTION FAILED’
END CATCH
— Transaction 2
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRY
BEGIN TRANSACTION
UPDATE EmployeeMaster SET salary=salary + (salary * 0.1)
WHERE offeredBy=‘University of Wollongong'
UPDATE EmployeeMaster SET phone_number=‘62336145’
WHERE applicantID=‘000003’
COMMIT TRANSACTION
PRINT ’TRANSACTION SUCCEEDED’
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
PRINT ’TRANSACTION FAILED’
END CATCH
THESE TWO TRANSACTIONS WILL CREATE A DEADLOCK DUE TO SERIALIZABLE ISOLATION LEVEL BECAUSE IN SERIALIZABLE ISOLATION LEVEL THE DATA CAANOT BE READ BY OTHER TRANSACTION UNTIL IT IS COMMITED BY THE PREVIOUS TRANSACTION. AS BOTH THE TRANSACTIONS WANTS TO UPDATE THE SAME TABLE AND SAME FIELD AND SAME ROWS OR MAY BE COMMON ROWS BOTH WILL TRY TO LOCK THE DATA AND UNTIL THE DATA IS LOCKED THE NEXT STATEMENT WILL NOT EXECUTE WHICH LEADS TO A DEADLOCK
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.