I had a recent discussion with someone who believed that Oracle's default locking behavior on inserts was table-level locking.
From a practical perspective, the notion that an OLTP database like Oracle would use table-level locks for inserts would lead to such inconceivably bad performance for enterprise applications that I had to research this a bit. From what I can see, default Oracle behavior is ROW-LEVEL locking for inserts.
First, I tried an example of twin-session inserts into the same table. The sessions are below. From what I can see, Oracle has ROW-level, not table-level locks for inserts. In other words, I could commit from session two while I had a pending insert in session 1, and session 1 would see the latter insert before its own commit. Now each session provides a "commit-session-consistent" view of the data, but that to me is different from table-level locking.
I also checked the web, and found a link/book page below which seems to give the most succinct answer to the question "Does Oracle provide row-level locking for inserts?" with a "Yes".
Now I believe one can get a table-level lock for bulk inserts (ex. SQL*Loader), but this is not the default behavior. I also imagine Oracle may provide an OLAP-friendly table type whose default behavior is table-level locking, but again this is not the "default" for Oracle tables.
Of course, DDL operations (ALTER TABLE...) or index rebuilds should lead to table-level locks, but this is not for standard, single-row inserts.
Perhaps he was thinking of MySQL's "default" MyISAM table type, whose behavior IS table-level locking. MyISAM tables are great for "browsing" applications because they offer fast read access, but they're terrible for logging or transaction purposes because of their table-level locking. MySQL's InnoDB table type (with row-level locking) is preferable for cases where you have a lot of inserts.
SESSION 1 09:01:56 SQL> create table dkl_tmp (id integer primary key, txt varchar2(40)); Table created. 09:02:14 SQL> insert into dkl_tmp values (1, 'From session 1'); 1 row created. 09:02:20 SQL> select * from dkl_tmp; ID TXT ---------- ---------------------------------------- 1 From session 1 09:02:26 SQL> select 'Insert and commit from second session here' from dual; 'INSERTANDCOMMITFROMSECONDSESSIONHERE' ------------------------------------------ Insert and commit from second session here 09:03:45 SQL> select * from dkl_tmp; ID TXT ---------- ---------------------------------------- 1 From session 1 2 From session 2 09:03:55 SQL> commit; Commit complete. SESSION 2 09:02:52 SQL> select * from dkl_tmp; no rows selected 09:02:53 SQL> insert into dkl_tmp values (2, 'From session 2'); 1 row created. 09:03:03 SQL> select * from dkl_tmp; ID TXT ---------- ---------------------------------------- 2 From session 2 09:03:12 SQL> commit; Commit complete. 09:03:16 SQL> select * from dkl_tmp; ID TXT ---------- ---------------------------------------- 2 From session 2 09:04:23 SQL> select * from dkl_tmp; ID TXT ---------- ---------------------------------------- 1 From session 1 2 From session 2 09:04:45 SQL>
http://books.google.com/books?id=tdRes4IdLiIC&lpg=PA349&ots=7Qz01b9ybn&d...