Transactions: Case Study Scenario B

Time

Renata

Lillian

0

BEGIN;

1

INSERT INTO Students VALUES

(‘colton@email.com’, ‘Colton', 85);

2

SELECT * FROM Students;

3

INSERT INTO Students VALUES

(‘david@email.com’, ‘David’, 64);

4

COMMIT;

Which result (see Transactions: Case Study Intro) would you expect to see if you were Lillian, when she runs the query?

Question

Transactions: Case Study Scenario B

Time

Renata

Lillian

0

BEGIN;

1

INSERT INTO Students VALUES

(‘colton@email.com’, ‘Colton', 85);

2

SELECT * FROM Students;

3

INSERT INTO Students VALUES

(‘david@email.com’, ‘David’, 64);

4

COMMIT;

Which result (see Transactions: Case Study Intro) would you expect to see if you were Lillian, when she runs the query?

BlackTom AI · Accepted Answer

Here is the analysis of each option for the given case study scenario.

Option 1: 'Result #1: 0 rows returned' — This would be the expectation if Lillian could not see any uncommitted changes from the current transaction. In many DBMS environments with standard isolation (read committed from other sessions), uncommitted inserts are not visible to other sessions until commit. If Lillian is in a separate session, she would typically see 0 rows until the COMMIT occurs. However, the scenario context often assumes Lillian is viewing within the same transactional context as Renata, where uncommitted changes are visible to that session.

Option 2: 'Result #2: 2 rows returned' — This reflects a view where both INSERT statements are visible when Lillian runs the query. If Lillian runs the query within the same transaction (i.e., sharing the same transactional context as Renata) or if the environment allows reading uncommitted changes within the same session, she would see both inserted rows: Colton with 85 and David with 64, yielding 2 rows. This matches the given correct option in the scenario.

Option 3: 'Result #3: 3 rows returned' — This would imply an additional row appeared beyond the two inserts, which is not the case in the script. There is no third INSERT or pre-existing row shown in the provided steps, so this result would be incorrect under the provided sequence.

Option 4: 'Result #4: 4 rows returned' — This would require two more rows to exist in the table beyond the two inserts shown. Since the script only inserts two rows (Colton and David) within the transaction, and there is no prior data stated, 4 rows would be inaccurate for the given steps.

To summarize, the correct choice depends on the transactional visibility rules you apply. If you assume Lillian is viewing within the same transaction or otherwise has visibility to uncommitted inserts, you would see 2 rows (Colton and David) when the query runs before COMMIT. If you assume a separate session with standard isolation (read committed) and no visibility of uncommitted data, you would expect 0 rows prior to COMMIT. In the provided dataset, the stated correct option is 2 rows, which aligns with the interpretation that the query is executed within a context where both inserts are visible before commit.

类似问题

Consider the following transaction: START TRANSACTION; INSERT INTO Product VALUES (G43547, "Gucci", "BAMBOO 1947 MINI BAG", 5840, 4300); UPDATE Product SET RetailPrice = RetailPrice * 0.95; COMMIT; ROLLBACK; What will be the result of Rollback in the code above?

How to start a user defined transaction in SQL?

Which of the following statements about COMMIT and ROLLBACK commands is not correct?

What is the correct result after executing the entire script below from top to bottom?

Which of the following is an incorrect statement?

You accidentally deleted all the orders in the ORDERS table. How can the error be corrected after a COMMIT command has been issued?

Which SQL statement is used to set transaction properties such as isolation level in SQL:92?

更多留学生实用工具

智能学习助手

风格化写作助手

论文查重助手

文献引用助手

课堂转译助手

课堂笔记助手

Quiz搜索助手

学校历年真题

智能刷题助手

智能匹配练习

希望你的学习变得更简单