Bad SQL grammar with statements using NOLOCK using HSQLDB

Question

I am using HSQLDB and am trying to run a select statement which contains NOLOCK in the query.

When the statements are exeuted, a BadSqlGrammarException is thrown.

Is there a way to get around this issue as the command runs perfectly well with SQL Server?

Answer 1

a_horse_with_no_name is correct with his comment:

HSQLDB does not support NOLOCK. If you need that in your statements, you need to run your tests against the real thing (SQL Server)

To clarify this, NOLOCK is a SQL Server specific keyword specifying a hint to the database how to behave. It even has its own tag nolock here on Stack Overflow:

A SQL Server table hint that can be used to prevent queries from issuing shared read locks. While often used to prevent a query from blocking other queries it also makes it susceptible to dirty reads and other potential data issues.

Thus, SQL queries including this keyword are not valid for HyperSQL.

What you can do is:

• Run the tests against an SQL Server instance (as pointed out by the above comment).
• Refactor your SQL statements to not use specifics for SQL Server. In this case, you would have to make sure they don't use NOLOCK. How this would affect your application is impossible to say without more knowledge about it.

---

As a side note, HyperSQL fully supports the SQL specification:

HyperSQL 2.x supports the dialect of SQL defined by SQL standards 92, 1999, 2003, 2008 and 2011.