Results 1 to 3 of 3

Thread: savepoint

  1. #1
    Join Date
    Feb 2005
    Posts
    118

    Unanswered: savepoint

    Hi All !

    What are the benefits of savepoint ? Does it release locks (as commit would) ? does it reduce the occurence of transaction log full ?

    Presently, I have an application that does 20,000 inserts. And the requierment is 'all or none', meaning either insert all the 20,000 records, or backout all data incase of an error.

    What's being done right now is
    loop:
    insert all 20,000 rows
    end-loop
    COMMIT;

    I was wondering if doing a SAVEPOINT after every 2000 inserts (just a random number) would be of any benefit to the application ?

    thanks

    Anil

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    What are the benefits of savepoint ?
    A COMMIT will commit everything your code has done on the DB since the last COMMIT or ROLLBACK (a unit of work (UOW)).
    A ROLLBACK will undo everything your code has done on the DB since the last COMMIT or ROLLBACK.
    Savepoints make it possible to ROLLBACK to a specific place in your SQL script without doing a full ROLLBACK.

    eg.
    suppose you want to deliver some goods:
    a hammer
    4 tires with the latest Pirelli calendar
    1000 nails

    On your stock table you have defined a check constraint NBR_STOCK_GTE_0 that the amount in stock must be >= 0.

    Without savepoints, you'd have a situation like this:
    Code:
    UPDATE stock_tbl 
     SET nbr_in_stock =  nbr_in_stock - 1 
     WHERE stock_id = 'hammer'
    if SQLSTATE = 'check constraint violation NBR_STOCK_GTE_0' 
    then ROLLBACK; exit
    
    UPDATE stock_tbl
     SET nbr_in_stock =  nbr_in_stock - 4
     WHERE stock_id = 'tire'
    if SQLSTATE = 'check constraint violation NBR_STOCK_GTE_0' 
    then ROLLBACK; exit
    UPDATE stock_tbl
     SET nbr_in_stock =  nbr_in_stock - 1
     WHERE stock_id = 'Pirelli calendar'
    if SQLSTATE = 'check constraint violation NBR_STOCK_GTE_0' 
    then ROLLBACK; exit
    
    UPDATE stock_tbl
     SET nbr_in_stock =  nbr_in_stock - 1000
     WHERE stock_id = 'nail'
    if SQLSTATE = 'check constraint violation NBR_STOCK_GTE_0' 
    then ROLLBACK; exit
    
    COMMIT
    So if you'd be out of Pirelli calendars of have only 999 nails in stock, nothing would be delivered.

    With savepoints you could program something like this:

    Code:
    SAVEPOINT svp_hammer ON ROLLBACK RETAIN CURSORS
    UPDATE stock_tbl 
     SET nbr_in_stock =  nbr_in_stock - 1 
     WHERE stock_id = 'hammer'
    if SQLSTATE = 'check constraint violation NBR_STOCK_GTE_0' 
    then ROLLBACK TO SAVEPOINT svp_hammer
    RELEASE SAVEPOINT svp_hammer
    
    SAVEPOINT svp_tire ON ROLLBACK RETAIN CURSORS
    UPDATE stock_tbl 
     SET nbr_in_stock =  nbr_in_stock - 4 
     WHERE stock_id = 'tire'
    if SQLSTATE = 'check constraint violation NBR_STOCK_GTE_0' 
    then ROLLBACK TO SAVEPOINT svp_tire
    else UPDATE stock_tbl 
      SET nbr_in_stock =  nbr_in_stock - 1 
      WHERE stock_id = 'Pirelli calendar'
      if SQLSTATE = 'check constraint violation NBR_STOCK_GTE_0' 
      then ROLLBACK TO SAVEPOINT svp_tire
    RELEASE SAVEPOINT svp_tire
    
    SAVEPOINT svp_nail ON ROLLBACK RETAIN CURSORS
    UPDATE stock_tbl 
     SET nbr_in_stock =  nbr_in_stock - 1000 
     WHERE stock_id = 'nail'
    if SQLSTATE = 'check constraint violation NBR_STOCK_GTE_0' 
    then ROLLBACK TO SAVEPOINT svp_nail
    RELEASE SAVEPOINT svp_nail
    COMMIT
    So if you'd be out of Pirelli calendars of have only 999 nails in stock, the things that are in stock will still be deliverd. And the client will not complain that he got tires without the calendar, because they are coupled with the same savepoint: the tires AND the calendar are delivered or nothing will be deliverd at all.
    savepoint ? Does it release locks (as commit would) ?
    No.
    savepoint ? does it reduce the occurence of transaction log full ?
    No.
    Presently, I have an application that does 20,000 inserts. And the requierment is 'all or none', meaning either insert all the 20,000 records, or backout all data incase of an error.
    Savepoints are of no use for this. COMMIT - ROLLBACK is alll you need.
    Last edited by Wim; 10-28-05 at 09:17.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Oct 2005
    Location
    pune(india)
    Posts
    24
    how to use savepoint in case of backing up the databases? I mean how does it resumes its execution from last savepoint in an execution env.

Posting Permissions

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