Database Insert Mechanism

Question

I have a question in mind about the insert mechanism in different databases. Supposing a table with a single column primary key that is automatically generated (like identity columns), will the entire table become locked when inserting a new record? and if the insert takes too much time, will the other transactions have to wait more?

Answer 1

By default Oracle uses row level locks.

These locks are blocking only for writers(update, delete, insert etc). That means select will works all the time when a table is heavy updated, delete from, etc.

For example, let be tableA(col1 number, col2 number), with this data within it:

col1  |  col2
1     |  10
2     |  20
3     |  30

If user John issues at time1:

update tableA set col2=11 where col1=1;

will lock row1.

At time2 user Mark issue an

update tableA set col2=22 where col1=2;

the update will work, because the row 2 is not locked.

Now the table looks in database:

col1  |  col2
1     |  11   --locked by john
2     |  22   --locked by mark  
3     |  30

For Mark table is(he does not see the changes uncommited)

col1  |  col2
1     |  10   
2     |  22   
3     |  30

For John table is:(he does not see the changes uncommited)

col1  |  col2
1     |  11   
2     |  20   
3     |  30

If mark tries at time3:

update tableA set col2=12 where col1=1;

his session will hang until time4 when John will issue an commit.(Rollback will also unlock the rows, but changes will be lost)

table is(in db, at time4):

col1  |  col2
1     |  11   
2     |  22   --locked by mark  
3     |  30

Immediatley, after John's commit, the row1 is unlocked and marks's update will do the job:

col1  |  col2
1     |  12   --locked by mark  
2     |  22   --locked by mark  
3     |  30

lets's mark issue a rollbak at time5:

col1  |  col2
1     |  11   
2     |  20   
3     |  30

The insert case is simpler, because inserted rows are locked, but also are not seen by other users because they are not commited. When the user commits, he also releases the locks, so, other users can view these rows, update them, or delete them.

EDIT: As Jeffrey Kemp explained, when you have PK(it is implemented in Oracle with an unique index), if the users try to insert the same value(so, we would have a duplicate), the locking will happen in the index. The second session will be blocked until the first session ends because it try to write in the same place. If the first session commits, the second will throw Primary key violated exception and will fail to change the database. If first session does a rollback, the second will succeed(if no other problem appears).

(NB: In this explanation by user John I mean a session started by user John.)