YugabyteDB column-level locking for UPDATE

In a previous blog post showing how to change the datatype of a column without stopping the application, there is a phase where we need to update the new column from the old one. At the same time, the application may run some DML. This is why the update should be done in small transactions.

I mentioned that the DML that may be blocked are DELETE and UPDATE of the column that is retyped.

The reason why DELETE conflicts with concurrent updates is obvious: the whole row is locked.

The reason why INSERT doesn't conflict, even if the new column is updated by the trigger, is because of READ COMMITTED isolation level which can ignore new rows.

The reason why I mentioned only the update of the re-typed column is that in YugabyteDB only the updated columns are locked. In PostgreSQL, any update, even on other columns, would have been in conflict.

Here is what you can run to reproduce, taken from the blog post on re-typing a column:

drop table if exists demo;
create table demo ( k bigserial primary key, a int, v int default 0);
insert into demo(a) select generate_series(1,100);
alter table demo add column a_new bigint;
create or replace function a_new() returns trigger as $$
 begin new.a_new := new.a; return new; end; $$ language plpgsql;
create trigger a_new_trigger
before insert or update of a on demo for each row
 execute function a_new();
begin transaction;
insert into demo values(0,42);
update demo set v=v+1 where k=2;

\! psql -ec 'set statement_timeout to 3000' -c 'update demo set a_new=a where a_new is null'

commit;

I use \! psql -ec to run the concurrent UPDATE from another session.

In PostgreSQL, this UPDATE fails with ERROR: canceling statement due to statement timeout CONTEXT: while locking tuple (0,2) in relation "demo"
because the UPDATE of the column v has locked the row:

Running the same in YugabyteDB doesn't block and doesn't fail:

The reason is that the UPDATE of column v has locked only this column within the row. And then it doesn't conflict with the update of column a_new (through the trigger).

The level of locking improves with database technology. The oldest databases were escalating locks to the table level. Some can still wait on a whole block like Oracle when not having enough free space for the lock information. PostgreSQL locks at row level. YugabyteDB, thanks to the key-value storage with rows and column values as documents, store lock information at the lowest level.