RFC: Locking functions

[systay]

Locking Functions

Supporting the advisory locking functions in MySQL is important, given that they are used by common applications and frameworks.

Functions covered

• GET_LOCK()
• IS_FREE_LOCK()
• IS_USED_LOCK()
• RELEASE_ALL_LOCKS()
• RELEASE_LOCK()

Restrictions

Vitess will initially only support locking functions with these limitations:

• Can only be used in SELECT queries
• The queries can either have only the table dual, or have no FROM clause.

Functionality

Locking function evaluation will have a simple and consistent routing scheme, making sure all requests happen at the same target. This way, locks will be executed on the same mysqld.
The locking function evaluation always be routed to the first shard in the first keyspace known to the VTGate, sorted alphabetically.

Using any of the locking functions will force the session to use a reserved connection - a dedicated connection to mysqld for that session, so that get_lock()/release_lock() happen on the same connection, and so that COM_QUIT releases any lingering locks.

Examples of valid queries

SELECT GET_LOCK('lock1',10);
SELECT RELEASE_LOCK('lock1');
SELECT GET_LOCK('lock1',10), GET_LOCK('lock2',10);
SELECT RELEASE_ALL_LOCKS()
SELECT GET_LOCK(@customVariable, 10);

Examples of queries not supported in the first implementation

SELECT GET_LOCK(user_name,10) FROM users;
INSERT INTO T (id) VALUES (GET_LOCK('lock2',10));
DO GET_LOCK('lock1',10);

[shlomi-noach]

• Can only be used in SELECT queries
• The queries can either have only the table dual, or have no FROM clause.

I think these are reasonable restrictions.