<div id="docs-body">
 <div class="section">
  <div class="titlepage">
   <div>
    <div>
     <h4 class="title">
      <a name="locking-service">
      </a>
      7.6.9.1 The Locking Service
     </h4>
    </div>
   </div>
  </div>
  <a class="indexterm" name="idm46045254516576">
  </a>
  <a class="indexterm" name="idm46045254515088">
  </a>
  <p>
   MySQL distributions provide a locking interface that is
        accessible at two levels:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      At the SQL level, as a set of loadable functions that each
            map onto calls to the service routines.
     </p>
    </li>
    <li class="listitem">
     <p>
      As a C language interface, callable as a plugin service from
            server plugins or loadable functions.
     </p>
    </li>
   </ul>
  </div>
  <p>
   For general information about plugin services, see
   <a class="xref" href="plugin-services.html" title="7.6.9 MySQL Plugin Services">
    Section 7.6.9, “MySQL Plugin Services”
   </a>
   . For general information about
        loadable functions, see
   <a class="ulink" href="/doc/extending-mysql/8.4/en/adding-loadable-function.html" target="_top">
    Adding a Loadable Function
   </a>
   .
  </p>
  <p>
   The locking interface has these characteristics:
  </p>
  <div class="itemizedlist">
   <ul class="itemizedlist" style="list-style-type: disc; ">
    <li class="listitem">
     <p>
      Locks have three attributes: Lock namespace, lock name, and
            lock mode:
     </p>
     <div class="itemizedlist">
      <ul class="itemizedlist" style="list-style-type: circle; ">
       <li class="listitem">
        <p>
         Locks are identified by the combination of namespace and
                lock name. The namespace enables different applications
                to use the same lock names without colliding by creating
                locks in separate namespaces. For example, if
                applications A and B use namespaces of
         <code class="literal">
          ns1
         </code>
         and
         <code class="literal">
          ns2
         </code>
         ,
                respectively, each application can use lock names
         <code class="literal">
          lock1
         </code>
         and
         <code class="literal">
          lock2
         </code>
         without interfering with the other application.
        </p>
       </li>
       <li class="listitem">
        <p>
         A lock mode is either read or write. Read locks are
                shared: If a session has a read lock on a given lock
                identifier, other sessions can acquire a read lock on
                the same identifier. Write locks are exclusive: If a
                session has a write lock on a given lock identifier,
                other sessions cannot acquire a read or write lock on
                the same identifier.
        </p>
       </li>
      </ul>
     </div>
    </li>
    <li class="listitem">
     <p>
      Namespace and lock names must be
            non-
      <code class="literal">
       NULL
      </code>
      , nonempty, and have a maximum
            length of 64 characters. A namespace or lock name specified
            as
      <code class="literal">
       NULL
      </code>
      , the empty string, or a string
            longer than 64 characters results in an
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_locking_service_wrong_name" target="_top">
       <code class="literal">
        ER_LOCKING_SERVICE_WRONG_NAME
       </code>
      </a>
      error.
     </p>
    </li>
    <li class="listitem">
     <p>
      The locking interface treats namespace and lock names as
            binary strings, so comparisons are case-sensitive.
     </p>
    </li>
    <li class="listitem">
     <p>
      The locking interface provides functions to acquire locks
            and release locks. No special privilege is required to call
            these functions. Privilege checking is the responsibility of
            the calling application.
     </p>
    </li>
    <li class="listitem">
     <p>
      Locks can be waited for if not immediately available. Lock
            acquisition calls take an integer timeout value that
            indicates how many seconds to wait to acquire locks before
            giving up. If the timeout is reached without successful lock
            acquisition, an
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_locking_service_timeout" target="_top">
       <code class="literal">
        ER_LOCKING_SERVICE_TIMEOUT
       </code>
      </a>
      error occurs. If the timeout is 0, there is no waiting and
            the call produces an error if locks cannot be acquired
            immediately.
     </p>
    </li>
    <li class="listitem">
     <p>
      The locking interface detects deadlock between
            lock-acquisition calls in different sessions. In this case,
            the locking service chooses a caller and terminates its
            lock-acquisition request with an
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_locking_service_deadlock" target="_top">
       <code class="literal">
        ER_LOCKING_SERVICE_DEADLOCK
       </code>
      </a>
      error. This error does not cause transactions to roll back.
            To choose a session in case of deadlock, the locking service
            prefers sessions that hold read locks over sessions that
            hold write locks.
     </p>
    </li>
    <li class="listitem">
     <p>
      A session can acquire multiple locks with a single
            lock-acquisition call. For a given call, lock acquisition is
            atomic: The call succeeds if all locks are acquired. If
            acquisition of any lock fails, the call acquires no locks
            and fails, typically with an
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_locking_service_timeout" target="_top">
       <code class="literal">
        ER_LOCKING_SERVICE_TIMEOUT
       </code>
      </a>
      or
      <a class="ulink" href="/doc/mysql-errors/8.4/en/server-error-reference.html#error_er_locking_service_deadlock" target="_top">
       <code class="literal">
        ER_LOCKING_SERVICE_DEADLOCK
       </code>
      </a>
      error.
     </p>
    </li>
    <li class="listitem">
     <p>
      A session can acquire multiple locks for the same lock
            identifier (namespace and lock name combination). These lock
            instances can be read locks, write locks, or a mix of both.
     </p>
    </li>
    <li class="listitem">
     <p>
      Locks acquired within a session are released explicitly by
            calling a release-locks function, or implicitly when the
            session terminates (either normally or abnormally). Locks
            are not released when transactions commit or roll back.
     </p>
    </li>
    <li class="listitem">
     <p>
      Within a session, all locks for a given namespace when
            released are released together.
     </p>
    </li>
   </ul>
  </div>
  <p>
   The interface provided by the locking service is distinct from
        that provided by
   <a class="link" href="locking-functions.html#function_get-lock">
    <code class="literal">
     GET_LOCK()
    </code>
   </a>
   and
        related SQL functions (see
   <a class="xref" href="locking-functions.html" title="14.14 Locking Functions">
    Section 14.14, “Locking Functions”
   </a>
   ).
        For example,
   <a class="link" href="locking-functions.html#function_get-lock">
    <code class="literal">
     GET_LOCK()
    </code>
   </a>
   does not
        implement namespaces and provides only exclusive locks, not
        distinct read and write locks.
  </p>
  <div class="section">
   <div class="titlepage">
    <div>
     <div>
      <h5 class="title">
       <a name="locking-service-c-interface">
       </a>
       7.6.9.1.1 The Locking Service C Interface
      </h5>
     </div>
    </div>
   </div>
   <p>
    This section describes how to use the locking service C
          language interface. To use the function interface instead, see
    <a class="xref" href="locking-service.html#locking-service-interface" title="7.6.9.1.2 The Locking Service Function Interface">
     Section 7.6.9.1.2, “The Locking Service Function Interface”
    </a>
    For general
          characteristics of the locking service interface, see
    <a class="xref" href="locking-service.html" title="7.6.9.1 The Locking Service">
     Section 7.6.9.1, “The Locking Service”
    </a>
    . For general information
          about plugin services, see
    <a class="xref" href="plugin-services.html" title="7.6.9 MySQL Plugin Services">
     Section 7.6.9, “MySQL Plugin Services”
    </a>
    .
   </p>
   <p>
    Source files that use the locking service should include this
          header file:
   </p>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting one-line language-c"><div class="docs-select-all right" id="sa83413049"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-c"><span class="token macro property">#<span class="token directive keyword">include</span> <span class="token string">&lt;mysql/service_locking.h&gt;</span></span></code></pre>
   </div>
   <p>
    To acquire one or more locks, call this function:
   </p>
   <a class="indexterm" name="idm46045254474112">
   </a>
   <a class="indexterm" name="idm46045254472592">
   </a>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-c"><div class="docs-select-all right" id="sa39112813"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-c"><span class="token keyword">int</span> <span class="token function">mysql_acquire_locking_service_locks</span><span class="token punctuation">(</span>MYSQL_THD opaque_thd<span class="token punctuation">,</span>
                                        <span class="token keyword">const</span> <span class="token keyword">char</span><span class="token operator">*</span> lock_namespace<span class="token punctuation">,</span>
                                        <span class="token keyword">const</span> <span class="token keyword">char</span><span class="token operator">*</span><span class="token operator">*</span>lock_names<span class="token punctuation">,</span>
                                        size_t lock_num<span class="token punctuation">,</span>
                                        <span class="token keyword">enum</span> enum_locking_service_lock_type lock_type<span class="token punctuation">,</span>
                                        <span class="token keyword">unsigned</span> <span class="token keyword">long</span> lock_timeout<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The arguments have these meanings:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        opaque_thd
       </code>
       : A thread handle. If
              specified as
       <code class="literal">
        NULL
       </code>
       , the handle for the
              current thread is used.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        lock_namespace
       </code>
       : A null-terminated
              string that indicates the lock namespace.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        lock_names
       </code>
       : An array of null-terminated
              strings that provides the names of the locks to acquire.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        lock_num
       </code>
       : The number of names in the
       <code class="literal">
        lock_names
       </code>
       array.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        lock_type
       </code>
       : The lock mode, either
       <code class="literal">
        LOCKING_SERVICE_READ
       </code>
       or
       <code class="literal">
        LOCKING_SERVICE_WRITE
       </code>
       to acquire read
              locks or write locks, respectively.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        lock_timeout
       </code>
       : An integer number of
              seconds to wait to acquire the locks before giving up.
      </p>
     </li>
    </ul>
   </div>
   <p>
    To release locks acquired for a given namespace, call this
          function:
   </p>
   <a class="indexterm" name="idm46045254456160">
   </a>
   <a class="indexterm" name="idm46045254454640">
   </a>
   <div class="copytoclipboard-wrapper">
    <pre class="programlisting language-c"><div class="docs-select-all right" id="sa49687193"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-c"><span class="token keyword">int</span> <span class="token function">mysql_release_locking_service_locks</span><span class="token punctuation">(</span>MYSQL_THD opaque_thd<span class="token punctuation">,</span>
                                        <span class="token keyword">const</span> <span class="token keyword">char</span><span class="token operator">*</span> lock_namespace<span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
   </div>
   <p>
    The arguments have these meanings:
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <code class="literal">
        opaque_thd
       </code>
       : A thread handle. If
              specified as
       <code class="literal">
        NULL
       </code>
       , the handle for the
              current thread is used.
      </p>
     </li>
     <li class="listitem">
      <p>
       <code class="literal">
        lock_namespace
       </code>
       : A null-terminated
              string that indicates the lock namespace.
      </p>
     </li>
    </ul>
   </div>
   <p>
    Locks acquired or waited for by the locking service can be
          monitored at the SQL level using the Performance Schema. For
          details, see
    <a class="xref" href="locking-service.html#locking-service-monitoring" title="Locking Service Monitoring">
     Locking Service Monitoring
    </a>
    .
   </p>
  </div>
  <div class="section">
   <div class="titlepage">
    <div>
     <div>
      <h5 class="title">
       <a name="locking-service-interface">
       </a>
       7.6.9.1.2 The Locking Service Function Interface
      </h5>
     </div>
    </div>
   </div>
   <p>
    This section describes how to use the locking service
          interface provided by its loadable functions. To use the C
          language interface instead, see
    <a class="xref" href="locking-service.html#locking-service-c-interface" title="7.6.9.1.1 The Locking Service C Interface">
     Section 7.6.9.1.1, “The Locking Service C Interface”
    </a>
    For general
          characteristics of the locking service interface, see
    <a class="xref" href="locking-service.html" title="7.6.9.1 The Locking Service">
     Section 7.6.9.1, “The Locking Service”
    </a>
    . For general information
          about loadable functions, see
    <a class="ulink" href="/doc/extending-mysql/8.4/en/adding-loadable-function.html" target="_top">
     Adding a Loadable Function
    </a>
    .
   </p>
   <div class="itemizedlist">
    <ul class="itemizedlist" style="list-style-type: disc; ">
     <li class="listitem">
      <p>
       <a class="xref" href="locking-service.html#locking-service-function-installation" title="Installing or Uninstalling the Locking Service Function Interface">
        Installing or Uninstalling the Locking Service Function Interface
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="locking-service.html#locking-service-function-usage" title="Using the Locking Service Function Interface">
        Using the Locking Service Function Interface
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="locking-service.html#locking-service-monitoring" title="Locking Service Monitoring">
        Locking Service Monitoring
       </a>
      </p>
     </li>
     <li class="listitem">
      <p>
       <a class="xref" href="locking-service.html#locking-service-function-reference" title="Locking Service Interface Function Reference">
        Locking Service Interface Function Reference
       </a>
      </p>
     </li>
    </ul>
   </div>
   <div class="simplesect">
    <div class="titlepage">
     <div>
      <div class="simple">
       <h6 class="title">
        <a name="locking-service-function-installation">
        </a>
        Installing or Uninstalling the Locking Service Function Interface
       </h6>
      </div>
     </div>
    </div>
    <a class="indexterm" name="idm46045254436272">
    </a>
    <a class="indexterm" name="idm46045254434784">
    </a>
    <p>
     The locking service routines described in
     <a class="xref" href="locking-service.html#locking-service-c-interface" title="7.6.9.1.1 The Locking Service C Interface">
      Section 7.6.9.1.1, “The Locking Service C Interface”
     </a>
     need not be
            installed because they are built into the server. The same
            is not true of the loadable functions that map onto calls to
            the service routines: The functions must be installed before
            use. This section describes how to do that. For general
            information about loadable function installation, see
     <a class="xref" href="function-loading.html" title="7.7.1 Installing and Uninstalling Loadable Functions">
      Section 7.7.1, “Installing and Uninstalling Loadable Functions”
     </a>
     .
    </p>
    <p>
     The locking service functions are implemented in a plugin
            library file located in the directory named by the
     <a class="link" href="server-system-variables.html#sysvar_plugin_dir">
      <code class="literal">
       plugin_dir
      </code>
     </a>
     system variable.
            The file base name is
     <code class="literal">
      locking_service
     </code>
     .
            The file name suffix differs per platform (for example,
     <code class="filename">
      .so
     </code>
     for Unix and Unix-like systems,
     <code class="filename">
      .dll
     </code>
     for Windows).
    </p>
    <p>
     To install the locking service functions, use the
     <a class="link" href="create-function.html" title="15.1.14 CREATE FUNCTION Statement">
      <code class="literal">
       CREATE FUNCTION
      </code>
     </a>
     statement,
            adjusting the
     <code class="filename">
      .so
     </code>
     suffix for your
            platform as necessary:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa4666306"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> service_get_read_locks <span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
  <span class="token keyword">SONAME</span> <span class="token string">'locking_service.so'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> service_get_write_locks <span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
  <span class="token keyword">SONAME</span> <span class="token string">'locking_service.so'</span><span class="token punctuation">;</span>
<span class="token keyword">CREATE</span> <span class="token keyword">FUNCTION</span> service_release_locks <span class="token keyword">RETURNS</span> <span class="token datatype">INT</span>
  <span class="token keyword">SONAME</span> <span class="token string">'locking_service.so'</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     If the functions are used on a replication source server,
            install them on all replica servers as well to avoid
            replication problems.
    </p>
    <p>
     Once installed, the functions remain installed until
            uninstalled. To remove them, use the
     <a class="link" href="drop-function.html" title="15.1.26 DROP FUNCTION Statement">
      <code class="literal">
       DROP FUNCTION
      </code>
     </a>
     statement:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa60427808"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> service_get_read_locks<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> service_get_write_locks<span class="token punctuation">;</span>
<span class="token keyword">DROP</span> <span class="token keyword">FUNCTION</span> service_release_locks<span class="token punctuation">;</span></code></pre>
    </div>
   </div>
   <div class="simplesect">
    <div class="titlepage">
     <div>
      <div class="simple">
       <h6 class="title">
        <a name="locking-service-function-usage">
        </a>
        Using the Locking Service Function Interface
       </h6>
      </div>
     </div>
    </div>
    <p>
     Before using the locking service functions, install them
            according to the instructions provided at
     <a class="xref" href="locking-service.html#locking-service-function-installation" title="Installing or Uninstalling the Locking Service Function Interface">
      Installing or Uninstalling the Locking Service Function Interface
     </a>
     .
    </p>
    <p>
     To acquire one or more read locks, call this function:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa56185116"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> service_get_read_locks<span class="token punctuation">(</span><span class="token string">'mynamespace'</span><span class="token punctuation">,</span> <span class="token string">'rlock1'</span><span class="token punctuation">,</span> <span class="token string">'rlock2'</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> service_get_read_locks('mynamespace', 'rlock1', 'rlock2', 10) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                                                             1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
    </div>
    <p>
     The first argument is the lock namespace. The final argument
            is an integer timeout indicating how many seconds to wait to
            acquire the locks before giving up. The arguments in between
            are the lock names.
    </p>
    <p>
     For the example just shown, the function acquires locks with
            lock identifiers
     <code class="literal">
      (mynamespace, rlock1)
     </code>
     and
     <code class="literal">
      (mynamespace, rlock2)
     </code>
     .
    </p>
    <p>
     To acquire write locks rather than read locks, call this
            function:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa63793966"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> service_get_write_locks<span class="token punctuation">(</span><span class="token string">'mynamespace'</span><span class="token punctuation">,</span> <span class="token string">'wlock1'</span><span class="token punctuation">,</span> <span class="token string">'wlock2'</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> service_get_write_locks('mynamespace', 'wlock1', 'wlock2', 10) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                                                              1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
    </div>
    <p>
     In this case, the lock identifiers are
     <code class="literal">
      (mynamespace, wlock1)
     </code>
     and
     <code class="literal">
      (mynamespace, wlock2)
     </code>
     .
    </p>
    <p>
     To release all locks for a namespace, use this function:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30672818"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> service_release_locks<span class="token punctuation">(</span><span class="token string">'mynamespace'</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> service_release_locks('mynamespace') <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                                    1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span></code></pre>
    </div>
    <p>
     Each locking function returns nonzero for success. If the
            function fails, an error occurs. For example, the following
            error occurs because lock names cannot be empty:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa36409363"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> service_get_read_locks<span class="token punctuation">(</span><span class="token string">'mynamespace'</span><span class="token punctuation">,</span> <span class="token string">''</span><span class="token punctuation">,</span> <span class="token number">10</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output">ERROR 3131 (42000)<span class="token punctuation">:</span> Incorrect locking service lock name ''.</span></code></pre>
    </div>
    <p>
     A session can acquire multiple locks for the same lock
            identifier. As long as a different session does not have a
            write lock for an identifier, the session can acquire any
            number of read or write locks. Each lock request for the
            identifier acquires a new lock. The following statements
            acquire three write locks with the same identifier, then
            three read locks for the same identifier:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa30486410"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> service_get_write_locks<span class="token punctuation">(</span><span class="token string">'ns'</span><span class="token punctuation">,</span> <span class="token string">'lock1'</span><span class="token punctuation">,</span> <span class="token string">'lock1'</span><span class="token punctuation">,</span> <span class="token string">'lock1'</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token keyword">SELECT</span> service_get_read_locks<span class="token punctuation">(</span><span class="token string">'ns'</span><span class="token punctuation">,</span> <span class="token string">'lock1'</span><span class="token punctuation">,</span> <span class="token string">'lock1'</span><span class="token punctuation">,</span> <span class="token string">'lock1'</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     If you examine the Performance Schema
     <code class="literal">
      metadata_locks
     </code>
     table at this point, you
            should find that the session holds six distinct locks with
            the same
     <code class="literal">
      (ns, lock1)
     </code>
     identifier. (For
            details, see
     <a class="xref" href="locking-service.html#locking-service-monitoring" title="Locking Service Monitoring">
      Locking Service Monitoring
     </a>
     .)
    </p>
    <p>
     Because the session holds at least one write lock on
     <code class="literal">
      (ns, lock1)
     </code>
     , no other session can acquire
            a lock for it, either read or write. If the session held
            only read locks for the identifier, other sessions could
            acquire read locks for it, but not write locks.
    </p>
    <p>
     Locks for a single lock-acquisition call are acquired
            atomically, but atomicity does not hold across calls. Thus,
            for a statement such as the following, where
     <a class="link" href="locking-service.html#function_service-get-write-locks">
      <code class="literal">
       service_get_write_locks()
      </code>
     </a>
     is
            called once per row of the result set, atomicity holds for
            each individual call, but not for the statement as a whole:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa68678949"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">SELECT</span> service_get_write_locks<span class="token punctuation">(</span><span class="token string">'ns'</span><span class="token punctuation">,</span> <span class="token string">'lock1'</span><span class="token punctuation">,</span> <span class="token string">'lock2'</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1 <span class="token keyword">WHERE</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token punctuation">;</span></code></pre>
    </div>
    <div class="caution" style="margin-left: 0.5in; margin-right: 0.5in;">
     <div class="admon-title">
      Caution
     </div>
     <p>
      Because the locking service returns a separate lock for
              each successful request for a given lock identifier, it is
              possible for a single statement to acquire a large number
              of locks. For example:
     </p>
     <div class="copytoclipboard-wrapper">
      <pre class="programlisting one-line language-sql"><div class="docs-select-all right" id="sa90683664"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token keyword">INSERT</span> <span class="token keyword">INTO</span> <span class="token punctuation">.</span><span class="token punctuation">.</span><span class="token punctuation">.</span> <span class="token keyword">SELECT</span> service_get_write_locks<span class="token punctuation">(</span><span class="token string">'ns'</span><span class="token punctuation">,</span> t1<span class="token punctuation">.</span>col_name<span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span> <span class="token keyword">FROM</span> t1<span class="token punctuation">;</span></code></pre>
     </div>
     <p>
      These types of statements may have certain adverse
              effects. For example, if the statement fails part way
              through and rolls back, locks acquired up to the point of
              failure still exist. If the intent is for there to be a
              correspondence between rows inserted and locks acquired,
              that intent is not satisfied. Also, if it is important
              that locks are granted in a certain order, be aware that
              result set order may differ depending on which execution
              plan the optimizer chooses. For these reasons, it may be
              best to limit applications to a single lock-acquisition
              call per statement.
     </p>
    </div>
   </div>
   <div class="simplesect">
    <div class="titlepage">
     <div>
      <div class="simple">
       <h6 class="title">
        <a name="locking-service-monitoring">
        </a>
        Locking Service Monitoring
       </h6>
      </div>
     </div>
    </div>
    <p>
     The locking service is implemented using the MySQL Server
            metadata locks framework, so you monitor locking service
            locks acquired or waited for by examining the Performance
            Schema
     <code class="literal">
      metadata_locks
     </code>
     table.
    </p>
    <p>
     First, enable the metadata lock instrument:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa34829595"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">UPDATE</span> performance_schema<span class="token punctuation">.</span>setup_instruments <span class="token keyword">SET</span> ENABLED <span class="token operator">=</span> <span class="token string">'YES'</span>
<span class="token prompt">    -&gt;</span> <span class="token keyword">WHERE</span> <span class="token keyword">NAME</span> <span class="token operator">=</span> <span class="token string">'wait/lock/metadata/sql/mdl'</span><span class="token punctuation">;</span></code></pre>
    </div>
    <p>
     Then acquire some locks and check the contents of the
     <code class="literal">
      metadata_locks
     </code>
     table:
    </p>
    <div class="copytoclipboard-wrapper">
     <pre class="programlisting language-sql"><div class="docs-select-all right" id="sa6437262"><div class="copy-help left">Press ⌘+C to copy</div> <div class="right"><button class="clipboard-btn" title="Copy to Clipboard"><span class="icon-clipboard"></span></button></div></div><code class="language-sql"><span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> service_get_write_locks<span class="token punctuation">(</span><span class="token string">'mynamespace'</span><span class="token punctuation">,</span> <span class="token string">'lock1'</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> service_get_write_locks('mynamespace', 'lock1', 0) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                                                  1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> service_get_read_locks<span class="token punctuation">(</span><span class="token string">'mynamespace'</span><span class="token punctuation">,</span> <span class="token string">'lock2'</span><span class="token punctuation">,</span> <span class="token number">0</span><span class="token punctuation">)</span><span class="token punctuation">;</span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span> service_get_read_locks('mynamespace', 'lock2', 0) <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token output"><span class="token punctuation">|</span>                                                 1 <span class="token punctuation">|</span></span>
<span class="token output"><span class="token punctuation">+</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">-</span><span class="token punctuation">+</span></span>
<span class="token prompt">mysql&gt;</span> <span class="token keyword">SELECT</span> OBJECT_TYPE<span class="token punctuation">,</span> OBJECT_SCHEMA<span class="token punctuation">,</span> OBJECT_NAME<span class="token punctuation">,</span> LOCK_TYPE<span class="token punctuation">,</span> LOCK_STATUS
<span class="token prompt">    -&gt;</span> <span class="token keyword">FROM</span> performance_schema<span class="token punctuation">.</span>metadata_locks
<span class="token prompt">    -&gt;</span> <span class="token keyword">WHERE</span> OBJECT_TYPE <span class="token operator">=</span> <span class="token string">'LOCKING SERVICE'</span>\G
<span class="token output"><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 1. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
  OBJECT_TYPE<span class="token punctuation">:</span> LOCKING SERVICE
OBJECT_SCHEMA<span class="token punctuation">:</span> mynamespace
  OBJECT_NAME<span class="token punctuation">:</span> lock1
    LOCK_TYPE<span class="token punctuation">:</span> EXCLUSIVE
  LOCK_STATUS<span class="token punctuation">:</span> GRANTED
<span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span> 2. row <span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span><span class="token punctuation">*</span>
  OBJECT_TYPE<span class="token punctuation">:</span> LOCKING SERVICE
OBJECT_SCHEMA<span class="token punctuation">:</span> mynamespace
  OBJECT_NAME<span class="token punctuation">:</span> lock2
    LOCK_TYPE<span class="token punctuation">:</span> SHARED
  LOCK_STATUS<span class="token punctuation">:</span> GRANTED</span></code></pre>
    </div>
    <p>
     Locking service locks have an
     <code class="literal">
      OBJECT_TYPE
     </code>
     value of
     <code class="literal">
      LOCKING SERVICE
     </code>
     . This is
            distinct from, for example, locks acquired with the
     <a class="link" href="locking-functions.html#function_get-lock">
      <code class="literal">
       GET_LOCK()
      </code>
     </a>
     function, which
            have an
     <code class="literal">
      OBJECT_TYPE
     </code>
     of
     <code class="literal">
      USER
            LEVEL LOCK
     </code>
     .
    </p>
    <p>
     The lock namespace, name, and mode appear in the
     <code class="literal">
      OBJECT_SCHEMA
     </code>
     ,
     <code class="literal">
      OBJECT_NAME
     </code>
     , and
     <code class="literal">
      LOCK_TYPE
     </code>
     columns. Read and write locks
            have
     <code class="literal">
      LOCK_TYPE
     </code>
     values of
     <code class="literal">
      SHARED
     </code>
     and
     <code class="literal">
      EXCLUSIVE
     </code>
     ,
            respectively.
    </p>
    <p>
     The
     <code class="literal">
      LOCK_STATUS
     </code>
     value is
     <code class="literal">
      GRANTED
     </code>
     for an acquired lock,
     <code class="literal">
      PENDING
     </code>
     for a lock that is being waited
            for. You can expect to see
     <code class="literal">
      PENDING
     </code>
     if one
            session holds a write lock and another session is attempting
            to acquire a lock having the same identifier.
    </p>
   </div>
   <div class="simplesect">
    <div class="titlepage">
     <div>
      <div class="simple">
       <h6 class="title">
        <a name="locking-service-function-reference">
        </a>
        Locking Service Interface Function Reference
       </h6>
      </div>
     </div>
    </div>
    <p>
     The SQL interface to the locking service implements the
            loadable functions described in this section. For usage
            examples, see
     <a class="xref" href="locking-service.html#locking-service-function-usage" title="Using the Locking Service Function Interface">
      Using the Locking Service Function Interface
     </a>
     .
    </p>
    <p>
     The functions share these characteristics:
    </p>
    <div class="itemizedlist">
     <ul class="itemizedlist" style="list-style-type: disc; ">
      <li class="listitem">
       <p>
        The return value is nonzero for success. Otherwise, an
                error occurs.
       </p>
      </li>
      <li class="listitem">
       <p>
        Namespace and lock names must be
                non-
        <code class="literal">
         NULL
        </code>
        , nonempty, and have a
                maximum length of 64 characters.
       </p>
      </li>
      <li class="listitem">
       <p>
        Timeout values must be integers indicating how many
                seconds to wait to acquire locks before giving up with
                an error. If the timeout is 0, there is no waiting and
                the function produces an error if locks cannot be
                acquired immediately.
       </p>
      </li>
     </ul>
    </div>
    <p>
     These locking service functions are available:
    </p>
    <div class="itemizedlist">
     <ul class="itemizedlist" style="list-style-type: disc; ">
      <li class="listitem">
       <p>
        <a name="function_service-get-read-locks">
        </a>
        <a class="link" href="locking-service.html#function_service-get-read-locks">
         <code class="literal">
          service_get_read_locks(
          <em class="replaceable">
           <code>
            namespace
           </code>
          </em>
          ,
          <em class="replaceable">
           <code>
            lock_name
           </code>
          </em>
          [,
          <em class="replaceable">
           <code>
            lock_name
           </code>
          </em>
          ] ...,
          <em class="replaceable">
           <code>
            timeout
           </code>
          </em>
          )
         </code>
        </a>
       </p>
       <a class="indexterm" name="idm46045254353456">
       </a>
       <a class="indexterm" name="idm46045254352000">
       </a>
       <p>
        Acquires one or more read (shared) locks in the given
                namespace using the given lock names, timing out with an
                error if the locks are not acquired within the given
                timeout value.
       </p>
      </li>
      <li class="listitem">
       <p>
        <a name="function_service-get-write-locks">
        </a>
        <a class="link" href="locking-service.html#function_service-get-write-locks">
         <code class="literal">
          service_get_write_locks(
          <em class="replaceable">
           <code>
            namespace
           </code>
          </em>
          ,
          <em class="replaceable">
           <code>
            lock_name
           </code>
          </em>
          [,
          <em class="replaceable">
           <code>
            lock_name
           </code>
          </em>
          ] ...,
          <em class="replaceable">
           <code>
            timeout
           </code>
          </em>
          )
         </code>
        </a>
       </p>
       <a class="indexterm" name="idm46045254345600">
       </a>
       <a class="indexterm" name="idm46045254344096">
       </a>
       <p>
        Acquires one or more write (exclusive) locks in the
                given namespace using the given lock names, timing out
                with an error if the locks are not acquired within the
                given timeout value.
       </p>
      </li>
      <li class="listitem">
       <p>
        <a name="function_service-release-locks">
        </a>
        <a class="link" href="locking-service.html#function_service-release-locks">
         <code class="literal">
          service_release_locks(
          <em class="replaceable">
           <code>
            namespace
           </code>
          </em>
          )
         </code>
        </a>
       </p>
       <a class="indexterm" name="idm46045254338896">
       </a>
       <a class="indexterm" name="idm46045254337440">
       </a>
       <p>
        For the given namespace, releases all locks that were
                acquired within the current session using
        <a class="link" href="locking-service.html#function_service-get-read-locks">
         <code class="literal">
          service_get_read_locks()
         </code>
        </a>
        and
        <a class="link" href="locking-service.html#function_service-get-write-locks">
         <code class="literal">
          service_get_write_locks()
         </code>
        </a>
        .
       </p>
       <p>
        It is not an error for there to be no locks in the
                namespace.
       </p>
      </li>
     </ul>
    </div>
   </div>
  </div>
 </div>
 <br/>
</div>
