Description
When querying with LockMode.Upgrade, no lock are actually acquire in the database with SAP SQL Anywhere and its current NHibernate dialects.
This is demonstrated by #530 test case, and the cause is diagnosed here.
The trouble is, SybaseSQLAnywhere10Dialect uses the for update by lock syntax. This syntax is cursor specific, and silently results in "no-op" (no intent locks acquired) as soon as it is used in a context where the engine considers there are no updatable cursors involved. With the way queries are sent by NHibernate to the database, the engine considers no such cursor is involved and so it does not lock anything. (This can be ascertain by break-pointing after such a query and checking locks in the database with SELECT * FROM sa_locks();.)
To actually get intent lock, the query can be changed for using with (updlock) hint instead of the for update syntax. The hint is not bound to cursor usage and do acquire locks whatever the query or cursor used. When using such a query, then locks can be seen in the database.
See also this forum post on the subject.