Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Indexes and Locking
Some of us are having the dubious pleasure of porting our SQLServer 7.0 database to Oracle 8i. So, I am afraid I will be inundating you with questions.
In SQLServer, using row level locks on two seperate readcommited (blocks on updates - allows multiple reads) transactions, the two transactions can deadlock because they both access the index (which is not clustered).
Process 1 submits the statement:
UPDATE Indexed_table
SET Non_Clustered_Index_Field = 'NewData2'
WHERE Id = 2
Process 2 submits the statement:
SELECT Non_Indexed_Field
FROM Indexed_Table
WHERE Non_Clustered_Index_Field = 'Data2'
The lock happens because process 1 requests two exclusive locks in sequential order - first on the row and then on the nonclustered index. Process 2 takes requests two shared locks in sequential order - first on the index and then on the row. This causes deadlock...
So, my question is does this happen in Oracle? Is there a method to keep this situation from happening in Oracle (bitmap indexes, b-tree indexes, some setting in the config file, etc...)?
Reference: SQL Server Magazine - May 2000
Thanks for any help...
Got questions? Get answers over the phone at Keen.com.
Up to 100 minutes free!
http://www.keen.com
Received on Fri Jun 16 2000 - 00:00:00 CDT