Re: Re[2]: HELP Informix Locks

Susik_Lee@United#u#Group.CCMAIL.CompuServe.COM wrote: > > Tolis, > > When I was using share mode lock with page mode lock in table, some > times I run out of locks. I think because that it will count 1 lock > per page, thus if you are updating many rows, you will use more than 1 > lock. > > Anyway, do you know how many row locks can you have in one page. For > example, if I want to update 1000 records in a table, do you know how > may locks I will be using in share mode with page lock? > > We are using On-line 5.x. > > Thank > > Susik > Susik, it depends how the data you want to update is distributed on the pages.At the worst case if it is distributed across 1000 pages you will need 1000 locks. At the best case if the data you want to update is stored contiguous then you may use as an approximation the following formula that is fully described on the "Guide to SQL: Tutorial", Page 10-12: If your rowsize is lesser than your pagesize then the number of rows you can have at one page is defined as: datrows = min(255,floor((pagesize-28)/rowsize) so if you have pagesize 2048 and rowsize 100 then you have 20 records per page.Assuming your data is stored contiguous it is then placed on 50 pages, so you will need 50 page locks.So the answer is something between 50 and 1000 locks. The best solution in such cases if you can afford to lose the concurrency for the duration of this transaction is to lock the table in exclusive mode. Regards Tolis +---------------------------------------------------------------------+ | V+K Relational Solutions email: tvarnas@compulink.gr | | Deligiorgi 26 tvarnas@orbit.de | | 546 42 Thessaloniki Voice: (30) 31 820270 | | Greece Fax: (30) 31 865463 | +---------------------------------------------------------------------+