Showing entries 1 to 10 of 42
10 Older Entries »
Displaying posts with tag: transactions (reset)
MySQL 8.0 - auto increment feature gets fixed

How InnoDB initializes AUTO_INCREMENT counters is actually not a bug, but a documented mechanism. There were some complaints and even people who lost data over this. To initialize an auto-increment counter after a server restart, InnoDB executes the equivalent of the following statement on the first insert into a table containing an AUTO_INCREMENT column. SELECT MAX(ai_col) FROM table_name FOR

Introduction into storage engine troubleshooting: Q & A

In this blog, I will provide answers to the Q & A for the “Introduction into storage engine troubleshooting” webinar.

First, I want to thank everybody for attending the July 14 webinar. The recording and slides for the webinar are available here. Below is the list of your questions that I wasn’t able to answer during the webinar, with responses:

Q: At which isolation level do 

pt-online-schema-change

 and 

pt-archive

  copy data from a table?

A: Both tools do not change the server’s default transaction isolation level. Use either

REPEATABLE READ

[Read more]
Q: Does MySQL support ACID? A: Yes

I was recently asked this question by an experienced academic at the NY Oracle Users Group event I presented at.

Does MySQL support ACID? (ACID is a set of properties essential for a relational database to perform transactions, i.e. a discrete unit of work.)

Yes, MySQL fully supports ACID, that is Atomicity, Consistency, Isolation and Duration. (*)

This is contrary to the first Google response found searching this question which for reference states “The standard table handler for MySQL is not ACID compliant because it doesn’t support consistency, isolation, or durability”.

The question is however not a simple Yes/No because it depends on timing …

[Read more]
Configuring and testing MySQL binary log

The binary log contains “events” that describe database changes. On a basic installation with default options, it's not turned on. This log is essential for accommodating the possible following requirements:

Replication: the binary log on a master replication server provides a record of the data changes to be sent to slave servers. Point in Time recovery: allow to recover a database from a full

MySQL replication in action - Part 3: all-masters P2P topology

Previous episodes:

MySQL replication in action - Part 1: GTID & CoMySQL replication in action - Part 2 - Fan-in topology


In the previous article, we saw the basics of establishing replication from multiple origins to the same destination. By extending that concept, we can deploy more complex topologies, such as the point-to-point (P2P) all-masters topology, a robust and …

[Read more]
Tracking MySQL query history in long running transactions

Long running transactions can be problematic for OLTP workloads, particularly where we would expect most to be completed in less than a second. In some cases a transaction staying open just a few seconds can cause behaviour that is entirely unexpected, with the developers at a loss as to why a transaction remained open. There are a number of ways to find long running transactions, luckily versions of MySQL from 5.6 onwards provide some very insightful instrumentation.

Here we will use the information_schema coupled with the …

[Read more]
Improvements for XA in MySQL 5.7

Today I was doing some tests with XA transactions in MySQL 5.6.

The output of the XA RECOVER command to list transactions was hard to read because of the representation of the data column:

The good news is that 5.7 has transaction information in performance_schema:

mysql> select trx_id, isolation_level, state, xid, xa_state, access_mode 
-> from performance_schema.events_transactions_current;
+-----------------+-----------------+--------+--------+----------+-------------+
| trx_id | isolation_level | state | xid | xa_state | access_mode |
+-----------------+-----------------+--------+--------+----------+-------------+
| NULL | REPEATABLE READ | ACTIVE | x-1 | PREPARED | READ WRITE |
| 421476507015704 | REPEATABLE READ | …
[Read more]
When your query is blocked, but there is no blocking query - Part 3

In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions.

In this post I will show you how to get even more information about what is locked by a transaction.

As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the documentation says:
"The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another …

[Read more]
When your query is blocked, but there is no blocking query - Part 2

In my previous post I talked about a transaction which blocked other transactions without doing anything. I talked about finding data from the blocking transaction using SYS and performance_schema.

But what are the possible solutions?

The first solution is to (automatically) kill the blocking transactions. Long running transactions can also stall the purging in InnoDB. See this blog post by Mark Leith about a possible solution.

The second solution would be make the application end the transaction sooner and/or to commit more often. Depending on your application this might or might not work. I consider this the best solution.

The …

[Read more]
Introducing TokuMX Transactions for MongoDB Applications

Since our initial release last summer, TokuMX has supported fully ACID and MVCC multi-statement transactions. I’d like to take this post to explain exactly what we’ve done and what features are now available to the user.

But before beginning, an important note: we have implemented this for non-sharded clusters only. We do not support distributed transactions across different shards.

At a high level, what have we done?

We have taken MongoDB’s basic transactional behavior, and extended it. MongoDB is transactional with respect to one, and only one, document. MongoDB guarantees single document atomicity. Journaling provides durability for that document. The database …

[Read more]
Showing entries 1 to 10 of 42
10 Older Entries »