>>>>> "Philippe" == Philippe MORIN <pmorin@stripped>
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
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?
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:
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 */
if (next_block < info->state->data_file_length &&
info->s->state.dellink != HA_OFFSET_ERROR)
& BLOCK_DELETED) &&
res_length + del_block.block_len < MI_DYN_MAX_BLOCK_LENGTH)
/* Check if next block is a deleted block */
block_info.filepos + block_info.block_len) &
/* Use; Unlink it and extend the current block */
DBUG_PRINT("info",("Extending current block"));
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.