Professional Web Applications Themes

Inconsistency in SQL - Microsoft SQL / MS SQL Server

While the DML is being performed within a transaction an Update lock is being hold for that row. Hence, a normal read would not happen. If you're able to read the row, you're running in an Isolation level of "read uncommitted" which means you're reading *dirty* data. So, what you see is by-design. -- -oj RAC v2.2 & QALite! http://www.rac4sql.net "Marsanz" <com.mx> wrote in message news:045801c350a5$a0affd60$gbl... ...

  1. #1

    Default Re: Inconsistency in SQL

    While the DML is being performed within a transaction an Update lock is
    being hold for that row. Hence, a normal read would not happen. If you're
    able to read the row, you're running in an Isolation level of "read
    uncommitted" which means you're reading *dirty* data. So, what you see is
    by-design.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net


    "Marsanz" <com.mx> wrote in message
    news:045801c350a5$a0affd60$gbl... 


    oj Guest

  2. #2

    Default Re: Inconsistency in SQL

    Hi

    Actually, I donīt use an Isolation level, because it show
    me false value while the transaccion donīt finish.
    And the select is locks, then I want to use select to see
    the original value without this locks

    What can I do?

    Thanks 
    Update lock is 
    happen. If you're 
    level of "read 
    what you see is [/ref]
    B ". [/ref]
    client [/ref]
    field [/ref]
    to 
    >
    >
    >.
    >[/ref]
    marsanz Guest

  3. #3

    Default Re: Inconsistency in SQL

    As I said, you're in a 'read uncommitted' isolation. Else you would not be
    able to see any rows that are being updated and are within an uncommitted
    transaction from another connection. It's by design that the rows being
    updated are locked until the change is either committed or rolled back.

    The bottom line, you can only read an in-transactioned row only if you use
    (nolock) or in an 'read uncommitted' isolation. And what you can read is
    *dirty* data.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "marsanz" <com.mx> wrote in message
    news:04e601c350b6$96412eb0$gbl...
    Hi

    Actually, I donīt use an Isolation level, because it show
    me false value while the transaccion donīt finish.
    And the select is locks, then I want to use select to see
    the original value without this locks

    What can I do?

    Thanks 
    Update lock is 
    happen. If you're 
    level of "read 
    what you see is [/ref]
    B ". [/ref]
    client [/ref]
    field [/ref]
    to 
    >
    >
    >.
    >[/ref]


    oj Guest

  4. #4

    Default Re: Inconsistency in SQL

    What I understand from your communication you want to read the old value
    while transaction is inprogress.

    try with readpast hint in your select.

    HTH
    Falik

    "Marsanz" <com.mx> wrote in message
    news:045801c350a5$a0affd60$gbl... 


    Falik Guest

  5. #5

    Default Re: Inconsistency in SQL

    Falik,

    ReadPast hint allows you to read-past the locked rows. It will not allow you
    to see *old* values.

    --
    -oj
    RAC v2.2 & QALite!
    http://www.rac4sql.net



    "Falik Sher" <com> wrote in message
    news:phx.gbl... 
    >
    >[/ref]


    oj Guest

  6. #6

    Default Re: Inconsistency in SQL

    As oj mentioned, when a resource is modified and the transaction is still open, other readers are either blocked (in the default READ COMMITTED isolation), or you see "dirty" data (in READ UNCOMMITTED isolation). To be able to see a consistent state of the data before the modifying transaction commits you have to build your own custom solution, e.g., having your modifying transactions copy the data aside before the modification, and deleting it at the end, and having your readers look for the data in the table that holds the copy if they are blocked.

    --
    BG, SQL Server MVP
    Solid Quality Learning
    www.solidqualitylearning.com


    "marsanz" <com.mx> wrote in message news:04e601c350b6$96412eb0$gbl...
    Hi

    Actually, I donīt use an Isolation level, because it show
    me false value while the transaccion donīt finish.
    And the select is locks, then I want to use select to see
    the original value without this locks

    What can I do?

    Thanks 
    Update lock is 
    happen. If you're 
    level of "read 
    what you see is [/ref]
    B ". [/ref]
    client [/ref]
    field [/ref]
    to 
    >
    >
    >.
    >[/ref]
    Itzik Guest

Similar Threads

  1. Deleting a PDF - inconsistency
    By Tommy Thomson in forum Macromedia Contribute General Discussion
    Replies: 0
    Last Post: May 23rd, 09:31 AM
  2. now() datediff inconsistency
    By theriversideweb in forum Macromedia ColdFusion
    Replies: 11
    Last Post: May 16th, 06:05 PM
  3. backup inconsistency
    By fpetalio in forum Windows Server
    Replies: 1
    Last Post: July 14th, 08:44 AM
  4. anchor inconsistency
    By Woahaw webforumsuser@macromedia.com in forum Macromedia Dreamweaver
    Replies: 3
    Last Post: July 17th, 11:19 PM
  5. Combo Box Inconsistency
    By Leon in forum Microsoft Access
    Replies: 0
    Last Post: July 7th, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139