if you try to use a LockTypeMode with SQL Server driver using for example this code:

retVal = session.createQuery("from " + object.getSimpleName() + " fetch all properties " + filter + " order by 1 desc").setLockMode(lockMode).setMaxResults(1).uniqueResult();

you will receive this error:

*Incorrect syntax near 'holdlock'. If this is intended as a part of a table hint, A WITH keyword and parenthesis are now required. See SQL Server Books Online for proper syntax.
*

This because the query produced by hibernate is like this:

select psbosettin0.SettingsID as Settings1_2_, psbosettin0_.Name as Name2_2_, psbosettin0_.Value as Value3_2_ from DB_OcmGroup.dbo.PS_BO_SETTINGS psbosettin0_ *holdlock *where Name='InvoiceSequence' order by 1 desc_

while holdlock should be with(holdlock)