Tag Info

New answers tagged

0

A SAVEPOINT is not a commit. It doesn't "save" anything other than the "point" where you are, in the transaction, when you create it... allowing you to roll back to the savepoint, if you need to, without rolling back the entire transaction. You can't alter a table in another connection while one or more transactions is holding locks against it -- the other ...


1

As a matter of best practices, transactions should be kept as short as possible and never wait for user interaction; every time you perform some type of data or schema modification within a transaction, this places locks on the objects or rows that have been touched/modified, which keeps other users' queries waiting. This is turn can create chain effects ...


3

It looks like the order of the statements is causing the problem. In my old post row locking within ACID transaction innodb, I named 12 statements that break a transaction intermittently. In your particular case, it was the CREATE TABLE statement. Once you ran CREATE TABLE inside a START TRANSACTION ... COMMIT/ROLLBACK block, there was no framework to ...


9

The reason is that that some statements, like CREATE TABLE cause an implicit commit. You can read about them in the documentation: Statements That Cause an Implicit Commit. So the original sequence of statements: START TRANSACTION SHOW TABLES LIKE customers CREATE TABLE `customers__20150119_14_08_20` LIKE `customers` INSERT INTO ...


2

Yes, it's possible if you change the transaction isolation level for the session (that's what you call "window" in SSMS) that queries modified data. Often this is not such a great idea, as you might get some unexpected results. Consider the side effects carefully. I have no idea if it's possible to change the transacion isolation level in the Excel Power ...


0

I know this is an old thread but I would say to a large degree snapshot isolation is a magic bullet. It will eliminate all of your blocking between readers and writers. It will however not prevent writers from blocking other writers. There is no way around that. In my experience, the additional load on the TEMPDB is negligible and the benefits of row ...


1

There are some caveats that apply to mixing transactional and non-transactional tables in the same query, but in this case, you're actually doing it because the side effects are desirable... so I see no problem with the following suggestion: The MEMORY storage engine seems like a good fit for this application. It's non-transactional, so your "status" ...


0

Could you explain the context a bit more, please? Do you mean the average number of rows a SQL query returns? This helps you to find out if an SQL query was executed well. An SQL query that returns 50 rows but does a full table scan over 10mil rows is not good. An SQL query that returns 8mil rows with a index access is not good either. So this helps you to ...


2

Not exactly an answer to your question but amount of changes generated by the session can be found using such query. select st.inst_id, trunc(st.value/(1024*1024)) as MB, ss.sid, ss.username, ss.osuser, ss.machine, ss.program, ss.logon_time from gv$sesstat st, v$statname sn, gv$session ss where st.statistic# = sn.STATISTIC# and sn.name = 'redo size' and ...


4

This is a session setting; there is nothing to roll back. If you had issued SET DATEFORMAT DMY; or SET LANGUAGE FRENCH;, would you expect an error to revert you to MDY or english_us? What if you had SET SHOWPLAN on, would you expect a rollback to stop showing you plans on subsequent queries? To be fair, the IDENTITY_INSERT case is a little special, since ...


2

There is more than one possibility here. One is what Peter Eisentraut suggested in the post you linked, using a writable CTE which deletes the rows from the table which will be processed or inserted elsewhere in the same query. This approach may be viable with small datasets. However, there are chances that some table bloat will occur with time, or ...


0

This Truncate process held highest level lock on tables, so no other query can run on the table in parallel with the truncate. So, if You do : truncate table a and after that, You do : insert into table a values (....) What happen in the postgres is : insert will run only after truncate finish. So no new data will get lost. And in contrary, if You ...


3

Before you run your update: SELECT @@SPID; Now, run the update, if it is not completing quick enough, then in another window run: SELECT status, command, wait_type, last_wait_type, blocking_session_id FROM sys.dm_exec_requests WHERE session_id = <@@SPID from above>; If you get a value in blocking_session_id, then you can run the same query ...


1

Oracle has spoken: http://docs.oracle.com/cd/B19306_01/server.102/b14231/ds_txnman.htm#i1008473 Distributed Query Returns Different Results on Consecutive Runs when no Changes Exist in the Base Tables. (Doc ID 561471.1) ...skip some more writing... Bug.611416 SELECT AFTER UPDATE AND COMMIT DOESN'T SHOW UPDATED COLUMNS ...skip some more ...



Top 50 recent answers are included