|
| Rollback to savepoint | 5 May 1999 |
| After a rollback to savepoint, will a waiting transaction continue to wait or can it proceed? | ||
 | It depends on what type of wait it is. If the transaction was blocked on a row-level lock and thus waiting on the TX enqueue for the blocking transaction, then it will continue to wait despite rollback to savepoint. However, if the transaction was waiting on a TM lock, then it will be able to proceed immediately. |
|
| X$KSQST output | 28 May 1999 |
I would like to get an explanation on the following entries in the X$KSQST table:
Lo Gets Waits -- ---------- ---------- CF 8363 167 CI 1742173 4614 CU 624419 10 DX 1591426 1 SQ 48817 313 ST 221221 73514 TM 22005125 11 TS 550966 14 TX 18836620 5257 WL 1153 1 What do the different lock types mean, and what impact do they have? | ||
|
This shows you the number of logical waits for each enqueue type.
There is no indication of impact.
However, the likelihood is that only the ST (space transaction), TM (table) and TX (transaction) locks have had any major impact.
The only one of these that is clearly a problem is the ST enqueue. This may indicate disk sorts writing to temporary segments in a permanent tablespace with a small default extent size, or possibly lots of dynamic extension and shrinking of rollback segments, or something else like that. Try to work out which, and reduce the amount of dynamic space management. |
|
| TS enqueue locks | 9 June 1999 |
| I traced my process and I saw that there were too many waits on the TS lock. But I also know that there was no sort on disk. This means that my process does not use a temporary segment. Why is it waiting for a TS lock? | ||
| The TS (id2=0) lock is held for each sort run or hash partition write to a temp segment. You may be doing a hash join, if you are not doing a disk sort. Do not trust the disk sorts statistic, it is known to miss some disk sorts. Rather check for I/O against your temporary tablespace in V$FILESTAT, or look for an UNMAP operation in your raw trace file. That said, while you may see lots of gets on this lock, waits should be relatively rare, and their duration brief. You can check out X$KSQST for the ratio of gets to waits. The wait times have to come from the raw trace file. |
|
| Library cache lock | 16 June 1999 |
| I'm trying to drop a synonym, and it's been waiting on a library cache lock for about an hour now. Is there any way to see who is holding the lock that I'm trying to get? Is there an init.ora parameter I can increase to reduce these problems? This happens frequently. | ||
| There is no init.ora parameter that is relevant here. Run $ORACLE_HOME/rdbms/admin/catblock.sql and then use the DBA_KGLLOCK view to see what is happening. This is fairly unusual. |
|
| Strange enqueue wait | 1 October 1999 |
Why would Oracle incur an enqueue wait on update statements issued by two database sessions
that touch two different records if both records are on the same block,
but otherwise succeed if the statements update two records from different blocks?
SESSION_ID TYPE ID1 ID2 MODE_HELD MODE_REQUESTED
---------- ---- ---------- ---------- -------------- --------------
19 TX 262182 71967 None Share
18 TX 262182 71967 Exclusive None
| ||
| You can tell from the fact that the waiting session wants a SHARE mode lock that it is waiting for a free transaction slot in the block. The problem is that there is not enough free space in the block to dynamically allocate another transaction slot. |
|
| Why buffer locks? | 6 October 1999 |
|
Say process A holds the buffer and process B is waiting to get a lock on it.
Would there be a buffer busy wait even though processes A and B need different rows in that block?
If yes, how do we get around it?
Also, if a buffer lock locks the entire buffer (hence the block) why do we need a row level lock? | ||
| I think you may be missing the difference in the duration of these locks. Row-level locks are held for the duration of a transaction. Buffer locks are held only for the brief moment that it takes to actually change the block. You cannot have two processes changing the same buffer at exactly the same time, because they would mess up each other's changes. |
|
| ORA-4020 - deadlock detected while trying to lock object | 29 October 1999 |
| An application is trying to insert into a table and gets an ORA-4020 error. Looking at the V$LOCK view shows a TS lock being held. I restarted the database and the TS lock had disappeared. We reran the application and about a minute later this lock had reappeared. Any idea where else I could look to find out more about this problem? | ||
|
This error message relates to library cache lock deadlocks, not enqueue lock deadlocks.
V$LOCK is only appropriate for enqueue locking issues.
To investigate library cache locking issues,
you need to use DBA_KGLLOCK which is created by the $ORACLE_HOME/rdbms/admin/catblock.sql script.
If you are unable to catch the output from DBA_KGLLOCK at the right moment, and if Oracle does not dump a trace file for you automatically, it will be necessary to use an event setting to diagnose this. If it is a self-deadlock, as I suspect, then a processstate dump will be adequate, if not, then an entire systemstate dump will be needed. If you have access to the code, modify the insert session to do the following: alter session set max_dump_file_size = unlimited; alter session set events '4020 trace name processstate forever, level 10';Otherwise you can put the equivalent settings in the init.ora file. If you are keen to get a resolution quickly, you may want to go straight to a systemstate dump, but be warned it will be LARGE. Analyzing the dump is non-trivial unfortunately. You will probably have to get Oracle Support to do it for you. |
|
| Tables with uncommitted changes | 12 November 1999 |
| Is it possible to list all the application tables that have undergone insert/update/delete operations that have not yet been committed? | ||
| Assuming that DML locks have not been disabled, V$LOCKED_OBJECT will get you close, but it will also include cases in which the lock has been taken but no DML has actually been done (yet). That however is the best that you will be able to do. |
|
| enqueue_resources | 20 November 1999 |
I got the following from another web site.
If you have many tables in your database, consider setting ENQUEUE_RESOURCES to the number of tables in your database plus 30%. If you continue to get timeouts, increase the value until the timeouts disappear. | ||
| A better way of working out what value to set enqueue_resources to is to look at the high-water mark of the resource usage in V$RESOURCE_LIMIT. The implication that a larger value can in some way help with timeouts is incorrect. |
|
| Row-level locks | 24 November 1999 |
| How does one determine the actual rows that are locked, not just the table that is locked? | ||
| It is not possible (by normal means) to determine which rows are locked in a particular table. The row-level locks actually exist in the database blocks, rather than in the SGA. Also it is possible for a block to be written to disk with some row-level lock still held, and those locks are not explicitly released when the transaction commits. Instead, the next transaction to require one of those row-level locks will check whether it is still in force, and if not it will clean it out. If a session is waiting for a particular row-level lock, then the rowid can be seen in the V$SESSION.ROW_WAIT_* columns. But otherwise, and other than that the existence (and more correctly, the persistence) of row-level locks cannot be readily determined. |
|
| dml_locks | 8 December 1999 |
| We are using Oracle 7.3 and yesterday for the first time we got an ORA-55 error (maximum number of DML locks exceeded). I have increased the dml_locks parameter and restarted the instance, but I would like to know if there is any dynamic view that I can query to find out the current usage, and that I could monitor and set the dml_locks parameter accordingly. | ||
|
The following query will show you the high-water mark of dml_locks usage since instance startup.
It is an extract from our fixed_table_hwms.sql script.
That script will alert you to such problems with any of the fixed tables.
select
'dml_locks' parameter,
count(*) setting,
count(decode(ksqlkctim, 0, null, 0)) hwm,
to_char(
100 * count(decode(ksqlkctim, 0, null, 0)) / count(*),
'99999'
) || '%' usage
from
sys.x_$ktadm
/
|
|
| Locking mechanisms | 21 December 1999 |
| What is the difference between locks, latches, semaphores and enqueues? | ||
| Latches are memory locations managed by an atomic hardware level TEST-AND-SET instruction. Enqueue and locks are different names for the same thing - namely, locks that support queuing and concurrency subject to compatibility rules. Semaphores are an operating system facility used to control waiting. |
|
| Disabling table locks | 27 December 1999 |
| We have a mysterious recurrent locking problem. Something is taking a lock on a key table. Killing the holding sessions does not seem to work. We have to restart the instance each time. What can we do? | ||
| The blocker is probably waiting at the head of the queue, rather than holding the lock. This scenario is explained on page 46 of my book. You can prevent the problem and identify the culprit using the ALTER TABLE DISABLE TABLE LOCKS command on the key table. |
|
| Library cache self-deadlock | 1 January 2000 |
I am getting a deadlock on a single row update for the only session connected.
This has been occurring for three days on most tables within a particular schema.
The instance has been restarted.
V$LOCK shows nothing.
What is causing it?
SQL> update admin.block
2 set blk_to_code = 'ES'
3 where str_idx = 23058.791
4 and blk_idx = 6;
update admin.block
*
ERROR at line 1:
ORA-04020: deadlock detected while trying to lock object ADMIN.BLOCK
Here is an extract from the trace file.
object waiting waiting blocking blocking handle session lock mode session lock mode -------- -------- -------- ---- -------- -------- ---- c688a088 c6fc85f8 c7327ae8 X c6fc85f8 c7327d68 S ------------- WAITING LOCK ------------- SO: c7327ae8, type: 23, owner: c7254ec8, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=c7327ae8 handle=c688a088 request=X call pin=0 session pin=0 user=c6fc85f8 session=c6fc96f8 count=0 flags=[00] savepoint=0 ------------- BLOCKING LOCK ------------ SO: c7327d68, type: 23, owner: c703c068, flag: INIT/-/-/0x00 LIBRARY OBJECT LOCK: lock=c7327d68 handle=c688a088 mode=S call pin=c7328d88 session pin=0 user=c6fc85f8 session=c6fc85f8 count=1 flags=PNC/[04] savepoint=248 | ||
|
Firstly, these are library cache locks, not enqueue locks, so V$LOCK will not help.
It only shows enqueue locks.
To investigate library cache locking issues, you need to use DBA_KGLLOCK
which is created by the $ORACLE_HOME/rdbms/admin/catblock.sql script.
However, the trace extract that you have provided contains the same information.
The trace shows that this is a self-deadlock. Note that the blocking user session is c6fc85f8, and so is the waiting user session. A shared lock has been taken by a call in the user session part of the way into its transaction (savepoint 248), and an exclusive lock is required by the recursive session c6fc96f8. Recursive sessions like this are used for recursive transactions. However, I cannot tell you from this trace what that recursive transaction was trying to do, and how to fix it. To get the fuller information required, could you please reproduce the error after having set the following event in the session. alter session set events '4020 trace name processstate forever, level 10'; |
|
| Here is the trace with the additional information. It still doesn't mean much to me. I hope you can shed some more light on how to avoid the deadlock. |
|
Here is the part of the trace file for the recursive session.
SO: c6fca7f8, type: 3, owner: c703c0b4, flag: INIT/-/-/0x00
(session) trans: c7254b60, creator: 0, flag: (2) -/REC -/-/-/-/-/-
----------------------------------------
SO: c7327da8, type: 23, owner: c6fca7f8, flag: INIT/-/-/0x00
LIBRARY OBJECT LOCK: lock=c7327da8 handle=c6527f50 mode=N
call pin=c7328dd0 session pin=0
user=c6fc9f78 session=c6fca7f8 count=1 flags=[00] savepoint=0
LIBRARY OBJECT HANDLE: handle=c6527f50
name=ALTER TRIGGER "ADMIN"."BLK_BIUR" COMPILE
The problem is that the trigger ADMIN.BLK_BIUR is invalid, probably because you have altered the definition of the table recently.
The recursive session is trying to recompile the trigger,
and needs to lock the table exclusively to update the definition of the table in the data dictionary that is used by stored PL/SQL code (the DIANA).
However, the table is already locked in shared mode by the top-level transaction.
Therefore, the X lock is impossible.
The workaround is to compile the trigger manually.
|
|
| Disabling Table Locks | 7 January 2000 |
| You mentioned the possibility of disabling table locks, but I couldn't find the syntax and explanation anywhere. | ||
|
Have a look at the syntax diagrams under ALTER TABLE in the SQL Reference manual.
Here is the explanation from the Parallel Server Concepts and Administration manual.
"To prevent users from acquiring table locks, use the following command:ALTER TABLE table_name DISABLE TABLE LOCKUsers attempting to lock a table when its table lock is disabled will receive an error. |
|
| ORA-1575 and ST enqueue | 2 February 2000 |
| We are getting ORA-1575 errors under Oracle 8 on a Sun. The message says, "Oracle has timed out waiting to acquire the Space Transaction enqueue lock". This is happening with simple loads, and ad hoc queries. | ||
| The ST enqueue has long been a potential source of contention and serialization, particularly for parallel segment creation operations. The standard mitigations are to avoid the use of a PERMANENT tablespace for sorts, or to avoid disk sorts entirely with a large sort_area_size, and to use a fixed extent size policy on all data tablespaces. The locally managed tablespace feature in 8.1 should also be adopted if possible. There is a bit more information in our tip on Planning Extents. |
|
| ITL entry shortages | 14 February 2000 |
| Some tables have been created with non-default values for PCTFREE and PCTUSED to improve block filling. However, we sometimes encounter deadlocks (the ones that are not real deadlocks but due to too little room for another transaction slot). Does this mean that PCTFREE and PCTUSED are too close together? | ||
| If you are sometimes unable to allocate another transaction slot, then that indicates that PCTFREE is too low, but not that PCTFREE and PCTUSED are too close together. |
|
| What about increasing INITRANS? |
| In general, you should not use a non-default INITRANS setting, because it can compromise data density, sometimes severely. Normally, this risk is adequately managed by ensuring that PCTFREE is non-zero, but in some cases it is necessary to allow PCTFREE to be slightly larger than the average row length. |
|
| Library cache deadlock | 16 February 2000 |
|
I have a problem with recurring ORA-4020 errors.
It seems to hit the first attempt to insert to certain tables each day.
I have captured some processstate dumps as you have suggested before in answer to similar questions.
The attached trace file contains dumps from two occurrences of the problem.
It contains some pretty low-level information.
How does one read such trace files?
I've sent some of these dumps to Oracle Support, but they are not being very helpful. All they have recommended is that we commit more often. | ||
| May I suggest that you ask for the call to be escalated to a senior support analyst? The person you are dealing with is clearly treating this as a routine DML locking problem, which it clearly is not. This has got nothing do with when you commit. It is a self-deadlock, and it is in the library cache. You are requesting an X lock on the library cache handles for the respective tables while executing an insert statement in a procedure. But, of course, you already have the S lock on that handle that was needed prior to executing the procedure. The question is, Why do you need to take an X lock on the library cache handle for these tables when all you doing is an insert? Normally, an X lock on the library cache handle is only needed if you are creating or altering the object. The other strange thing is that the intended owner of the lock is a transaction state object, not a call state object. That would be appropriate if you were indeed trying to alter the table in some way, but you are not. I strongly suspect a bug. My guess is that you have somehow lost the DIANA for these tables and need to regenerate it. The strange things is that the procedures themselves remain valid. |
|
| Good news. I think we're past our deadlock problem. It seems to be a problem with the export utility. We run a nightly full export of the database and had, for some reason, specified INCTYPE=COMPLETE. We removed the INCTYPE parameter from our exp parameter file, and the problem has gone away. Our Oracle Support representative has acknowledged that this is a new bug, but we don't have a bug number yet. |
| It looks like it was as I suggested at first, namely that the library cache objects for the tables had been invalidated. This would have been because the backup status for the table would have been changed, thus the row cache entry would have been pinned exclusive, which would invalidate the library cache object and cause it to need to be reloaded. However, dependent procedures should also have been invalidated, and they obviously were not. Thus it was possible for you to lock the procedure and its dependencies in shared mode, and then need to lock the table exclusive to reload it. Bang! This also explains why it was intermittent, because if the first reference was in a compile phase (rather than an execute) then the table objects would have been reloaded without problems. |
|
| ITL shortage deadlock | 25 February 2000 |
| I am getting deadlocks between several processes running at the same time, each of which update a mutually exclusive set of rows in the same table? How is this possible? Someone has suggested that I need to raise the INITTRANS parameter for the table. How will that help? | ||
| When updating a row, if all the transaction slots in that block are busy, and if there is not enough free space in a block for Oracle to dynamically create another transaction slot, then Oracle must wait for the transaction using an existing slot to commit. If your processes visit the blocks in your table in random order, then it is possible for this to result in a deadlock. You could avoid the deadlocks by driving each process via full table scan of the table. However, you would still block and wait for a long time. Therefore, the solution is either to raise PCTFREE to allow for the dynamic creation of additional transaction slots, or to increase INITRANS to equal the number of concurrent processes that you need to run. In either case, you need to rebuild the table. In this case, I would favor the INITRANS solution. |
|
| enqueue_resources | 6 March 2000 |
| How high can enqueue_resources go? I have increased it from 2000 to 6000, but it does not seem to have made a significant difference to the number of enqueue waits. | ||
| There is no relationship whatsoever between enqueue waits and enqueue_resources. Please see page 44 of my book. |
|
| Are you sure? I have read several books that say to increase enqueue_resources if the enqueue waits statistic is non-zero. |
| That's an old DBA's tale. The parameter sets the size of the enqueue resources array in the SGA. If you run out of slots in that array, you get and ORA-52 error without waiting. An enqueue wait occurs when the resource already has a slot in the resources array, but it is locked in an incompatible mode by another process. Increasing the enqueue_resources parameter in this case can do nothing other than waste some memory. |
|
| Lock waits during rollback? | 22 March 2000 |
| We had a bunch of session waiting on the same TX lock. Could it be that PMON was trying to undo these transactions? | ||
| Transactions that are rolling back do not wait on TX locks. They already have row-level locks for all the rows that they have changed and an ITL in each block too. Therefore, the waiting transactions were going forward. |
|
| ITL shortage deadlocks | 22 March 2000 |
| Can parallel processes that access different rows in a same table get into an ORA-60 deadlock because of ITL entry shortages? | ||
| If by parallel processes you mean slaves of a PDML operation, then no, the error that would be returned would be ORA-12829. If you just mean concurrent, then yes, it is possible. Again, the problem should normally be addressed by allowing adequate PCTFREE, rather than by raising INITRANS. |
|
| TX enqueue waits | 28 March 2000 |
| I would like to know more about TX enqueue waits. In your book, you mentioned "that when another transaction wants to modify the same row, and sees that an uncommitted transaction has modified that row, then that transaction waits, not on a row-level lock, but on the transaction lock for the blocking transaction." Are you talking about the transaction lock in the transaction table of the respective rollback segment? Please explain under what other circumstances a transaction will have to wait for a TX enqueue. | ||
| Yes. There are two TX enqueue waits: row-level lock waits, and ITL shortages. In the first case, the lock byte in the row header shows that a lock is held by another transaction. This lock byte points to an ITL entry which contains the XID (transaction ID) of the blocking transaction. In the second case, a transaction for which to wait is chosen pseudo-randomly from the ITL. In both cases the XID points to a slot in a rollback segment transaction table. The blocked transaction waits for the status of that transaction to change. |
|
| Killing a session | 1 April 2000 |
| I tried to kill a SELECT FOR UPDATE NOWAIT that had taken row locks on a large number of rows in the hope that PMON would clean up. The session was killed and remained in V$SESSION as KILLED - fine. But PMON did nothing, and table remained locked. I posted PMON (and SMON) using the ORADEBUG WAKEUP command. Still nothing. Eventually, I aborted the instance. It came up again with the table still locked. After a few minutes the locks were released and everything was OK again. Why didn't killing the session work? This is Oracle 7.3. | ||
| If you kill a session with ALTER SYSTEM KILL SESSION, there can be a delay before it dies. In particular, if it is already rolling back, it will complete that operation first. Otherwise, PMON (or a parallel execution slave process under Oracle8) will begin to rollback any open transaction immediately. The session remains in V$SESSION with status KILLED until the user attempts to make further use of the connection, at which point they are informed that their session has been killed, and the V$SESSION row disappears. However, the duration of the retention of locks is entirely a function of how long rollback takes. If you abort the instance before the rollback has finished, it will continue under the control of SMON once the database has been reopened, and the locks will remain in force until it has finished. |
| Copyright © Ixora Pty Ltd |
|