As one of the three winners I’d like to thank the entire MySQL team and congratulate my co-winners, Roland Bouman and Beat Vontobel.

I’d also like to thank other honourable Community members that I had the pleasure to have contact with, like Andrew Gilfrin, Giuseppe Maxia and Jay Pipes. These people and the MySQL company have all done great work and I’m sure that a lot of great things are yet to come :-). You never make life boring.

This prize is a big honour – it’s not such a long time that I have been active in the Community, maybe half a year or so. I still consider myself to be at the beginning and I never thought it could happen so quickly that I would be recognized and awarded from the MySQL team and other Community members. I don’t have decades of development experience like some others (actually, I started programming about 2 1/2 years ago) and I’m still discovering different techniques and ways how to become a REALLY skilled developer. I believed (and still believe), I was at the beginning of a very long way – so it’s absolutely a phantastic and motivating thing to be rewarded so extremely soon in a worldwide contest from the ‘World’s Most Popular Open Source Database’ company.

So this shows, don’t be afraid if you are new to MySQL and new in the MySQL Community. It doesn’t only take people with many years of experience, there’s also a lot that “fresh” people can do to help!

Well, what are my current plans and activities? My next big step will hopefully be to pass the MySQL Professional Exam, which I plan to take around the middle of December (it requires tough work, it’s definetly no easy thing, but there was a lot to learn which I probably wouldn’t have learnt without taking the Core and soon the Professional exam). At the moment I, together with Roland Bouman, am writing an article and a code sample (that includes all the common methods that exist) about data access to MySQL using C#.NET for Andrew’s mysqldevelopment.com site.

I also have some ideas for db4free.net, so if time allows me, there could be a completely new version with new possibilities next year. It’s hard to promise it, because you never know, what’s coming up – but I’ll try hard to make it real :-). At least, the great feedback I received is very motivating and inspiring!

Well, and finally there’s my current series in my weblog about Transaction Isolation Levels, which will soon continue with the article about the REPEATABLE READ. So stay tuned!

Lets continue this series about transaction isolation levels and table a look at READ COMMITTED.

We start with the same data and perform the same steps as we did with READ UNCOMMITTED and take a look at the differences. So prepare the table ‘tt’ that it looks like this:

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

Again, open 2 MySQL monitor windows and change the transaction isolation level of the 1st window (which we call CLIENT 1) to READ COMMITTED:

set session transaction isolation level read committed;

So let’s start with the first example. Client 1 starts a transaction and performs a SELECT statement. Then, client 2 updates row 2 to value 4.

CLIENT 1
========

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

CLIENT 2
========

mysql> update tt set d=4 where d=2;
Query OK, 1 row affected (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
+------+
3 rows in set (0.00 sec)

As we see, the result is exactly the same as with READ UNCOMMITTED. It’s still possible to see changes of the data applied by another client.

Now, client 2 inserts a row and client 1 performs another SELECT statement:

CLIENT 2
========

mysql> insert into tt values (5);
Query OK, 1 row affected (0.00 sec)

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.00 sec)

Again, there’s no difference to READ UNCOMMITTED. Finally client 2 also starts a transaction and updates and inserts a few rows:

CLIENT 2
========

mysql> start transaction;

Query OK, 0 rows affected (0.00 sec)


mysql> insert into tt values (6), (7);

Query OK, 2 rows affected (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0


mysql> update tt set d=8 where d=1;

Query OK, 1 row affected (0.00 sec)

Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.00 sec)

Here’s the difference between READ UNCOMMITTED and READ COMMITTED. While client 2 is in transaction mode, client 1 is not able to see the changes done by client 2.

To sum this up: READ COMMITTED allows non repeatable reads and phantom reads to happen, but in contrast to READ UNCOMMITTED, it does not allow dirty reads.

This series of articles will show you how the different transaction isolation levels work in practice. Most people who work with databases on a regular basis might have heared that there is a distiction how transactions can deal with concurrent reads and writes. However, their names and exact (but simular) functionality differ between the database systems. This shows, how the isolation levels work in MySQL.

To execute the examples, you need one simple InnoDB table and two opened MySQL monitor clients. With them, we can simulate slowly what in real world applications with multiple concurrent reads and writes can happen in less than a blink of an eye.

Here’s the definition of the table – very simple, indeed. Insert three rows with values from 1 to 3:

create table tt (d int) engine=innodb;
insert into tt values (1), (2), (3);

We start with the least restrictive isolation level, the READ UNCOMMITTED.

To output the current isolation level, execute the command

select @@tx_isolation;

To change the isolation level, use the command

set session transaction isolation level read uncommitted;

Now open 2 MySQL monitor clients and execute following commands:

CLIENT 1
========
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    2 |
|    3 |
+------+
3 rows in set (0.00 sec)

CLIENT 2
========

mysql> update tt set d=4 where d=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
+------+
3 rows in set (0.00 sec)

What we see here is a non repeatable read. After client 1 started the transaction and selected the data, client 2 changed one row. When client 1, still in transaction mode, performed a second SELECT statement, he got a different result.

We continue this example:

CLIENT 2
========

mysql> insert into tt values (5);
Query OK, 1 row affected (0.00 sec)

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.02 sec)

After client 2 inserts a row, client 1 can immediately see this new row, while he’s still inside the transaction. This is called a phantom read.

As this example continues, client 2 also starts a transaction:

CLIENT 2
========

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into tt values (6), (7);
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> update tt set d=8 where d=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    8 |
|    4 |
|    3 |
|    5 |
|    6 |
|    7 |
+------+
6 rows in set (0.00 sec)

As client 2 starts the transaction, inserts 2 rows and updates another one, client 1 can immediately see the changes. Now, client 2 performs a rollback on his transaction:

CLIENT 2
========

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

CLIENT 1
========

mysql> select * from tt;
+------+
| d    |
+------+
|    1 |
|    4 |
|    3 |
|    5 |
+------+
4 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

As client 2 rolls back his transaction, client 1 sees the same result as before client 2 started his transaction. This way, client 2 sees records that have never really existed in the table. This is called a dirty read.

I just came across an interesting article from John Berns about Compiling PHP5 with mysql and mysqli Database Drivers.

As Andrew Gilfrin told in his Weblog recently, it’s still hard to find web hosters who support the mysqli extension. I’m also struggeling with my web hoster to convince him to install the mysqli extension (and of course to update MySQL to 5.0) – but it’s always a long process until it actually happens. So maybe, this article is a great starting point for the administrators at web hosting companies to extend their offers. I’ve posted the link to this article into the forum of my web hoster – cross your fingers that it helps to speed things up.