0

This answer claims that a transaction is useful for multiple read statements as well. Is the following test case faulty or does it require related tables to return consistent results?

In console #1 execute the following:

set transaction isolation level serializable;
begin transaction;
select * from TableA;
waitfor delay '00:00:15';
select * from TableB;
commit transaction;

In console #2, during that 15 second window, execute following:

set transaction isolation level serializable;
begin transaction;
insert into TableB(col) values('b');
commit transaction;

After 15 seconds has passed, console #1 returns with a row with 'b' in it. Why? I thought console #1 would return no results or the transaction #1 would abort, because TableB was modified.

I've tried SQL Server LocalDB 2017 on Windows and SQL Server 2019 RC1 on Linux. I ran my sql commands with DataGrip, but the original use case I had with Entity Framework 6.

Jani
  • 1,088
  • 1
  • 10
  • 18
  • I have SQL Server 2014 and I don't get the inserted record (b) in console #1 – PeterHe Sep 19 '19 at 13:50
  • @PeterHe: Which tool you used to run the commands? I tried SQL Server 2017 Express with Advanced Services and SSMS 2018 and still got the record `b`. – Jani Sep 20 '19 at 05:23
  • I used SSMS as the client and connected to SQL server 2014 server – PeterHe Sep 24 '19 at 15:39

1 Answers1

0

The console #1 execution Return the 'b' Record against TableB because the console #2 Add the Record in TableB before the Selection Query of TableB in console #1. Transaction lock only those Tables which is exist in Executed Queries. console #1 will not get the 'b' record if WAIT add after the selection of TableB. Query will be abort if structure of table is changed like Drop the column and add the New column before selection Query but If the * is not used. There is no error generate if the Drop column is not exists by Name in selected Query.

Liaqat Kundi
  • 71
  • 6