Re: table locking

On Mon, 7 Dec 1998, Yan Zhu wrote: > hmm, tried that, the user is still able to perform select statement on it > and unload the data to a text file. Which database engine? Is the database logged? I think not! To demonstrate this, you need (for example) two windows on your machine. time Window 1 Window 2 | dbaccess unlogged - | lock table tablename | in exclusive mode; | dbaccess unlogged - | select * from tablename; V With OnLine 7.24.UC1 and an unlogged database, the SELECT works because the second process is working at DIRTY READ isolation (which ignores locks). When I tried setting the isolation to committed read, I got error -256 (transaction not available). time Window 1 Window 2 | dbaccess logged - | begin work; | lock table tablename | in exclusive mode; | dbaccess logged - | select * from tablename; V In this case, the SELECT got error -244/-113 (table is locked). Moral: use databases with transaction logs. > Jonathan Leffler wrote: > > On Mon, 7 Dec 1998, Yan Zhu wrote: > > > How do you lock a table in exclusive mode so no other users can > > > update, view, or unload the data until you release the lock. > > > > LOCK TABLE TableName IN EXCLUSIVE MODE; > > > > Beware transaction boundaries. Note that a process does the locking; > > if the process dies, the table is unlocked. Also note that only the > > locking process can access the table after successfully executing that > > statement -- child processes are locked out just as much as unrelated > > processes are. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix v0.60 -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn