I previously started having problems setting up liferay ..on my first two attempts there was no problem at all ...
This time though I get server startup I get 404 error ... here are the logs:

"18:21:38,874 WARN [BasicResourcePool:1841] com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1dbb12b -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception:
java.sql.SQLException: The database is already in use by another process: org.hsqldb.persist.NIOLockFile@acc51323[file =/opt/liferay-portal-5.2.3/data/hsql/lportal.lck, exists=true, locked=false, valid=false, fl =null]: java.lang.Exception: checkHeartbeat(): lock file [/opt/liferay-portal-5.2.3/data/hsql/lportal.lck] is presumably locked by another process.
at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
at org.hsqldb.jdbc.jdbcConnection.<init>(Unknown Source)
at org.hsqldb.jdbcDriver.getConnection(Unknown Source)
at org.hsqldb.jdbcDriver.connect(Unknown Source)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Aug 23, 2009 6:21:38 PM org.apache.jk.common.ChannelSocket init
INFO: JK: ajp13 listening on /0.0.0.0:8009
Aug 23, 2009 6:21:38 PM org.apache.jk.server.JkMain start
INFO: Jk running ID=0 time=0/44 config=null
Aug 23, 2009 6:21:38 PM org.apache.catalina.storeconfig.StoreLoader load
INFO: Find registry server-registry.xml at classpath resource
Aug 23, 2009 6:21:39 PM org.apache.catalina.startup.Catalina start
INFO: Server startup in 106454 ms"

I have no idea what else could be using the database ... or what this means ...

I eventually solved this by deleting file lportal.lck in data/hsql folder. This must have been created in one of the false starts.

Was stuck with the same problem. Resolved it after reading your post.
Thanks a lot!

Though one thing I noticed (I may be wrong here), that this problem started after I created a database connection to local HSQL liferay database from eclipse.
The problem persisted even after disconnecting that DB connection and deleting it from eclipse.
Eventually got resolved after deleting that .lck file. Thanks again!

Regards,
Ashish

HSQL as is used in Liferay demo package is just for single user. If you accessed it from Eclipse and tried to access it again from Liferay, you're bound to get into trouble.
I, usually just use MySQL when using Liferay IDE to avoid this problem.