Professional Web Applications Themes

Select the rows which are implicated in an not committed update command - Microsoft SQL / MS SQL Server

Table Test(id bigint, name varchar(55)) and contains: 1 Linda 2 Tom User A: set transaction ISOLATION LEVEL READ COMMITTED begin tran insert into Test(name) values('John') User B: set transaction ISOLATION LEVEL READ COMMITTED begin tran update Test set name='Mary' where id=1 User C: set transaction ISOLATION LEVEL READ COMMITTED begin tran select * from Test with(readpast) Result for user C is: 2 Tom but user C wants to see: 1 Linda 2 Tom and doesn't want to see the insert maked by user A. Users A and B doesn't committed their commands....

  1. #1

    Default Select the rows which are implicated in an not committed update command

    Table Test(id bigint,
    name varchar(55)) and contains:
    1 Linda
    2 Tom

    User A:
    set transaction ISOLATION LEVEL READ COMMITTED
    begin tran
    insert into Test(name) values('John')

    User B:
    set transaction ISOLATION LEVEL READ COMMITTED
    begin tran
    update Test set name='Mary' where id=1

    User C:
    set transaction ISOLATION LEVEL READ COMMITTED
    begin tran
    select * from Test with(readpast)

    Result for user C is:
    2 Tom

    but user C wants to see:
    1 Linda
    2 Tom

    and doesn't want to see the insert maked by user A.

    Users A and B doesn't committed their commands.

    Lucian Guest

  2. #2

    Default Re: Select the rows which are implicated in an not committed update command

    At present there is no way to do that. You can either wait until they have
    been committed and be blocked or use NOLOCK and read the dirty data.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Lucian Secara" <ro> wrote in message
    news:04ae01c3494a$147e1280$gbl... 


    Andrew Guest

  3. #3

    Default Re: Select the rows which are implicated in an not committed update command

    User C can say:

    set transaction ISOLATION LEVEL READ COMMITTED
    begin tran
    select * from Test with(nolock)
    where id is not null

    (Because the insert won't be completed until the update is committed.)







    "Lucian Secara" <ro> wrote in message
    news:04ae01c3494a$147e1280$gbl... 


    Aaron Guest

  4. #4

    Default Re: Select the rows which are implicated in an not committed update command

    Sorry, I take that back. I don't think there's a way to tell the database
    "I want new updates, but not new inserts." You either want new data or you
    don't.




    "Lucian Secara" <ro> wrote in message
    news:04ae01c3494a$147e1280$gbl... 


    Aaron Guest

  5. #5

    Default Re: Select the rows which are implicated in an not committed update command

    I want to see the old data. No insert, no new values from
    the update, but I want THE OLD VALUES from the row which
    is locked in update.
    Unfortunately, the row which is locked by update is
    invisible to the select!

    Ex.:
    I have an invoice. I print a report with the invoice.
    It's ok. In 5 minutes somebody update the data of the
    invoice (changes the doent date, for example...). If he
    doesn't commit, I print the same report without this
    invoice (which I have seen it 5 minutes ago!)

     
    tell the database 
    want new data or you 

    Lucian Guest

  6. #6

    Default Re: Select the rows which are implicated in an not committed update command

    I know what your asking for but it is currently not available.

    --

    Andrew J. Kelly
    SQL Server MVP


    "Lucian Secara" <ro> wrote in message
    news:006701c34954$aff3b210$gbl... 
    > tell the database 
    > want new data or you 
    >[/ref]


    Andrew Guest

Similar Threads

  1. Select Multiple Rows within CFGRID?
    By roninDLC in forum Coldfusion Flash Integration
    Replies: 14
    Last Post: January 10th, 02:47 AM
  2. Select datagrid rows with key-up and key-down?
    By Mervin Williams in forum ASP.NET Data Grid Control
    Replies: 1
    Last Post: October 20th, 09:24 AM
  3. How to select first 50 rows?
    By miyaki in forum Informix
    Replies: 4
    Last Post: October 29th, 02:01 PM
  4. limiting rows returned in a sql select
    By Lennart Jonsson in forum IBM DB2
    Replies: 2
    Last Post: August 26th, 03:41 AM
  5. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM

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