New answers tagged transaction
Without explicit transaction, all statements are in auto-commit mode - so each insert is separate transaction. And that comes with some overhead for syncing etc. If you insert all rows without commiting in between, mysql can do lot of it in memory/cache and sync it to disk only once (simplified, caches will probably be flushed multiple times by filesystem ...
Each pgAdmin query window has it's own session. You can run one transaction at a time in each session. It's not possible to run two transactions concurrently in the same session (at least not in pgAdmin or psql). You can open and bind more sessions to the same query window with the "connection bar" in the menu of the query window, then switch between ...
We have a similar situation and we had to create a larger script to resolve deletion in a proper manner. Note: Another issue you will run into with large delete operations (one or many transactions), is that your transaction logs will grow really fast during the runtime of the delete. So, be prepared for that in terms of disc space. It may be required ...
You want current_setting: SELECT current_setting('pubsubpull.request_id');
No, absolutely impossible. Each sqlcmd invocation is a different session and transaction cannot span sessions. Your use of a transaction on the script that invokes the sqlcmd is completely useless, it has nothing to do with the scripts being run.
If possible you should reduce the amount of data changed per transaction. Commit more often with less data. Otherwise increase the log size even more. If you are running out of space, you need to increase.
•On my observation, I found that this is happening only with insert statement not with select. So, I presume that log entries will only be for INSERT/UPDATE & not for SELECT statement in any case. Am I correct? Select statement as such is not logged as compared to DML statements like insert , update and delete. If you see output of fn_dblog for ...
I don't see how this would be more useful than just CONNECTION_ID() ... but the InnoDB transaction ID (shown in SHOW ENGINE INNODB STATUS) is available using this: SELECT trx_id FROM information_schema.innodb_trx WHERE trx_mysql_thread_id = CONNECTION_ID(); This is available in MySQL 5.5 and up (and possibly with the InnoDB plugin on 5.1), but ...
The Slave should be irrelevant, unless you are using (misusing) some form of parallel execution on the Slave. Try other transaction_isolation_modes. GET_LOCK()/RELEASE_LOCK() may provide a sufficient "lock"; but all writers to that table(s) would need to use it. Do not turn on auto-reconnect in your connection. An network glitch will mess with both the ...
Top 50 recent answers are included