2 phase write?

[Sean Maurik]

Hi everyone. Doing some currency / load testing with our system and we're seeing something very odd that I'm hoping someone can help explain.

We've got a table with a datetime in it:
create table cas_rec (
id bigint not null auto_increment,
requestData text,
requestHeaders text,
type bigint not null,
receivedAt datetime,
statusCode integer,
WAPPushCode varchar(255),
comment varchar(255),
userID bigint not null,
appID bigint,
hostID bigint,
deviceID bigint,
primary key (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

As you can see we're using InnoDB for transactions. We're hitting this table with hibernate (3.03) using a few threads and what we're seeing is that under tight concurrency conditions we'll insert a row with one thread and then read it almost instantly with another and we find that the datetime field is 0000-00-00 which then barfs in hibernate (ERROR - Value '0000-00-00' can not be represented as java.sql.Timestamp). We aren't setting it to 0s, instead we're giving it the current date and we can never see the 0s in the database ourselves but it seems as if the second thread, if it gets the timing just right, does see it in the weird state almost as if it's first created a row with all blanks/defaults and then fills it in with the actual data. We're using read_committed isolation (iso level = 2) so we shouldn't be seeing anything uncomitted but we can reproduce this pretty consistently.

I'm trying to create a really dumb test to recreate the issue but of course its never that easy :( so I'm hoping someone here might be able to shed some light on the situation.

Thanks in advance
Sean

PS: running mysql on mandrake 10, its 4.1.12-standard and the client is using mysql-connector-java-3.1.7-bin to connect. The client has been tried on both linux and windows xp setups with the same results.