newbie: em.flush() is locking a select statement

[mars1412]

I just noticed following issue, that I can't understand.

I change some properties on myEntity and set a breakpoint before

entityManager.flush();

In a command window I start sqlcmd (MS-Sql Servers commandline interface) and select the entity that I am about to change.

select * from MyEntity where id = 1;

This works as expected.

Then in the debugger I execute the flush() statement (only this single line).

When I now try to do the same select in my command window, the select blocks until I resume my debugging session!
As soon as the debugged transaction is committed, the select in the command window resumes and gives me the results.

What is going on here??

How can the update-statement in my debugging session block a select (!) statement in another transaction (that even exists in another process)?

If I issued another update in the command window this behaviour would be ok. But not for a select, where I only read the data.

I hope someone can enlighten me.

[jaikiran]

Since the data is not yet committed, the select statement is blocked to avoid reading stale data. In MSSQL server, there's a option to provide a nolock hint to the server through the select query. However AFAIK, MySQL doesn't have such a hint. Try doing this:

set transaction isolation level READ UNCOMMITTED;
select * from MyEntity where id = 1;

See if it works.

[mars1412]

ah, thanks
you pointed me in the right direction

after some investigations I found out, that the READ_COMMITED transaction isolation level behaves differently between oracle and MS Sql server.

SQL Server uses shared locks by default. Thus the select statement is blocked while the update is not commited yet.

If you set READ_COMMITTED_SNAPSHOT to ON for your database, then it uses row-versioning (like Oracle's default mode): and the select will not be blocked until the update is commited.
I think this behaviour can improve performance on my webapplication.

more information on this:
http://msdn2.microsoft.com/en-us/library/ms173763.aspx
http://msdn2.microsoft.com/en-us/library/tcbchxcb(VS.80).aspx