List:Internals« Previous MessageNext Message »
From:Michael Widenius Date:February 1 2001 5:57pm
Subject:concurrent update/select
View as plain text  

>>>>> "Philippe" == Philippe MORIN <pmorin@stripped>
> writes:

Philippe> Hi
Philippe> I'm thinking about adding dirty read into MySQL, to allow concurrent
> update/select or insert/select, even if there are some holes in the tables of my
> database.
Philippe> I read the sources and I wonder if it is possible to modify the sources to
> unlock and lock tables, used by a select request, between each read of row in the tables
> (in sub_select function in sql_select.cpp file), to allow other threads to update my
> database during a big select.
Philippe> I know that the result of the select may be wrong (duplicate results for
> example), and that the engine may slow down, but it doesn't matter for me.
Philippe> I tried to find some informations in MySQL archive list about dirty read, but
> I didn't found anything, except none locking during select and so on...
Philippe> Does somebody have an idea about possible problems or about another way to
> realize this feature?

Some backgroung

The MyISAM code can do automatic locking/unlocking when running
multiple process by doing an automatic lock/unlock for each read/write
(with fntl().  This is the default if the application doesn't execute
mi_lock_database() (we have to change the name of this function)
to lock the whole table.

The thing is that the above lock/unlock doesn't work within the same thread.

To get what you suggest to work you have to do:

- Change all table locks to TL_WRITE_ALLOW_WRITE;  This will alow
  multiple reader/writers on the table.
  (See how we do in ha_berkeley::external_lock)

- Put a rw_rdlock or rw_wrlock around all reads, insert, update and delete
  (Currently we do it only for read/write to key trees, see

- Ignore all HA_EXTRA_WRITE_CACHE calls to
  myisam/mi_extra.c:extra()  (We can't buffer insert rows in this context)

- Change mi_delete to not pack rows on the fly.  The problem with this
  is that one thread may move the beginning of a row while another
  thread is scanning the rows.
  To fix this you need to disable the code in:


in "delete_dynamic_record()"

    if (_mi_get_block_info(&del_block,info->dfile,filepos+length) &
        BLOCK_DELETED && del_block.block_len+length < MI_DYN_MAX_BLOCK_LENGTH)
      /* We can't remove this yet as this block may be the head block */
in "_mi_write_part_record()"

  if (next_block < info->state->data_file_length &&
        info->s->state.dellink != HA_OFFSET_ERROR)
      if ((_mi_get_block_info(&del_block,info->dfile,next_block)
           & BLOCK_DELETED) &&
          res_length + del_block.block_len < MI_DYN_MAX_BLOCK_LENGTH)
        if (unlink_deleted_block(info,&del_block))
          goto err;

in "update_dynamic_record()"

          /* Check if next block is a deleted block */
          MI_BLOCK_INFO del_block;
          if (_mi_get_block_info(&del_block,info->dfile,
                                 block_info.filepos + block_info.block_len) &
            /* Use; Unlink it and extend the current block */
            DBUG_PRINT("info",("Extending current block"));
            if (unlink_deleted_block(info,&del_block))
              goto err;


The hardest part is to allow both row buffering (when scanning) and
people that changes rows as the following may happen:
(Note that affects ONLY affects dynamic, not fixed size rows)

- One thread is scanning the table and has read a big row block with
  _mi_read_cache(), which reads X bytes of data

- Assume a existing row starts at X-1

- While scanning the buffered rows, the row at X-1 is deleted. In this
  case the scanning threads doesn't see that the row at X-1 is deleted
  but reads the first byte from the in-memory row cache and the rest
  from file, which will cause confusion

The fix is to keep a list of all IO_CACHE in use and everytime you
update a row on disk, you also update all IO_CACHE buffers so that
they reflect what's on the disk.

concurrent update/selectPhilippe MORIN1 Feb
  • Re: concurrent update/selectSasha Pachev1 Feb
  • concurrent update/selectMichael Widenius1 Feb
  • Re: Some questions about table->read_set and table->write_setSergei Golubchik28 Jul