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.)

Answer 2

Inserting will not lock the table. The inserted records will not be visible to other sessions until you commit.

Answer 3

Your question is relevant to any case where you are inserting into a table with any unique constraint. If there was no index, and you insert a row into the table, you'd expect the database would need to lock the entire table - otherwise duplicates might be inserted in a multi-user system.

However, Oracle always polices unique constraints with an index. This means that the data for the column is always sorted, and it can quickly and easily determine whether a conflicting row already exists. To protect against multiple sessions trying to insert the same value at the same time, Oracle will just lock the block in the index for that value - in this way, you won't get contention for the whole table, only for the particular value you're inserting. And since an index lookup is typically very fast, the lock will only need to be held for a very small period of time.

(But now, you might ask, what if a session inserts a value but doesn't commit straight away? What if another session tries to insert the same value? The answer is, the second session will wait. This is because it will request a lock on the same index block, but since the first session hasn't committed yet, the block will still be locked. It must wait because it cannot know if the first session will commit or rollback.)