programming real life - Page 490

Difference between graph database: Neo4j & AllegroGraph

- by user313456

Hi All, What is the difference between this 2 graph DB: Neo4j and AllegroGraph? Which is better for JAVA Web programming? Thanks Erwin

How to avoid using duplicate savepoint names in nested transactions in nested stored procs?

I have a pattern that I almost always follow, where if I need to wrap up an operation in a transaction, I do this: BEGIN TRANSACTION SAVE TRANSACTION TX -- Stuff IF @error <> 0 ROLLBACK TRANSACTION TX COMMIT TRANSACTION That's served me well enough in the past, but after years of using this pattern (and copy-pasting the above code), I've suddenly discovered a flaw which comes as a complete shock. Quite often, I'll have a stored procedure calling other stored procedures, all of which use this same pattern. What I've discovered (to my cost) is that because I'm using the same savepoint name everywhere, I can get into a situation where my outer transaction is partially committed - precisely the opposite of the atomicity that I'm trying to achieve. I've put together an example that exhibits the problem. This is a single batch (no nested stored procs), and so it looks a little odd in that you probably wouldn't use the same savepoint name twice in the same batch, but my real-world scenario would be too confusing to post. CREATE TABLE Test (test INTEGER NOT NULL) BEGIN TRAN SAVE TRAN TX BEGIN TRAN SAVE TRAN TX INSERT INTO Test(test) VALUES (1) COMMIT TRAN TX BEGIN TRAN SAVE TRAN TX INSERT INTO Test(test) VALUES (2) COMMIT TRAN TX DELETE FROM Test ROLLBACK TRAN TX COMMIT TRAN TX SELECT * FROM Test DROP TABLE Test When I execute this, it lists one record, with value "1". In other words, even though I rolled back my outer transaction, a record was added to the table. What's happening is that the ROLLBACK TRANSACTION TX at the outer level is rolling back as far as the last SAVE TRANSACTION TX at the inner level. Now that I write this all out, I can see the logic behind it: the server is looking back through the log file, treating it as a linear stream of transactions; it doesn't understand the nesting/hierarchy implied by either the nesting of the transactions (or, in my real-world scenario, by the calls to other stored procedures). So, clearly, I need to start using unique savepoint names instead of blindly using "TX" everywhere. But - and this is where I finally get to the point - is there a way to do this in a copy-pastable way so that I can still use the same code everywhere? Can I auto-generate the savepoint name on the fly somehow? Is there a convention or best-practice for doing this sort of thing? It's not exactly hard to come up with a unique name every time you start a transaction (could base it off the SP name, or somesuch), but I do worry that eventually there would be a conflict - and you wouldn't know about it because rather than causing an error it just silently destroys your data... :-(

Search Results

Search found 24922 results on 997 pages for 'programming real life'.

Page 490/997 | < Previous Page | 486 487 488 489 490 491 492 493 494 495 496 497 | Next Page >

- by user313456

- by Gary McGill

- by garulfo

- by HJ-INCPP

- by Levi Roberts

- by Andrew Patterson

- by Chris M

- by Nick

- by girinie

- by nCdy

- by Mark Tomlin

- by mkorpela

- by Filip Ekberg

- by ILya

- by Gerald Kaszuba

- by emdadgar2

- by chaos

- by David Holm

- by Aaqil

- by Hough

- by Danijel Arsenovski

- by mcass20

- by furtelwart

- by Marco

- by fmsf

< Previous Page | 486 487 488 489 490 491 492 493 494 495 496 497 | Next Page >