Bug #16501 IS_USED_LOCK does not appear to work

Description:
IS_USED_LOCK can report the wrong connection identifier for a lock.

How to repeat:
Open up two connections to mysql, connection 1 and connection 2.  Run the following statements, as designated by their connection:
1:
    SELECT GET_LOCK('42',600);
 
2:
    SELECT IS_USED_LOCK('42');
    SELECT GET_LOCK('42',600);
 
1:
    SELECT RELEASE_LOCK('42');
    SELECT IS_USED_LOCK('42');

Suggested fix:
The last IS_USED_LOCK should report the connection identifier for connection 2, since connection 2 actually holds the lock.

Thank you for a bug report. Verified just as described with 4.1.17-BK (ChangeSet@1.2475, 2006-01-11 17:31:52+03:00) on Linux. From the first session (connection id 5) I've got:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.17-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT GET_LOCK('42',600);
+--------------------+
| GET_LOCK('42',600) |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.05 sec)

mysql> SELECT RELEASE_LOCK('42');
+--------------------+
| RELEASE_LOCK('42') |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.01 sec)

mysql> SELECT IS_USED_LOCK('42');
+--------------------+
| IS_USED_LOCK('42') |
+--------------------+
|                  5 |
+--------------------+
1 row in set (0.00 sec)

Note, that connection identifier is still 5. Connection identifier for the lock is not updated. This is a bug.

We need to fix it before 4.1.20 release

I wasn't able to repeat Valeriy's shortened test case, below is the patch that fixes the original problem:

===== item_func.cc 1.259 vs edited =====
--- 1.259/sql/item_func.cc	2006-04-12 23:02:02 +04:00
+++ edited/item_func.cc	2006-04-22 21:31:34 +04:00
@@ -1972,6 +1972,7 @@
 void item_user_lock_release(User_level_lock *ull)
 {
   ull->locked=0;
+  ull->thread_id= 0;
   if (mysql_bin_log.is_open())
   {
     char buf[256];
@@ -2170,6 +2171,7 @@
   {
     ull->locked=1;
     ull->thread=thd->real_id;
+    ull->thread_id= thd->thread_id;
     thd->ull=ull;
     error=0;
   }

(Tomash, this is just to save you investigation time).

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/5405

Approved by email.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6056

Fixed in 4.1.20, 5.0.22, 5.1.11.

Noted in 4.1.20, 5.0.22, 5.1.11 changelogs.

<literal>IS_USED_LOCK()</literal> could return an incorrect
connection identifier.