3

I'm using a READ UNCOMMITTED transaction and I want to know if a specific row is committed or uncommitted.

AHiggins
  • 7,029
  • 6
  • 36
  • 54
Ricardo França
  • 2,923
  • 2
  • 18
  • 18
  • use this `DBCC USEROPTIONS WITH NO_INFOMSGS` it will tell you what isolation level you have in your current session. or even `DBCC USEROPTIONS` alone you give you the required info – M.Ali Aug 19 '15 at 14:12
  • @M.Ali - I think this question is row-by-row, rather than session-by-session. – MatBailie Aug 19 '15 at 14:13
  • well then question is wrong, you cant have Isolation level Row-by-Row , Isolation level is set for a session , any work done in that particular session will have the same Isolation level unless a query hint is specified e.g `WITH (NO LOCK)` etc. – M.Ali Aug 19 '15 at 14:15
  • http://stackoverflow.com/questions/22245573/ – wiretext Aug 19 '15 at 14:18
  • @MatBailie That's the question! I need row-by-row. – Ricardo França Aug 19 '15 at 14:21
  • @M.Ali I think you're right. I used "DBCC USEROPTIONS" but like you said, it's only by session. – Ricardo França Aug 19 '15 at 14:23
  • @tinka I'm using read uncommitted to get faster response, this select gonna overhead this one. – Ricardo França Aug 19 '15 at 14:28
  • 1
    No you can't find out, and you are probably misunderstanding [some of the hazards of using read uncommitted](http://sqlperformance.com/2015/04/t-sql-queries/the-read-uncommitted-isolation-level) if you think it is as simple as a row being committed or not. Read uncommitted will completely miss rows, potentially pages, it will return duplicates, it will return the same row with different data, or the same committed row from two points in time. If you care enough to find out whether it is a dirty read or not then use a more appropriate isolation level. – GarethD Aug 19 '15 at 14:40
  • read uncommitted is NOT a performance enhancing isolation level. It may return data faster but that speed comes at a price and that price is accuracy. A better approach would be to fix the query and or indexing. – Sean Lange Aug 19 '15 at 14:50

1 Answers1

0

You seem to need a variation of this:

SELECT * FROM T WITH (READUNCOMMITTED)
EXCEPT
SELECT * FROM T WITH (READCOMMITTED, READPAST)

This gives you the rows that are currently only visible under READUNCOMMITTED.

What a horrible hack. Please find another way to write your application. This is super brittle and hard to understand.

Community
  • 1
  • 1
usr
  • 168,620
  • 35
  • 240
  • 369