← Back to team overview

drizzle-discuss team mailing list archive

Re: Improving the Engine API


Hi Jay,

Yes, how ALTER TABLE, TRUNCATE, CHECK, etc is handled does depend (almost entirely) on the engine.

I actually think that, for the moment anyway, the statement type will be sufficient.

Maybe I have misunderstood what you mean by pluggable statements, but lets assume some engine adds a custom statement: "ENCRYPT TABLE".

My engine will not know what type of lock to acquire for this statement, but on the other hand, it will also not know how to execute the statement. So it must reject the statement outright.

Which means an engine needs a list of statements it supports, and everything else must be rejected. So the engine needs to know the statement type.

I think it is important that we distinguish between statements and operations.

In general, MySQL implements the ALTER TABLE using 3 operations: create-table, rename-table and delete-table.

The cool thing is: if my engine can execute these 3 operations, then it can perform all ALTER TABLE statements, although the implementation is not optimal. To implement this better, the engine needs to support a custom alter-table operation. But the engine's alter-table operation may, for example, only support ADD INDEX.

So basically, the engine examines the statement in startStatement(), add decides on one of the following:

- Use the default execution method for the statement
- Use the engine custom statement method
- Reject the statement as not implemented

It also decides (and maybe acquires) the locks it will need, and does any other statement initialization.

So here is an example of the call sequence, if the engine uses the default method for alter table:

startStatement("ALTER TABLE", "t1") --> return: use default method
// Copy all rows from "t1" to "tmp" using standard cursor operations
renameTable("t1", "tmp2") // t1 --> tmp2
renameTable("tmp", "t1") // tmp --> t1

So Drizzle does not know anything about table locks. The engine knows it is in an ALTER TABLE statement, and knows (for example) that table t1 must be read locked when the row copy begins (i.e. when the cursor is opened), and that an exclusive lock is required to rename the table.

And this is how the engine would handle "ADD INDEX", or "ENCRYPT TABLE":

startStatement("ENCRYPT TABLE", "t1") --> return: use custom method
doTableOperation("ENCRYPT TABLE", "t1")

The engine can write table operations to its transaction log, and in this way it could ensure that the entire ALTER TABLE statement is atomic.

On Dec 7, 2009, at 4:10 PM, Jay Pipes wrote:

Paul McCullagh wrote:
Hi Toru,
On Dec 7, 2009, at 3:31 AM, Toru Maesaka wrote:
Great to hear another use-case where knowing a statement type in
advance is useful :)
Yes, generally I need to know the following:
- If I have a update type statement (i.e. whether the statement modifies rows). - Whether I need a table lock (examples: ALTER TABLE, TRUNCATE, CHECK).

But, Paul, doesn't this depend on the engine itself?  I mean, some
engines can do (some types of) ALTER TABLE without taking a table lock.
So, is this request really for whether the kernel thinks a table-level
lock is necessary, or is it really just for a descriptor of the
statement type?

And, if it really does just boil down to the statement type, then how do we deal with the reality that Brian speaks about -- that statement type
will be pluggable, and how do we deal with future statement types for
pluggable engines?

Is a reasonable solution to pass to engines a sort of "statement
traits"?  So, instead of passing ALTER_TABLE, CREATE_TABLE, UPDATE,
DELETE, etc, we instead pass a std::bitset<> (or uint64_t for C folks)
containing traits of the statement such as:

etc, etc

And then to deal with transaction locking concerns, just add a method to Cursor:

void Cursor::setTransactionIsolationLevel(enum enum_tx_isolation);



- If we have a SELECT FOR UPDATE.
I was talking to Toru about this, and another possibility is that we have statements declare a needed "lock type" that any plugin could then query. I outlined the solution for Toru, but I don't know if he has written the patch yet :)

I've taken notes from our discussion the other day. I'm planning on
working on it when I finish testing through my current progress of
Great! :)
For now, I'm happy with Jay's advise of using


On Sat, Dec 5, 2009 at 5:59 AM, Brian Aker <brian@xxxxxxxxxxx> wrote:

On Dec 4, 2009, at 3:12 AM, Paul McCullagh wrote:

If we have a startStatement() call, then it could be used in place of beginAlter(), assuming we can determine the statement type, and the tables involved.

The problem with relying on statement type is that at some point statement type will be pluggable... which means you would constantly need to update your engine for new statements.


I was talking to Toru about this, and another possibility is that we have statements declare a needed "lock type" that any plugin could then query. I outlined the solution for Toru, but I don't know if he has written the patch yet :)

Then, when a handle is returned to the pool it is deleted, instead of adding it back to the pool.

BTW very soon engines will own their Cursor objects and will be free to reuse them.

The locking thread waits until all handles are returned and deleted before it can proceed. The lock on the pool then prevents a new table handle from being created while the locking thread is busy. Either way, it would be good if Drizzle closes all handlers/ cursors before a table is deleted or renamed.

I would say that long term this will be optional, based on what the engine requires.

OK, this make things a lot simpler! Indeed, if we don't need to support LOCK TABLE then external_lock() can be removed altogether.

Tried removing the external_lock() right now and seeing if any issues pop up?

Paul McCullagh
PrimeBase Technologies

Paul McCullagh
PrimeBase Technologies

Follow ups