Quote:
|
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.
Quote:
|
savepoint ? Does it release locks (as commit would) ?
|
No.
Quote:
|
savepoint ? does it reduce the occurence of transaction log full ?
|
No.
Quote:
|
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.
dBforums
>
savepoint
10-28-05, 04:15
Linear Mode
