MySQL and update statements

I recently had to do an SQL query that performed a 2x2 matrix multiplation where each field was represented by a column (e.g. column; a, b, c and d).

I then found out, that there's a bit of atomicity lacking when using MySQL. Depending on the way you view it, it can seem intuitive, however it can cause some problems when/if you're assuming the opposite, and/or when other db's act differently (e.g. Postgres).

The easiest example to consider, is swapping the value of two columns.

INSERT INTO mytable (id, col1, col2) VALUES(1, 500, 0);
UPDATE mytable SET col1 = col2, col2 = col1 WHERE id = 1;

Try to guess: What is the value of col2 after this operation?

The answer: It depends on which database you use.

My instincts told me that col2 would contain 500 after the operation. That is true for Postgres, but not true for MySQL. In MySQL col2 would contain 0.

I could imagine, that MySQL does this for reasons of performance, i.e. direct in-place updating of the column or something, but that's just me guessing.

In any case, there are several ways to work around this. At first, I introduced a buffer column to contain the old column value, e.g.:

UPDATE mytable SET tmp = col1, col1 = col2, col2 = tmp WHERE id = 1;

However, seeing that this may only be MySQL specific, I'm thinking about using a @variable instead, as this may be more efficient.