7.3.4  Locking

Some MySQL applications when ported to DB2 appear to behave identically, and 

the topic of concurrency can be ignored. However, if your applications involve 

frequent access to the same tables you may experience a different behavior. By 

default, MySQL runs in a mode that is called 

autocommit

. This means that 

MySQL considers each and every SQL statement as an atomic 

unit of work 

or 

transaction. 

In contrast, DB2 by default considers a group of SQL statements with the 

corresponding unit of work boundaries set by a commit respectively a rollback 

statement as single or atomic transaction. Only certain interfaces such as the 

DB2 command line processor (CLP) or the JDBC interface run in autocommit 

mode. For all other application interfaces, autocommit is by default turned off.

Another matter causing heated discussions among experts is the level of locking 

that needs to be implemented on the database level. Should the locking 

approach be implemented with the lowest level of overhead, and therefore 

maintain locks on a table level? Or, is it better to lock on a lower level for example 

on page level? Should the granularity be even finer and locking occur on row 

level?

As usual, the correct answer to these questions is: 

it depends!

MySQL development decided to go the 

multi storage engine 

way and decided to 

implement lock levels based on the type of table. Table types can be mixed within 

a database and even a statement, and types can be altered. The default storage 

engine for MySQL supports only table level locking. MyISAM table, Merge and 

HEAP tables use a default storage engine and have table level locking. The 

InnoDB

 storage engine was released as a transactional table handler of MySQL 

with a lock manager for row level locking mechanisms. Hence, the MySQL table 

type InnoDB

defines tables most alike DB2 tables. In addition to the two storage 

engines already discussed, MySQL integrated the BDB or 

Berkley DB

table type. 

Table 7 11 gives a superficial comparison of the different flavors of MySQL tables 

with DB2 tables:

Table 7 11   MySQL and DB2 table comparison

Characteristics

DB2 tables

MyISAM 

InnoDB 

BDB tables

tables

tables

Lock level

Row level, 

None or table 

Row level 

Page level 

Table level 

level

and table 

and table 

only on explicit 

level

level

request

 Chapter 7. Application porting 

229