Обсуждение: possible to lock a single row in table?
Hello! i have a lots of threads that work on a table, making insertions, updates removes..... now i certain cases its important to keep data integrity, so i looked into locks.... all i found was a lock function that locks the entire table.... now even in shared mode, if i understand it well this means that as long as the locking procedure isn't finished, no other thread can update or delete data, right? Thats really harsh, since there are really an awful lot of threads.... isn't it possible to lock only a specific row? and what happens to other insert queries whilst the lock is operational? Are they postponed and the valling thread waits, or does the call return with an error? -- ciao bboett ============================================================== bboett@earthling.net http://inforezo.u-strasbg.fr/~bboett http://erm1.u-strasbg.fr/~bboett =============================================================== the total amount of intelligence on earth is constant. human population is growing....
Bruno Boettcher writes: > i have a lots of threads that work on a table, making insertions, > updates removes..... > > now i certain cases its important to keep data integrity, so i looked > into locks.... Only in certain cases??? ;-) > all i found was a lock function that locks the entire table.... There are also row level locks, which are automatically acquired when needed. The best way to lock specific rows is the SELECT ... FOR UPDATE statement. Playing with the LOCK command is generally not necessary. > and what happens to other insert queries whilst the lock is operational? > Are they postponed and the valling thread waits, or does the call return > with an error? That depends on the transaction isolation level (which you can set). See the chapter on concurrency control in the User's Guide for more information. -- Peter Eisentraut peter_e@gmx.net http://funkturm.homeip.net/~peter
On Wed, 20 Jun 2001, Bruno Boettcher wrote: > i have a lots of threads that work on a table, making insertions, > updates removes..... > > now i certain cases its important to keep data integrity, so i looked > into locks.... What kind of data integrity are you trying to implement? > all i found was a lock function that locks the entire table.... > > now even in shared mode, if i understand it well this means that as long > as the locking procedure isn't finished, no other thread can update or > delete data, right? Thats really harsh, since there are really an awful > lot of threads.... > > isn't it possible to lock only a specific row? Yes. Look at SELECT ... FOR UPDATE. That should lock those rows matched such that another transaction that attempts to select for update, update or delete those rows waits until the locking transaction finishes. > and what happens to other insert queries whilst the lock is operational? > Are they postponed and the valling thread waits, or does the call return > with an error? IIRC, postponed until the transaction that made the lock commits or rolls back.