why introduce mysql named lock in lock_tables_if function call

[swingbach]

when I execute this line in MySQL select get_lock("handlersocket_wr",10); and then to insert a new row using handlersocket,it fails and return a error message

i was wondering why introduce this user level lock in the function call lock_tables_if which do a named lock which cause all the write be completely serializable

i thought innodb and mysql can do all the lock things,why the user level lock is needed.

[ahiguti]

This advisory lock is necessary in order to avoid deadlocks among writer threads. Because writer threads execute multiple updates/inserts in a single transaction without sorting them, they would deadlock without an advisory lock.

Moreover, serializing writer threads is desirable for MySQL's single-threaded replication (as of 5.5), which is the real bottleneck for update-intensive workloads. You need not to worry about replication lag if you use HandlerSocket for updating data. Grouping multiple requests is a huge performance gain for update-intensive workloads (100 times faster in a truly durable configuration), and it's effective for slaves also.

[swingbach]

when will this happen : "there are more than one updates/inserts statement in a single transaction". Is that a performance optimization by collecting statement together and execute it once.

Can I remove the advisory code if I use simple single statement only?

[ahiguti]

Yes, it's a performance optimization.

You should not remove the advisory lock from database.cpp even if you only use simple queries.