Start session three that monitors locks in the v$lock view.<\/li>\nWe first create our table, as well as a function that will insert 30 rows generated in a pipelined function that sleeps for one second between the generation of each row. This ensures the insert will run for 30 seconds and simulates a larger load.<\/p>\n
\r\nSQL> create table foo(c number)\r\n 2 \/\r\n\r\nTable created.\r\n\r\nSQL> create or replace type array as table of number\r\n 2 \/\r\n\r\nType created.\r\n\r\nSQL> create or replace function big_slow_insert return array pipelined as\r\n 2 begin\r\n 3 for i in 1..30 loop\r\n 4 pipe row(i);\r\n 5 dbms_lock.sleep(1);\r\n 6 end loop;\r\n 7 end;\r\n 8 \/\r\n\r\nFunction created.\r\n\r\nSQL>\r\n<\/pre>\nWe then script a python program that will generate the three sessions we mentioned earlier. We will print progress to standard output every few seconds, so we we can see what is happening. The script is below…<\/p>\n
\r\n#!\/home\/oracle\/local\/bin\/python\r\n\r\nimport cx_Oracle, time, sys\r\nfrom threading import Thread\r\n\r\nrunning = True\r\n\r\n#------------------------------------------------------------------------------------\r\n\r\nclass slowInsert(Thread):\r\n def __init__ (self):\r\n Thread.__init__(self)\r\n def run(self):\r\n global running\r\n running = True\r\n conn = cx_Oracle.connect(\"rep\",\"rep\")\r\n cursor = conn.cursor()\r\n print \"Starting append insert, which should block other inserts...\"\r\n cursor.execute(\"insert \/*+ append *\/ into foo select * from table(big_slow_insert())\")\r\n print \"Transaction finished, sleeping 30 seconds to determine effect in v$Lock of an open transaction that hasn't been commited...\"\r\n time.sleep(30)\r\n conn.commit()\r\n conn.close()\r\n\r\n#------------------------------------------------------------------------------------\r\n\r\nclass regularInsert(Thread):\r\n def __init__ (self):\r\n Thread.__init__(self)\r\n def run(self):\r\n global running\r\n conn = cx_Oracle.connect(\"rep\",\"rep\")\r\n cursor = conn.cursor()\r\n print \"Starting regular insert, sleeping 3 seconds to ensure other session has lock before we start...\"\r\n time.sleep(3)\r\n cursor.execute(\"insert into foo values(1)\")\r\n print \"Regular insert successful...\"\r\n conn.commit()\r\n conn.close()\r\n running = False\r\n\r\n#------------------------------------------------------------------------------------\r\n\r\nclass monitorEnqueue(Thread):\r\n def __init__ (self):\r\n Thread.__init__(self)\r\n def run(self):\r\n global running\r\n conn = cx_Oracle.connect(\"rep\",\"rep\")\r\n while running == True:\r\n cursor = conn.cursor()\r\n cursor.execute(\"select count(*),lmode,request from v$Lock where type = 'TM' group by lmode,request\")\r\n for row in cursor:\r\n print str(row[0]) + \" sessions have a TM lock in mode \" + str(row[1]) + \", with a request for it in mode \" + str(row[2])\r\n time.sleep(5)\r\n cursor.close()\r\n conn.close()\r\n\r\n#------------------------------------------------------------------------------------\r\n\r\nthreadList = []\r\n\r\ncurrent = slowInsert()\r\nthreadList.append(current)\r\ncurrent.start()\r\ncurrent = monitorEnqueue()\r\nthreadList.append(current)\r\ncurrent.start()\r\ncurrent = regularInsert()\r\nthreadList.append(current)\r\ncurrent.start()\r\n\r\nfor thisThread in threadList:\r\n thisThread.join()\r\nprint \"Finished\"\r\n<\/pre>\n…with the output below when it is run. Keep in mind 6 is an exclusive table lock (our append hint), while 3 is a row exclusive lock (standard insert generates this). Notice our regular insert is blocked for the duration of the test until the first session commits.<\/p>\n
\r\nStarting append insert, which should block other inserts...\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\nStarting regular insert, sleeping 3 seconds to ensure other session has lock before we start...\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\nTransaction finished, sleeping 30 seconds to determine effect in v$Lock of an open transaction that hasn't been commited...\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\n1 sessions have a TM lock in mode 6, with a request for it in mode 0\r\n1 sessions have a TM lock in mode 0, with a request for it in mode 3\r\nRegular insert successful...\r\nFinished\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"Today a DBA issued an insert statement with an append hint. This resulted in a fairly time consuming insert of many rows. The DBA was puzzled when all other sessions were blocked from inserting into the table. This is actually…<\/p>\n
Read more →<\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_mi_skip_tracking":false,"footnotes":""},"categories":[19,22],"tags":[],"_links":{"self":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/815"}],"collection":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/comments?post=815"}],"version-history":[{"count":23,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/815\/revisions"}],"predecessor-version":[{"id":830,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/posts\/815\/revisions\/830"}],"wp:attachment":[{"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/media?parent=815"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/categories?post=815"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/appcrawler.com\/wordpress\/wp-json\/wp\/v2\/tags?post=815"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}