hi!
>>>>> "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
myisam/mi_write.c)
- 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:
mi_dynrec.cc:
---------
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 */
remove_next_block=1;
length+=del_block.block_len;
}
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;
res_length+=del_block.block_len;
}
}
in "update_dynamic_record()"
else
{
/* Check if next block is a deleted block */
MI_BLOCK_INFO del_block;
del_block.second_read=0;
if (_mi_get_block_info(&del_block,info->dfile,
block_info.filepos + block_info.block_len) &
BLOCK_DELETED)
{
/* Use; Unlink it and extend the current block */
DBUG_PRINT("info",("Extending current block"));
if (unlink_deleted_block(info,&del_block))
goto err;
length+=del_block.block_len;
}
}
--------------
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.
Regards,
Monty
