New answers tagged

2

Many alterings in one transaction with rollback and commit - it is not a dream. It is possible. Here is a scaffold for your script (following MS guidelines with improvements): BEGIN TRANSACTION BEGIN TRY -- place your script in this TRY block -- your DDL instructions: ALTER TABLE1... ALTER TABLE2... -- data modifications: EXEC(' ...


2

Man, I'm terribly sorry. I'm 'answering' stating that there's no simple solution as the one you devised. You presented us a very interesting challenge. I've been doing research and exercises in the past four hours to assure there's no way one can collect the aftermath of a committed transaction relying upon SQL Server engine by itself. If you, please, ...


2

There is no 100% reliable way to map rows to transactions, unless you add custom columns. Using ORA_ROWSCN, even with ROWDEPENDENCIES enabled, is not always accurate. Jack's test is correct, and shows that a single transaction will save all rows with the same SCN. However, it is also possible for a different transaction to create a row with the same SCN. ...


2

You could use a trigger which insert into table2 what has been removed from table1. CREATE TABLE table1(id int, name varchar(10), level int); INSERT INTO table1(id, name, level) VALUES (0, 'a', 0) , (1, 'b', 1) , (2, 'c', 0) , (3, 'd', 2) , (4, 'e', 1) , (4, 'f', 0); CREATE TABLE table2(id int, name varchar(10), level int); DELIMITER // ...


1

Insert everything into a temp table first. Then your insert and delete are simply everything from the temp table rather than your complicated query. This solves your problem of how to do both halves and is probably going to perform better than running your complicated query multiple times. I do more MS-SQL than My-SQL so I'll link to another answer on Stack ...


3

The idea is to track logical time vs physical time. Logical simply refers to what a user/app expects the time of an insert/update/delete to be. The fact that the DML operation may take a while for whatever reason, isn't meaningful or even easily determined and understood by a user. If you've ever had to explain lock vs latch contention to an accountant (I ...


13

Having an open transaction by itself will have almost no consequence. A simple BEGIN TRANSACTION -- wait for a while, doing nothing -- wait a bit longer COMMIT will, at worst, hold a few bytes of status values. No big deal. Most programs will do actual work within the transaction and this is another matter. The point of a transaction is so you can be ...


1

I did a small simulation of that situation, in a simple bash script that does that: ➜ rsandbox_5_6_30 cat test.sh ./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(3); rollback;" test 2> tx1 & ./m -e "START TRANSACTION; INSERT INTO test1 VALUES(6); select sleep(5); commit;" test 2> tx2 & ./m -e "START TRANSACTION; INSERT ...


4

Incomplete transaction may hold large number of locks and cause blocking When a transaction is not completed either because a query times out or because the batch is cancelled in the middle of a transaction without issuing a COMMIT or ROLLBACK statement to complete the transaction, the transaction is left open and all the locks acquired during ...


3

Your largest consequence will be blocking of the objects used in the transaction. Especially if you assume your users are inserting data, then that long running transaction could include SELECT statements on commonly used tables. Your users' update statements may not be able to get the necessary lock required to complete their updates or inserts. A ...



Top 50 recent answers are included