New answers tagged

2

You have to start over and restore full and diffs with no recovery and then the tlogs with stand by option. This will allow you to restore tlogs later as well. You can configure logshipping with option of stand by, so you can read from the secondary database. There will be a .tuf (transaction undo file) file created when you restore tlogs with standby ...


0

Yes, if you want to apply additional transaction logs you will need to start back at your full backup. To quote MSDN, The database cannot be recovered until after the final transaction log has been applied. If you recover the database after restoring one of the intermediate transaction log backups, that before the end of the log chain, you cannot ...


9

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 ...


0

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 ...


3

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 ...


0

You may have one giant transaction for Connection 2. "3. Connection 2: update progress" should be in a transaction unto itself. Simplest is to not use BEGIN in Connection 2 and have autocommit=0. That makes Connection 2 pretty much the same as MyISAM.


0

None of the other suggested solutions appeared to work for me. Instead, I ended up switching the "job progress" table over to MyISAM from InnoDB. Since MyISAM does not support transactions, the records are written to the table right away even during a transaction. In the future, I'd love an alternative way to keep all tables in InnoDB for consistency, but ...


3

What you see is the correct behaviour for the default isolation setting (REPEATABLE READ) and even for lower (READ COMMITTED) setting. More details can be found in MariaDB and MySQL docs: SET TRANSACTION Transaction Isolation Levels Consistent Nonlocking Reads which basically sums up to: What one transaction is writing, other transactions should not ...


0

(I can't answer your question, but maybe the following will eliminate the problem you are seeing.) For long-running tasks (more than a few seconds), consider this technique: SET autocommit=1; UPDATE Tasks SET owner='me' WHERE task='use_car' AND owner IS NULL; if ROWS_AFFECTED == 0 THEN Do something else, or wait, or whatever START TRANSACTION; ... ...


3

Q. So, my question is, how can I figure out what the root cause of this blocking is? You may find it's one of the old standards: A concurrency issue with the code. A maintenance job doing a rebuild. A user connecting directly to the database for reporting (or Excel!) causing blocking. But it's going to be difficult if not impossible to trace this down ...


1

--single-transaction is used to ensure the dump is consistent when dumping, it has nothing to do with import/restore. Loading a dump is not generally possible in a single transaction because many CREATE statements do an implicit commit. It is often not even desirable because for a rollback to be possible, the transaction needs to keep undo logs and if you ...



Top 50 recent answers are included