Tag Info

New answers tagged

1

Is it ok to use the word "commit" for nested transactions? I think so. The syntax is still COMMIT TRANSACTION or COMMIT WORK. How 'commits' of nested transactions are handled is implementation-dependent, but it seems common to treat them as a no-op. Handling nested rollbacks is much more interesting. In SQL Server at least, it requires marking a ...


3

I don't think there is a problem using the word commit and personally I don't see a better wording for the concept. Most database systems do not support nested transactions, the only one I could find is BerkelyDB. In their documentation on nested transactions they are using commit as the term. Committing a nested transaction has no effect on the state ...


1

The easiest way is to look into the transaction log and see where there is a sequence that you can 'split'. Start by reading How to read and interpret the SQL Server log to understand what I'm talking about. Then look into your DB log for a sequence like this: lsn1 BEGIN XACT xid1 lsn2 some update lsn3 COMMIT xid1 lsn4 BEGIN XACT xid2 lsn5 some ...


4

To make sure that this (and other) foreign keys are present in the database I open a REPEATABLE READ transaction and check them one by one for existence before I attempt to insert the task. I then compile a list of those that are absent (if any) and I roll back the transaction and return these back so that the API can form a meaningful error message. ...


7

Having run this through a debugging session (to refresh my failing memory): Rollback does more checks than a commit, but it shouldn't result in additional work or have a noticeable affect on performance in the situation you describe. The read-write transaction doesn't truly begin unless and until a data modification is made. You can see much of this ...


0

This behaviour seems to be caused by the option to auto commit after each command in dbisql configurable via Tools->Options->SQLAnywhere->Execution->"Commit after every statement". If this option is set, I can reproduce the error described at the question. Unchecked it's working as expected.


3

For many people, the MySQL Achilles' heel is implicit commit. According to Page 418 Paragraph 3 of the Book the following commands can and will break a transaction ALTER TABLE BEGIN CREATE INDEX DROP DATABASE DROP INDEX DROP TABLE RENAME TABLE TRUNCATE TABLE LOCK TABLES UNLOCK TABLES SET AUTOCOMMIT = 1 START TRANSACTION SUGGESTION When it comes to ...


1

Next time this happens, run sp_WhoIsActive (download | documentation) and see who is running what and look over the logic. Check to see if the TSQL can be optimized to run quicker or perhaps chop it up into smaller transactions. I've had cases where the transaction log of a bad query by a report writers, data loaders, etc. would grow the transaction log ...


1

Db2 has a transaction isolation level called "Chaos": http://www-01.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.swg.im.dbclient.adonet.ref.doc/doc/DB2ConnectionClassConnectionStringProperty.html Perhaps it's meant to be used for that.


0

The problem seems to be related to the amount of data present in the two tables. In local I had a small part of the dataset I had on RDS. Once I've dumped the RDS data and load that in local I got the same bahaviour. So, the flow seems to be: after the first transaction COMMIT, mysql takes some time to update the table based on the number of its rows ...


3

I try to make an hypothesis about the second question: Given the commit happens before the select (commit is at 14:16:06 and select is at 14:16:07) how is it that the select does not return the row inserted by the transaction? Transactions are managed by Spring. So it would be possible that before running the select spring has raised a start ...


1

The output is only showing the current query in a transaction. What else is in the BEGIN...COMMIT? Or are you running autocommit=0? Or =1? SELECT grabs shared locks on the rows it is looking at, for the duration of the transaction. INSERT grabs exclusive lock(s). It sounds like the SELECT already has a lock that the INSERT desires. Perhaps the SELECT ...



Top 50 recent answers are included