hi Daniel!

2014-11-25 1:03 GMT-02:00 Daniel Black <daniel.black@openquery.com>:

----- Original Message -----
> hi guys, i use read uncommited sometimes with innodb, that's nice and work

too scared to ask why. Its nice until the one day it shoots you.

no problem, i use with data that don't need fully consistency

but this problem isn't my design, that's why i'm searching some solution

just some points to avoid thinking that i'm a newbie trying to use myisam as innodb cause it have better count(*) speed than innodb or anything like it...

i like the innodb and myisam when right used, and yes innodb can do this job with transactions, i know isolation level "read uncommited" is for some transactional engine and not all engine can use it, that's engine specific

i don't remember what engine don't allow this, but i read something about it

myisam and aria don't execute as transactional engine, they have a non transacitonal model, ok i know this, i'm testing what it can really do

i know that's something that maybe the solution don't exists today with standard mysql/mariadb engines

maybe there's another engine "plug and play", i only don't know what engine and how complex is implement this if no one solve, yes i don't know how complex is change this applicatoin since i don't have the source code... i will execute query rewrite

forgetting the "solution" of 'use innodb instead of myisam'... (that's valid solution)

there's space to implement some new feature to aria/myisam? i think aria is better in this case cause it have jornalling, or maybe any other solution?

the table don't have many update/delete and writes are concurrent inserts, it's like a historical table

i don't have access to source code of application sending the queries, i will implement something between client and mariadb to rewrite the queries, i know exactly what query must execute in this kind of "feature" since app send comments telling what 'module' is executing the query, something like

"/* module=1242 */ SELECT ...."

> but now, i'm using a myisam table, and a aria table....
> does read uncommited work with this kind of engine?

There is never any uncommitted as a table lock is held when writing occurs. Reads wait until that finishes.

nice, confirmed with you too :)

that was a doubt, since transactional level is realated to transactional engine, i never tested if it could change something of non trasactional engines, a idiot doubt i know... but only testing to really know 100% if this change something, reading the docs or reading the myisam/aria source code, the easier method was testing... =]

that's probably expected

from what i know about myisam/aria, a table write lock block the table read lock (i can check this with meta data lock plugin), and read only execute after release of write lock, some writes can execute "without" "big" locks, like concurrent inserts, right?

but instead of a read lock waiting write locks, i'm thinking about a one new "level of lock" inside myisam (or aria), i don't know how to name this "level of lock" using the myisam/aria words, with transactional engines that's a transactional isolation level

no problem of slowing down the update query or the select query or the whole table read/write, and no problem about reading garbage (uncommited data) by select (that's expected to result "old" data)

i don't know 100% how write works with myisam/aria, but at a high level i think the only problem is reading one row while write is being executed, probably this need a row level locking or a 'range level lock' or a jornal method (like aria), but i'm thinking if some kind of non transactional model could be used to read uncommited data, i will try to run 2 or more mysqld with same mysql table and check if any problem occur, probably yes...

check that i don't care about transaction in this case, transaction solve the problem of course, but i'm thinking if there's a non transactional solution, and if exists how complex could be implement it, cause i didn't checked yet how complex is rewriting the whole queries from app to run with transactions, ideas and experiences with this are wellcome

> i tested and table stay
> 'waiting table lock' while a looooong update occurs

Right (+write) - as above.

yeap, the normal "problem" (solution) of write lock

what i didn't tested before is the transactional level with non transactional tables... the "dumb doubt"

> i don't know what's the internal diference but is possible to "easily"
> implement read uncommited to myisam/aria?

It is possible to change to easily change to innodb

i don't have the source code of app, probably i could execute a "proxy" between client and server to rewrite and implement begin/commit, and execute single statistics that myisam have (max/min/count) with triggers and statistics tables, rewrite some queries, etc...

but this is a bit "complex", i don't know how complex it is yet, i didn't tested and didn't tried to convert a whole system without changing source code

i know that i'm with a design problem, probably the 'easily' solution is contacting the developer

, an actually transactional storage engine, for the transaction features you want to use?

well... i don't know if this need all transactional features of innodb, i didn't checked yet if the row length could break innodb engine, and i should partition this table and create a view or a vertical partition table with spider

i was thinking about ACID:

* Atomicity - i don't need and myisam/aria don't have (aria "partially atomicity")

* Consistency - no need... only unique keys check

* Isolation - that's my doubt

today myisam serialize writes that's a lock method, not a isolation method

with read it do a 'uncommited' (execute reads with locks), it's a "lock oriented" engine

what i'm thinking is write execute normally as myisam/aria do, only select with read uncommited could read while writing, others selects should wait table write lock as normal

i don't know how complex, but maybe the simple solutions is a read that change writes to wait or something like this, i really don't know yet what's the solution, just trying to check if any idea could be possible

this will increase some lock contention or anyother contention (or maybe reduce it)... anything better than long write lock is ok

* Durability - execute as myisam/aria do today

i think aria have better support to this since aria have jornal log and some kind of multi versioning

aria write to jornal, and after write to file, the file have the 'uncommited data' and the jornal+file have the "commited" data

what i'm thinking with aria:

1) when executing this selects (uncommited) we could ensure that jornal be write to file before starting select

2) writing from jornal to file is blocked

3) when select end, the write from jornal to file is released

well i don't know if this is possible, just an idea...

maybe no other engine do this in this way (non transactional), i didn't checked yet nosql storages/plugins solutions, any idea?

sorry many doubts and questions, i'm thinking about possible solutions

Roberto Spadim