Professional Web Applications Themes

rollback doesn't reclaim unique id. - Microsoft SQL / MS SQL Server

I've created a table with an Identity column, seed = 1, increment = 1. I insert records 1, 2, and 3. As a test on how rollback works with the Identity columns I run the following statement Begin Tran Insert Into myTable (colName) Values (newValue) Rollback Tran If I then do a new insert statement I now have records 1, 2, 3, and 5. My objective is to only increment committed transactions, so that if the an insert is rolled back prior to commitment and re-committed It would result in records 1, 2, 3, and 4. (deletions of committed trans ...

  1. #1

    Default rollback doesn't reclaim unique id.

    I've created a table with an Identity column, seed = 1, increment = 1.
    I insert records 1, 2, and 3. As a test on how rollback works with
    the Identity columns I run the following statement

    Begin Tran
    Insert Into myTable (colName)
    Values (newValue)
    Rollback Tran

    If I then do a new insert statement I now have records 1, 2, 3, and 5.

    My objective is to only increment committed transactions, so that if
    the an insert is rolled back prior to commitment and re-committed It
    would result in records 1, 2, 3, and 4. (deletions of committed trans
    are a different story, I expect and want the id to be deleted)

    How do I write T-sql or configure the table so that rolling back a
    uncommitted transaction will not increment the seed?

    Thanks and regards
    --J Guest

  2. #2

    Default Re: rollback doesn't reclaim unique id.

    Is it a definite business requirement? The effect will be to serialize all inserts into this table. Say goodbye to scalability. Best to use it as advertised and ignore the gaps.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "--J" <com> wrote in message news:google.com...
    I've created a table with an Identity column, seed = 1, increment = 1.
    I insert records 1, 2, and 3. As a test on how rollback works with
    the Identity columns I run the following statement

    Begin Tran
    Insert Into myTable (colName)
    Values (newValue)
    Rollback Tran

    If I then do a new insert statement I now have records 1, 2, 3, and 5.

    My objective is to only increment committed transactions, so that if
    the an insert is rolled back prior to commitment and re-committed It
    would result in records 1, 2, 3, and 4. (deletions of committed trans
    are a different story, I expect and want the id to be deleted)

    How do I write T-sql or configure the table so that rolling back a
    uncommitted transaction will not increment the seed?

    Thanks and regards

    Tom Guest

  3. #3

    Default Re: rollback doesn't reclaim unique id.

    Whether or not you set the transaction isolation level to serializable or not, you will have to generate the keys without an identity column. Rather, you will need a key table to store the current value of the key. Each insert will have to begin a transaction, update the row to get the next key, do the insert and then commit. Thus, if you have a very busy system, there is going to be a lot of contention for that key table.

    So, to answer your question, SQL Server doesn't raise an error, since there is no error condition. Rather, it sits and waits until it can get a lock on the row in the key table.

    --
    Tom

    ---------------------------------------------------------------
    Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
    SQL Server MVP
    Columnist, SQL Server Professional
    Toronto, ON Canada
    www.pinnaclepublishing.com/sql


    "Julian Walker" <com> wrote in message news:phx.gbl...
    Thanks Tom for such a prompt response. Just to make sure I'm
    understanding you correctly. If I set inserts to SERIALIZABLE this
    locks out all other attempts to insert until the first transaction is
    committed thus greatly reducing scalability of users. Dose sql server
    queue the inserts or return an error or just ignore it?

    Thanks again,



    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!

    Tom Guest

Similar Threads

  1. Hide control and reclaim screen space
    By ScottGill in forum Macromedia Flex General Discussion
    Replies: 6
    Last Post: December 6th, 06:12 AM
  2. Unique Form inserting into many tables using unique id
    By Gabo Navarro in forum Macromedia Exchange Dreamweaver Extensions
    Replies: 0
    Last Post: September 16th, 06:25 PM
  3. CFTransaction doesn't rollback on error
    By CFGumby in forum Coldfusion Database Access
    Replies: 6
    Last Post: March 27th, 03:12 PM
  4. reclaim index space
    By Lyn Duong in forum IBM DB2
    Replies: 12
    Last Post: September 26th, 06:00 AM
  5. Reclaim disk storage
    By Franco Lombardo in forum IBM DB2
    Replies: 2
    Last Post: September 11th, 09:00 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