CODEWITHSUNDEEP
sqlsql-servertransactions
user1948635
user1948635

Reputation: 1409

SQL Server Explicit Transactions Behaviour

I have just had to do a SQL exam and I really struggled with 2 questions around transactions, it has been submitted, so this is just for my sanity... What are the answers to these?

Sorry if this shouldn't be here, or if these should be 2 separate questions.

1.

Employee X has a salary of 40,000
a. User A begins a transaction and updates employee X's salary to salary * 1.1
b. User B begins a transaction and updates employee X's salary to salary * 1.25
c. User A rolls back their transaction
d. User B commits their transaction

What is Employee X's salary? (I went with 50,000, there was no mention of transaction isolation level)

2. A script is built up as follows -

a. Transaction start
b. Insert record into table 1
c. Create savepoint
d. Insert record into table 2
e. Rollback to the save point created after the insert into table 1
f. Insert a record into table 3
g. Commit the transaction

Which tables were records inserted into? (I went with table 1 only)

Upvotes: 2

Views: 154

Answers (2)

M.Ali
M.Ali

Reputation: 69494

For your question 1 assuming we have default transaction isolation level i.e Read Committed . your answer is right.

Question 1

  1. User one Begin a transaction and does not commit it
  2. User two tries to update the same record but his Update will be blocked until user one rolls back his transaction, But once he rolls back his transaction it will be updated to Salary * 1.25 which is 50000.

Question 2

  1. Record inserted in table one with a savepoint means if this transaction is rolled back it will not be rolled back all but to the last saved point.
  2. Insert into table 2 and then rolled back , which will rollback the insert into table 2 but not the insert in table 1 which is protected by the savepoint.
  3. Insert into table 3 and commmit. Which will save inserts made into table 1 and 3 to the disk.

Upvotes: 6

raza rabbani
raza rabbani

Reputation: 449

Sorry i didn't get your 1st question.

For Question 2 , Data will be inserted in table1 and table3.

Upvotes: 0

Related Questions