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