MySQL 5.0 Cluster - Locking issues

[alejandro robles]

Hi,

I'm using the NDB Storage Engine to build up an application cluster. When testing the application I have noticed that row locking doesn't work as I was expecting.

To put it simple, I have one table called 'radippool' defined as follows:

mysql> desc radippool;
+--------------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------------------+------+-----+---------+----------------+
| id | bigint(21) unsigned | NO | PRI | | auto_increment |
| pool_name | char(48) | NO | MUL | | |
| ip_address | char(15) | NO | UNI | | |
| calling_station_id | char(32) | NO | MUL | | |
| expiry_time | datetime | NO | | | |
| username | char(32) | YES | | | |
| SGSNIPV4Address | char(15) | NO | | | |
| NASIPAddress | char(15) | NO | | | |
+--------------------+---------------------+------+-----+---------+----------------+
8 rows in set (0.00 sec)

The indexes created are:

mysql> show indexes from radippool;
+-----------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
| radippool | 0 | PRIMARY | 1 | id | A | 33526 | NULL | | | BTREE | |
| radippool | 0 | ip_address | 1 | ip_address | | 33526 | NULL | | | HASH | |
| radippool | 1 | poolexp | 1 | pool_name | A | NULL | NULL | | | BTREE | |
| radippool | 1 | poolexp | 2 | expiry_time | A | 33526 | NULL | | | BTREE | |
| radippool | 1 | calling_station_id | 1 | calling_station_id | A | 33526 | NULL | | | BTREE | |
+-----------+------------+--------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+
5 rows in set (0.00 sec)

Then I open two mysql sessions towards the same SQL server (the same happens if I access the cluster from two different ones). On each of the sessions I set autocomit off. Thereafter I run the following query on both of them:

mysql> select * from radippool where expiry_time < NOW() limit 1 for update;
+-------+---------------+-------------+--------------------+---------------------+----------+-----------------+--------------+
| id | pool_name | ip_address | calling_station_id | expiry_time | username | SGSNIPV4Address | NASIPAddress |
+-------+---------------+-------------+--------------------+---------------------+----------+-----------------+--------------+
| 22638 | test2_GGSN103 | 10.10.4.165 | | 2006-06-13 14:34:10 | | | |
+-------+---------------+-------------+--------------------+---------------------+----------+-----------------+--------------+
1 row in set (0.01 sec)


And in both cases I get the same result i.e. the retrieved row is not LOCKED!!! leading to inconsistent results.

I was expecting the row to be locked and the second query to either wait for the lock to be released or just skip this row and continue (getting the next one available).

What am I doing wrong? I have a hard time trying to dig into the problem since I can’t hardly find documentation on how locking and transactions work on NDB engine nor what commands are available that show the locks within the storage engine at a given point in time.

Do you have any clues of what's going on?

Thanks very much for you advice!

Regards,
Alex.