If I have done functional partitioning in my database and have for example a database Transactions and a database Products and these are different database servers and there are references from tables in the Transactions schema to tables in the products: If I have a transaction that involves/updates/deletes rows from tables both databases is that executed within a two phase commit protocol and is that done automatically under the hood by MySQL or some how the client program has to be involved? In my understanding the client program has to be involved in XA transactions but my scenario is not of that kind. So how would that work?

I am working with PostgreSQL version 11, and would like to turn off autocommit. Either permanently or per query through a PL/PSQL command. In the documentation it says it is turned off by default, elsewhere it says it is turned on by default. Not even the documentation is right about this, as psql does not recognize the config-parameter "autocommit", though the documentation mentions this variable explicitly. Does anyone know how to turn it off? (I am just running tests and "autocommit" might be one of the reasons that hinders performance)

I would like to know more about what happens behind the scenes with an auto-commit transaction when performing a cross-server query on a linked server. I naïvely think that when executing an auto-commit transaction the compiler/SQL Server/something else just prepends all statements with a BEGIN TRANSACTION and appends all statements with a COMMIT TRANSACTION since everything is technically enclosed in a transaction (https://dba.stackexchange.com/questions/43254/is-it-a-bad-practice-to-always-create-a-transaction?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa) . I'm sure this is incorrect and the source of my confusion as to why performing a cross-server UPDATE without explicitly stating BEGIN TRANSACTION works but explicitly stating one does not. According to Microsoft (https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?view=sql-server-2017) , an explicit BEGIN TRANSACTION on an UPDATE query that references a table on a linked server gets escalated to a distributed transaction; and since distributed transactions aren't configured on the linked server, I receive an error. How does the auto-commit setting avoid this? How does it not get escalated to a distributed transaction? Does the auto-commit setting send the data to the linked server but doesn't "listen" for a response from the linked server via Microsoft Distributed Transaction Coordinator (MS DTC); thus if an error occurs, it "silently" fails? Auto-commit doesn't escalate to a distributed transaction: UPDATE l SET l.RecordKey = s.RecordKey FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s ON l.Value1 = s.Value1; Explicit transaction does escalate to a distributed transaction (and errors in my case): BEGIN TRANSACTION UPDATE l SET l.RecordKey = s.RecordKey FROM LinkedServer.ExampleDatabase.dbo.ExampleTable AS l INNER JOIN ServerWithActiveConnection.ExampleDatabase.dbo.ExampleTable AS s ON l.Value1 = s.Value1; COMMIT TRANSACTION; I should add that the statements were run in SQL Server Management Studio.

MySQL does not open a transaction, and initiates a request containing multiple SQLs. This time, is it a transaction, or is each SQL a transaction? Does not open a transaction means that a transaction will autocommit. How about the details?

I know this question has been asked before. I am new to PostgreSQL and would like to turn off "autocommit" forever. After some research, I need to add "\set AUTOCOMMIT off" in the ~/.psqlrc file. Where do I find this file ~/.psqlrc? in directory /var/lib/pgsql/13/data/ I do not find it. I created it "vi ~/.psqlrc" and inserted the command \set AUTOCOMMIT, but it did not work. What is missing here? I would be very happy about any tip. Many greetings, Ahmed

I have run below statement without begin then, show a warning message (**WARNING: there is no transaction in progress**) like below image. But actually that table was deleted. Why it is not roll backed? Is there any effect in future transaction on that table?

I'm trying to make sense from "autocommit" concept. Originally I've heart about it in the context of Java JDBC API [Connection.setAutoCommit(boolean autoCommit)](https://docs.oracle.com/javase/8/docs/api/java/sql/Connection.html#setAutoCommit-boolean-) Quick search revealed that the term is not specific to JDBC, PostgreSQL refer to "autocommit" too: https://www.postgresql.org/docs/current/sql-start-transaction.html > In the standard, it is not necessary to issue START TRANSACTION to start a transaction block: any SQL command implicitly begins a block. PostgreSQL's behavior can be seen as implicitly issuing a COMMIT after each command that does not follow START TRANSACTION (or BEGIN), and it is therefore often called **“autocommit”**. Other relational database systems might offer an **autocommit** feature as a convenience. If it is widespread **what is isolation level of autocommit mode**? Conceptually as we have only one SQL statement before implicit COMMIT *isolation levels almost make no sense for autocommit*. Only READ UNCOMMITTED might influence the result, other anomalies are defined in term of a sequence of SQL statements. Can I alter autocommit isolation level to / away from READ UNCOMMITTED?

I'm using RDS Aurora (MySQL 5.6) By default Auto Commit flag is enabled. I have a transaction as follows BEGIN; INSERT INTO .....;// STATEMENT 1 INSERT INTO ......;//STATEMENT 2 COMMIT; I'm expecting both the Inserts to be commited only when COMMIT is executed. But Statement 1 is Commiting as soon as it got executed. So I wanted to know What does BEGIN do here ?? I was assuming that BEGIN does the task of disabling Auto Commit , Can someone also explain the difference between BEGIN and START TRANSACTION as well.

Is it better to use (for perfomance tuning) AUTOCOMMIT = 0 before bulk inserts of data in MySQL, if the insert query looks like INSERT INTO SomeTable (column1, column2) VALUES (val1,val2),(val3,val4),... ?

I'm importing around 9 million rows into my MySQL DB using the following command: SET autocommit=0; SOURCE my_import.sql; COMMIT; The command line is no longer outputting lines of Query OK, X rows affected..., and SELECT COUNT(*) query confirms that all the rows are there. Additionally, I'm no longer seeing an increase in my database size and the CPU/IOPS activity is very low. However, I'm anxious that the command line cursor has not returned: What could be going on here? Is it safe to assume that my import has finished and I can kill the terminal?

If a single application thread sends a series of INSERT statements and autocommit=true, will the statements be executed synchronously on the server? I read that on the postgres server side, "there is one client process connected to exactly one server process", but haven't found much more beyond that. I need synchronous execution to satisfy foreign key relationships, but I suspect the server (9.6) may run the statements concurrently.

Currently long_query_time is set to 5, and that works. However, many queries are part of a transaction, and the slow query log only keeps the final commit;. # Time: 2018-08-15T15:18:18.205309Z # User@Host: bijin[bijin] @ localhost [127.0.0.1] Id: 3274529 # Query_time: 7.510110 Lock_time: 0.000000 Rows_sent: 0 Rows_examined: 0 use interferences_db; SET timestamp=1534329958; commit; # (*or at least some part of it allowing to identify the culprit query*) *~~~ edit ~~~* The answer from @AmitPatel had me perform some tests using set autocommit = 0 instead of start transaction to initiate a transaction and, indeed, with autocommit = 0 the slow query log has the details of every slow query, even if they're eventually rollbacked. So, in order to log slow queries that are part of a transaction, can I safely replace, in the code, start transaction .... commit / rollback with set autocommit = 0 .... commit or rollback and set autocommit = 1 ? *( The final autocommit = 1 is to keep the same behavior in the code, after a transaction is rollbacked or committed )*

I am trying to understand the relationship between the concepts of *transaction chaining* and *autocommit*. Autocommit is usually exposed in a client-side programmatic interface. E.g. in JDBC with the Connection#setAutoCommit method. Namely I would like to know whether: 1. these concepts are simply aliases of one another (and what is the correspondence) or 2. these concepts are orthogonal and all 4 combinations make sense and yield different results (and what these results are) There's a number of resources that suggest the case is 1. The same resources seem to suggest that unchained corresponds to autocommit on. E.g. this post says that: > set chained off (default in Sybase, = set autocommit on) This more extensive post similarly says: > Some database programming interfaces have an autocommit mode, also called unchained mode. In this mode, each statement is a transaction, and is committed after execution. If you wish to use transactions in your applications, you need to be using manual commit mode, or chained mode. This has been my own understanding so far. For example, whenever I try to execute (using JDBC) a stored procedure and I run into the following message: > java.sql.SQLException: Stored procedure 'whatever' may be run only in unchained transaction mode. ... then setting in my code: conn.setAutoCommit(true); ... fixes the problem which seems to confirm that indeed *unchained* is the same as *autocommit*. However looking at the Sybase ASE documentation one reads: > The default mode, called unchained mode or Transact-SQL mode, requires explicit begin transaction statements paired with commit transaction or rollback transaction statements to complete the transaction. This seems to be the exact opposite of *autocommit* - unless the above text is understood as having the following meaning: > The default mode, called unchained mode or Transact-SQL mode, requires > explicit begin transaction statements paired with commit transaction > or rollback transaction statements to complete the transaction **when one wants to place more than a single statement in a transaction**. Finally, I've come across this post which suggests that *chaining* and *autocommit* are mostly orthogonal.

I have a table with composite unique key(uniqueness on combination of 3 columns.) Table structure: CREATE TABLE userreview ( cid bigint(12) unsigned NOT NULL, conid bigint(12) unsigned NOT NULL, userid bigint(12) unsigned NOT NULL, flag int(12) unsigned NOT NULL DEFAULT '0', updatedat timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3), UNIQUE KEY idx_userreview_cid_conid_userid (cid,conid,userid) ) ; Below is stored procedure . CREATE PROCEDURE testdeadlock(IN pconid BIGINT(12), IN pcid BIGINT(12), IN puserid BIGINT(12),IN pflag INT(1)) BEGIN DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING BEGIN ROLLBACK; RESIGNAL; END; START TRANSACTION; IF pflag = 1 THEN INSERT ignore into userreview(cid,conid,userid,flag) VALUES(pcid,pconid,puserid,1) ON DUPLICATE KEY UPDATE flag = 1; /*update operation in table 1*/ ELSEIF pflag = 0 THEN INSERT ignore into userreview(cid,conid,userid) VALUES(pcid,pconid,puserid) ON DUPLICATE KEY UPDATE flag = 0; /*update operation in table 2*/ ELSEIF pflag = 2 THEN INSERT ignore into userreview(cid,conid,userid) VALUES(pcid,pconid,puserid) ON DUPLICATE KEY UPDATE flag = 2; /*update operation in table 3*/ end if; COMMIT; end; When this procedure is executing from client side(using node.js),sometime it occurs deadlock when concurrent transactions are going to execute on same rows.Though there is unique index still facing deadlock problem. Is there any suggestion to avoid deadlock? Deadlock result:- LATEST DETECTED DEADLOCK ------------------------ 2018-07-06 16:55:51 0x2b0b08e99700 *** (1) TRANSACTION: TRANSACTION 102648166, ACTIVE 0 sec inserting mysql tables in use 2, locked 2 LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s) MySQL thread id 108948, OS thread handle 47323865503488, query id 242017676 10.0.3.130 numbertankroot update INSERT ignore into userreview(cid,conid,userid,flag) VALUES( NAME_CONST('pcid',8157), NAME_CONST('pconid',3158), NAME_CONST('puserid',85217)) ON DUPLICATE KEY UPDATE flag = 0 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 735 page no 17308 n bits 256 index idx_userreview_cid_conid_userid of table userreview trx id 102648166 lock_mode X waiting Record lock, heap no 183 PHYSICAL RECORD: n_fields 15; compact format; info bits 0 0: len 8; hex 0000000000001fdd; asc ;; 1: len 8; hex 000000000005af9f; asc ;; 2: len 8; hex 00000000000bd0b7; asc ;; 3: len 6; hex 0000061e4965; asc Ie;; 4: len 7; hex 2800000eb42873; asc ( (s;; 5: len 1; hex 80; asc ;; 6: len 1; hex 84; asc ;; 7: len 4; hex 00000001; asc ;; 8: len 1; hex 01; asc ;; 9: len 8; hex 0000000000001fdd; asc ;; 10: len 4; hex 00000000; asc ;; 11: len 4; hex 00000000; asc ;; 12: len 4; hex 00000001; asc ;; 13: len 6; hex 5b3f9f170000; asc [? ;; 14: len 6; hex 5b3f9f17213e; asc [? !>;; *** (2) TRANSACTION: TRANSACTION 102648170, ACTIVE 0 sec inserting mysql tables in use 2, locked 2 4 lock struct(s), heap size 1136, 2 row lock(s) MySQL thread id 108960, OS thread handle 47326394160896, query id 242017689 10.0.3.130 numbertankroot update INSERT ignore into userreview(cid,conid,userid,flag) VALUES( NAME_CONST('pcid',8157), NAME_CONST('pconid',3158), NAME_CONST('puserid',85217)) ON DUPLICATE KEY UPDATE flag = 1 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 735 page no 17308 n bits 256 index idx_userreview_cid_conid_userid of table userreview trx id 102648170 lock mode S locks rec but not gap Record lock, heap no 183 PHYSICAL RECORD: n_fields 15; compact format; info bits 0 0: len 8; hex 0000000000001fdd; asc ;; 1: len 8; hex 000000000005af9f; asc ;; 2: len 8; hex 00000000000bd0b7; asc ;; 3: len 6; hex 0000061e4965; asc Ie;; 4: len 7; hex 2800000eb42873; asc ( (s;; 5: len 1; hex 80; asc ;; 6: len 1; hex 84; asc ;; 7: len 4; hex 00000001; asc ;; 8: len 1; hex 01; asc ;; 9: len 8; hex 0000000000001fdd; asc ;; 10: len 4; hex 00000000; asc ;; 11: len 4; hex 00000000; asc ;; 12: len 4; hex 00000001; asc ;; 13: len 6; hex 5b3f9f170000; asc [? ;; 14: len 6; hex 5b3f9f17213e; asc [? !>;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 735 page no 17308 n bits 256 index idx_userreview_cid_conid_userid of table userreview trx id 102648170 lock_mode X waiting Record lock, heap no 183 PHYSICAL RECORD: n_fields 15; compact format; info bits 0 0: len 8; hex 0000000000001fdd; asc ;; 1: len 8; hex 000000000005af9f; asc ;; 2: len 8; hex 00000000000bd0b7; asc ;; 3: len 6; hex 0000061e4965; asc Ie;; 4: len 7; hex 2800000eb42873; asc ( (s;; 5: len 1; hex 80; asc ;; 6: len 1; hex 84; asc ;; 7: len 4; hex 00000001; asc ;; 8: len 1; hex 01; asc ;; 9: len 8; hex 0000000000001fdd; asc ;; 10: len 4; hex 00000000; asc ;; 11: len 4; hex 00000000; asc ;; 12: len 4; hex 00000001; asc ;; 13: len 6; hex 5b3f9f170000; asc [? ;; 14: len 6; hex 5b3f9f17213e; asc [? !>;; *** WE ROLL BACK TRANSACTION (1)

I have a few large CSV files to upload to an instance of SQL Server.(Nope, can't use SSMS) I'm using JDBC to read the CSVs line by line and insert the lines in a table in batches. Supposing my code is as such: val connection = DriverManager.getConnection() connection.setAutoCommit(false) val stmt = connection.prepareStatement("insert into blah_table values(?,?,?,?,?,?,?)") while() { //populate the stmt with values stmt.setObject(x,y) // add a batch stmt.addBatch() } stmt.executeBatch() connection.commit() Say the CSV file had some 20000 lines with some 50-60 or more columns. I encountered an error by chance where the connection to SQL Server was lost while connection.commit() was being executed. This resulted in ~11000/20000 lines being inserted. I went through my application several times hoping to find some error I might have made. Everything appears to be fine. My question is, is it possible that if the connection is lost in between committing, partial commits are left inserted? If so, what could be a way out to save from partial commits?

Out of curiosity: For example, I issue commit at the end of a transaction and wait for the response from the database. If for some reason, the waiting times out, how can I know if this transaction has been successfully committed and thus made durable by the DB? I supposed 3 possible reasons for the timeout: - The commit command is not delivered to the database. - The commit command is well delivered to the database but for some reason not processed by the DB and DB is just stuck there. - The commit command is well delivered to the database and processed by the DB, but for some reason, the response from the database cannot be delivered to my client application.

I have a database that already have a lot of changes for weeks and the autocommit has been disabled. Today we restarted mysql service, will those changes be rollbacked? We haven't executed COMMIT for weeks now. Or will the rollback only applies to the last query?

I am pretty new to MySql and Databases, and I was wondering in python sometimes I forgot to commit after an insert, and then when I do then next insert and commit that one the, auto-incrementing id column has skipped a number. So I was wondering what happens when you insert and forget to commit is this bad for the Database? Or does is just skip because when you insert it assigned an id to it but didn't save it? Thanks

AFAIK running transactions as READ UNCOMMITTED means that there is no lock, so every change caused by a transaction is immediately visible to other transactions. Is it correct to assume that this is the same behaviour we get with any transaction if we enable AUTOCOMMIT? AFAIK in this case, as well, the effects of any query are immediately visible to others. Of course, the two concepts are somewhat different, semantically, but is there any difference in what's actually happening? I'm interested in a general answer, though I do tend to care more about MySQL and Microsoft SQL Server.

I am using an H2 database with JDBC to create a connection with setAutoCommit(false). I am currently programatically checking whether or not there is an uncommitted transaction, but I was wondering if there was a way to directly query H2 for information about this state. I have found some information for other databases on this topic, but nothing so far on H2.