Re: Locks

>Clem -- >I have copy of an old post of yours regarding the subject of locks. >You say, "In summary, we use row locking and pump up the LOCKS value in the >ONCONFIG file as big as we can." > >How do you define "as big as we can"? Obviously we are talking about a >memory resource, but how do we measure what we need and ow much we can grab? > >If you could share your thoughts with me on this subject, I'd be highly >appreciative. > >We are finally moving our client-server application to the user >acceptance phase and I don't want a locked row to spoil the party. > >Thanks > >Paul Finkel >finkel@csws.attmail.att.com >908-457-5170 This is from an old post... #TFM states that locks are cheap, and the default setting of 2,000 seems #ludicrously low to me. I increase the locks to somewhere between 50,000 #and 100,000 for databases who's tables contain about that many rows. #Max locks is 250,000. The cost is shared memory. If you overlock you #run the risk of totally hosing the indexes and data for the table. I've #had to restore from archive before for that reason. The 50-100,000 number comes from a hunch about the maximum possible number of rows in the tables in my database. You MUST have enough locks to handle the maximum number of rows locked at any one time. If you are *really sure* that you have no applications that do, for example, massive deletes or updates, AND you have no programmers or users that would do such a thing from SQL then you may leave the locks relatively low. If you have your isolation level set to "repeatable read" then any application program may lock *every row it reads*, which could be the entire table on a sequential scan. Given these things, here's how we size our locking value: 1) Check how much memory you can use. 2) Find the largest table in your db. 3) Try setting the locks to hold an update to the entire largest table. If this value doesn't make your shmem too large then use it. If it won't fit in your memory space, then make it smaller until it does. If this new size is big enough to handle all foreseeable lock events then quit. If not, then you must take steps to ensure you don't run out of locks. These include: a) user training. Tell all users about your lock limit. b) application monitoring. Don't write code that deletes (for example) too many rows at a time. c) buying more memory to handle the larger shmem size. (you can never have too much memory/disk/CPU/...) 4) This is an iterative process involving compromise on several variables (shmem, isolation level, table size, application programming, user training in SQL.) 5) Be aware that the penalties of setting locks too low can be severe. You may have to sequentially unload, drop, re-create, and re-load tables whose indexes have become corrupted from overlock conditions (I have had to do this more than once.) :( That's all that comes to mind right now. I'm reminded of the user who said his database was pretty big (about 500MB), and was laughed off the net. Then he replied that he was measuring RAM, and I heard no more laughter. Have fun tuning your system, ;) PS I sent this twice to finkel@csws.attmail.att.com but it bounced "host unknown" both times. Is there some problem with your net, or mine? ITMT it won't hurt to post this for general consumption, I guess. __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|