Professional Web Applications Themes

Commit without releasing locks? - Microsoft SQL / MS SQL Server

Is it possible to commit a transaction and open another one automatically, inheriting the lock of the first transaction? The idea is to prevent the Transactions getting too big while not releasing the locks obtained......

  1. #1

    Default Commit without releasing locks?

    Is it possible to commit a transaction and open another one
    automatically, inheriting the lock of the first transaction?

    The idea is to prevent the Transactions getting too big while not
    releasing the locks obtained...
    Eric Guest

  2. #2

    Default Re: Commit without releasing locks?

    Yes, I understand that. But he also wants to commit the transaction. So
    although the 'nested tran' solution will hold the locks, it WON'T commit the
    transacton.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "JXStern" <net> wrote in message
    news:com... [/ref]
    one. [/ref]
    nest [/ref]
    the [/ref]
    have 
    >
    > Kalen, he doesn't want to release the locks, he wants to HOLD them!
    >
    > J.
    >[/ref]


    Kalen Guest

  3. #3

    Default Re: Commit without releasing locks?

    The name itself is confusing. A savepoint doesn't actually SAVE anything, it
    just marks a point to which you can do a rollback, and undo all work from
    the savepoint until the point where you issue the rollback command. As I
    said, COMMITs can never be partially done. It's always all or nothing.

    --
    HTH
    ----------------
    Kalen Delaney
    SQL Server MVP
    www.SolidQualityLearning.com


    "JXStern" <net> wrote in message
    news:com... [/ref]
    the 
    >
    > I find myself confused on this, but haven't run the requisite
    > experiments.
    >
    > ... pause ...
    >
    > OK, now I have.
    >
    > The save point seems to work about half-way as requested, in that you
    > CAN roll back to a save point.
    >
    > update tableX set whatever='****'
    >
    > select * from tableX
    >
    > begin transaction A
    >
    > update tableX set whatever='abc' where myID=1
    >
    > save transaction AA
    >
    > begin transaction B
    >
    > update tableX set whatever='def' where myID=2
    >
    > rollback transaction AA
    >
    > commit transaction A
    >
    > select * from tableX
    >
    > --
    >
    > The above changes only the record with myID=1.
    >
    > Maybe it's not quite what the original poster wants because save does
    > not REALLY do a commit, but he might just be happy with coming this
    > close.
    >
    > (It's also not really a nested transaction, I thought you could roll
    > back to an intermediate transaction level, but I get a runtime error
    > when I tried that).
    >
    > J.
    >[/ref]


    Kalen Guest

Similar Threads

  1. Acrobat not releasing files
    By Francois_Viljoen@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 2
    Last Post: July 29th, 12:55 PM
  2. version numbers and releasing software...
    By Jim in forum ASP.NET Web Services
    Replies: 1
    Last Post: July 14th, 12:26 PM
  3. Releasing extents from a table.
    By JAMES in forum Informix
    Replies: 9
    Last Post: January 30th, 04:06 PM
  4. Why is Sun releasing mozilla 1.2.1 now?
    By Akop Pogosian in forum Sun Solaris
    Replies: 10
    Last Post: August 3rd, 09:51 AM
  5. Releasing a Record Lock
    By Scott in forum FileMaker
    Replies: 0
    Last Post: June 26th, 05:29 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