let's say there are 2 processes a & b. process a write-locks a table. process b tries to read the table but it's locked by process a, so it waits. is there a way for process b to stop waiting (maybe a timeout variable)?
Originally posted by ijiajia
U should check if the table has been locked in process b.
Obviously, you didn't get the original question. There has to be some kind of error raised (for instance, it a SQL error -911 on DB2) on a lock timeout regardless of the lock type (i.e. row or table). You don't use database locking to manually check if there's a lock!
Just like the original poster, I'd like to know if there's any place where we can get the list of error codes. In my case, I am accessing the database through ODBC so I don't know how I'd get this error.
Regarding the timeout, there's no timeout on locking. Process b have to wait (no timeout) until process a unlocks the table.
You're right if you only use MyISAM tables but InnoDb can detect transaction deadlocks and can signal a timeout in a lock wait. The only problem is that InnoDB seems to roll back the whole transaction by itself instead on just raising the error and let the application handle the situation.
What remains to be seen is the error code returned by InnoDb on such an event.