Re: Re: lock table problems

Mark Lo wrote: > > hi, > > You mean if I have 10 tables under the same database, once I locked one > table with the statement "lock tables table1 read", then the other > tables(the rest of table except table1) will not be allowed to access by me > or other people, even for reading......unless I unlock table1. Is that what > you mean.?? > > Thank you > > Mark Not quite. Let's say tou have 10 tables. You need to perform a transaction involving 2 tables, you are going to read from table1 and write to table2. Another process wants to read from table2 and write to table1. If you only locked the table you were going to write, you could each lock the table the other needed to read to complete the transaction. Now neither can continue. You are in the "fatal Embrace." You "lock tables table1 read, table2 write" You can read from table1, and table2, and can write to table2. Everyone else can read from any table except table2, and write to any table except table1 and table2. The other process would "lock tables table1 write, table2 read" Now when you get your lock, you know you can read fron the two tables you locked, and write to the one you need to write to, while preventing writes to those specific tables during the critical transaction. Many times locks can be avoided by using atomic relative updates. EX. update itemaster set qty=qty+5 where inemno='red_ball'; Someone else can sell ( subtract ) 2 red balls while you are adding your 5 without any locks needed, since the server automatically locks the file during the update, and unlocks it when the update is completed. I hope this helps.