I have server that uses transactions to write data to database and if all the queries are successful it will commit otherwise it will rollback. Now I want to have two instances of the server work at the same time on the same database and tables.
When I was reading mysql's transaction documentation I noticed this sentence: "Beginning a transaction causes any pending transaction to be committed". Does this mean that if server A start transaction A and while transaction A is not completed yet, the server B start transaction B, transaction A is forced to commit? This doesn't make sense to me. If this is the case how I can insure that transaction B is not executed until transaction A is completed normally? Would SET autocommit = 0 be an alternative to this problem?
Assuming when you say you want two instances of the server to work at the same time you mean two separate sessions running on the same server.
The sentence "Beginning a transaction causes any pending transaction to be committed" refers to any pending transaction within the same session only. From http://dev.mysql.com/doc/refman/5.7/en/implicit-commit.html
The statements listed in this section (and any synonyms for them) implicitly end any transaction active in the current session, as if you had done a COMMIT before executing the statement.
So, if Session B starts a transaction before Session A is committed it will not force Session A to commit.