tag:blogger.com,1999:blog-7395977411859619892.post2794123838154016778..comments2024-01-30T09:21:06.579+01:00Comments on About Oracle: Scalability of dbms_lock.requestRob van Wijkhttp://www.blogger.com/profile/00499478359372903250noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-7395977411859619892.post-27645418466324178252013-02-18T10:34:41.012+01:002013-02-18T10:34:41.012+01:00Thanks Victor for sharing your insights here.Thanks Victor for sharing your insights here.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-73717083117500929782013-02-14T22:22:42.044+01:002013-02-14T22:22:42.044+01:00It should be noted that dbms_lock.request is unsca...It should be noted that dbms_lock.request is unscalable only within a transaction or a session (depending on the value of release_on_commit).<br /><br />If you start a transaction and then execute a for-loop requesting a big amount (e.g. 10.000) of locks in release_on_commit mode you can see a linear growth of the execution time needed for every next portion of locks (just as shown above in the Anonymoushttps://www.blogger.com/profile/03438151574150271823noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-24376035961336056132012-09-11T10:20:53.642+02:002012-09-11T10:20:53.642+02:00Right, I solved this by reducing the number of loc...Right, I solved this by reducing the number of locks being acquired. This works well in situations with low oder medium concurrency, but you may get more spurious locks and deadlocks:<br /><br />l_lock_dummy := dbms_lock.request ( release_on_commit => true, id => dbms_utility.get_hash_value( name => 'child' || to_char(MOD(p_parent_id,100)), base => 1, hash_size => power(Anonymoushttps://www.blogger.com/profile/03324749894469228728noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-69550799371341352542012-05-23T13:43:09.372+02:002012-05-23T13:43:09.372+02:00I'm pretty sure that Oracle documentation for ...I'm pretty sure that Oracle documentation for this package indicates that it is not efficient for a large number of locks. I have used this package extensively to implement mutual exclusion locks for processes that need to be serialized so the number of locks is always very low and it works quite well for this purpose.Scott Angelohttps://www.blogger.com/profile/10964663237232237331noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-49218181227970974422009-04-26T20:53:00.000+02:002009-04-26T20:53:00.000+02:00Yes, that was my point. The more locks with dbms_l...Yes, that was my point. The more locks with dbms_lock.request, the longer it takes.Rob van Wijkhttps://www.blogger.com/profile/00499478359372903250noreply@blogger.comtag:blogger.com,1999:blog-7395977411859619892.post-12247666020846856432009-04-26T10:43:00.000+02:002009-04-26T10:43:00.000+02:00Does this only happend when there are a lot of loc...Does this only happend when there are a lot of locks?mzkhttps://www.blogger.com/profile/16605891193346870658noreply@blogger.com