MySQL to an Oracle DBA, Part IV

Today is where the rubber meets the road in the Oracle/MySQL comparison; locking and transactions. At the end, I'll give you a little Oracle fun.

As I said yesterday, the different storage engines may or may not support transactions. MyISAM tables do not support transactions while InnoDB tables do. First, I create two tables; one as InnoDB, one as MyISAM:

system@localhost:world> create table city_myisam 
   engine = myisam as select * from city;
Query OK, 4063 rows affected (0.78 sec)
Records: 4063  Duplicates: 0  Warnings: 0

system@localhost:world> create table city_innodb 
    engine = innodb as select * from city;
Query OK, 4063 rows affected (0.53 sec)
Records: 4063  Duplicates: 0  Warnings: 0

Next, I set autocommit off.

system@localhost:world> set autocommit = 0;
Query OK, 0 rows affected (0.06 sec)

Now I'll delete some rows from the tables in a transaction and see what happens:

system@localhost:world> begin;
Query OK, 0 rows affected (0.00 sec)

system@localhost:world> delete from city_myisam 
   where country = 'FRA';
Query OK, 40 rows affected (0.11 sec)

system@localhost:world> delete from city_innodb 
   where country = 'FRA';
Query OK, 40 rows affected (0.09 sec)

system@localhost:world> rollback;
Query OK, 0 rows affected, 1 warning (0.05 sec)

system@localhost:world> show warnings;
+---------+------+---------------------------------------------------------------+
| Level   | Code | Message                                                       |
+---------+------+---------------------------------------------------------------+
| Warning | 1196 | Some non-transactional changed tables couldn't be rolled back |
+---------+------+---------------------------------------------------------------+
1 row in set (0.00 sec)

system@localhost:world> select count(*) from city_innodb 
   where country = 'FRA';
+----------+
| count(*) |
+----------+
|       40 |
+----------+
1 row in set (0.08 sec)

system@localhost:world> select count(*) from city_myisam 
   where country = 'FRA';
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.27 sec)

Aha, MySQL gives me a warning, but does it anyway. Note to self: no money transactions in MyISAM tables.

The next interesting thing was locking. The MyISAM storage engine only uses table level locks. When you update a row in a MyISAM table, you have to get an exclusive table level lock before you can update. These table level locks severly limit MyISAM scalability in a write intensive envrionment. The InnoDB engine, on the other hand, has row level locks, like Oracle.

The biggest Oracle misstatement today: Oracle will escalate to a table level lock when you delete more than 25% of the rows. Nope. In fact, I have a table with 5000 rows where the pk ranges from 1 to 5000:

SQL> l
  1  select count(*),
  2     min(x),
  3     max(x)
  4* from xyz
SQL> /

  COUNT(*)     MIN(X)     MAX(X)
---------- ---------- ----------
      5000          1       5000

In one session, I delete the first 4999 rows:

SQL> delete from xyz where x < 5000;

4999 rows deleted.

No problem. Now, from a different session I try to delete the row where x=4000.

SQL> delete from xyz where x=4000;

And, as expected, we are waiting on a lock from the first session:

SQL> l
  1  select waiting_Session wt, holding_session ho, lock_type lt,
  2     mode_held, mode_requested, lock_id1, lock_id2
  3* from dba_waiters
SQL> /

  WT   HO LT           MODE_HELD  MODE_REQUE LOCK_ID1 LOCK_ID2
---- ---- ------------ ---------- ---------- -------- --------
  24   25 Transaction  Exclusive  Exclusive    262148      163

But, if we try to delete the row that is not locked, it should go through with no issues.

SQL> delete from xyz where x=5000;

1 row deleted.

No problem, with no locks:

SQL> select waiting_Session wt, holding_session ho, lock_type lt,
  2     mode_held, mode_requested, lock_id1, lock_id2
  3  from dba_waiters
  4  /

no rows selected