Understanding shared lock on database
Krishna saw a shared lock on a remote database persist after a select finished, until the session ended, and asked why. Madison Pruet said it prevents the database being dropped while in use. Fernando Nunes confirmed the lock is set up after a distributed query and stays, and noted isolation levels belong to sessions, not databases. No way to release it earlier was given.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
When I execute a query (a select query) against another database, I notice a shared lock being placed on that database. This lock is held even after the query has completed execution and is released only after this session completes. Why is this the case? Shouldn't the shared lock be released once the query has completed execution? Both the databases have the same isolation level - committed read. The fine manual mentions that the shared lock on the database is held until the database is closed. http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlt.doc/sii10961218.htm Does that have anything to do with what I am observing? Krishna
Krishna wrote: > When I execute a query (a select query) against another database, I > notice a shared lock being placed on that database. This lock is held > even after the query has completed execution and is released only > after this session completes. Why is this the case? Shouldn't the > shared lock be released once the query has completed execution? > > Both the databases have the same isolation level - committed read. > > The fine manual mentions that the shared lock on the database is held > until the database is closed. > http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.sqlt.doc/sii10961218.htm > > Does that have anything to do with what I am observing? > > Krishna To prevent the database from being dropped while you are in the database.
On Aug 26, 8:57 pm, Madison Pruet <mpru...@verizon.net> wrote: > Krishna wrote: > > When I execute a query (a select query) against another database, I > > notice a shared lock being placed on that database. This lock is held > > even after the query has completed execution and is released only > > after this session completes. Why is this the case? Shouldn't the > > shared lock be released once the query has completed execution? > > > Both the databases have the same isolation level - committed read. > > > The fine manual mentions that the shared lock on the database is held > > until the database is closed. > >http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.s... > > > Does that have anything to do with what I am observing? > > > Krishna > > To prevent the database from being dropped while you are in the database. But in this case the lock is held on the remote database and not on the local database.
Krishna wrote: > On Aug 26, 8:57 pm, Madison Pruet <mpru...@verizon.net> wrote: >> Krishna wrote: >>> When I execute a query (a select query) against another database, I >>> notice a shared lock being placed on that database. This lock is held >>> even after the query has completed execution and is released only >>> after this session completes. Why is this the case? Shouldn't the >>> shared lock be released once the query has completed execution? >>> Both the databases have the same isolation level - committed read. >>> The fine manual mentions that the shared lock on the database is held >>> until the database is closed. >>> http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.s... >>> Does that have anything to do with what I am observing? >>> Krishna >> To prevent the database from being dropped while you are in the database. > > But in this case the lock is held on the remote database and not on > the local database. Yes... *after* you run a distributed query. It could be established and removed after each query, but it stays there longer... One side note: databases don't have isolation levels... sessions do. Databases have logging mode, which may restrict the available isolatation levels sessions can choose. A non-logged database only allows dirty read isolation level. A logged database allows all isolation levels. Regards.