Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-25210

why sub query need LOCK_R when SQLCOM_UPDATE_MULTI ?

    XMLWordPrintable

Details

    Description

      READ-COMMITTED:

      innodb READ-COMMITTED:

      table:

      CREATE TABLE `test` ( `key1` int(11) NOT NULL, `key2` int(11) DEFAULT NULL, PRIMARY KEY (`key1`) ) ENGINE=innodb DEFAULT CHARSET=utf8;
       
      CREATE TABLE `test1` ( `key1` int(11) NOT NULL, `key2` int(11) DEFAULT NULL, PRIMARY KEY (`key1`) ) ENGINE=innodb DEFAULT CHARSET=utf8;
       
      insert into test values(1,1);
      insert into test1 values(1,1);
      

      case 1:

      connection 1:
      begin;
      update test set key2=111 where key1 in (select key1 from test1);
      // test1 lock with LOCK_NONE (no lock); (sql_command:SQLCOM_UPDATE )
       
      then 
      connection 2:
      update test1 set key2=9994;
      Query OK, 1 row affected (0.091 sec)
      Rows matched: 1  Changed: 1  Warnings: 0
      

      case 2:

      connection 1:
      begin;
      update test a inner join (select key1 from test1) b on a.key1=b.key1 set a.key2=111;
      // test1 lock with LOCK_S (sql_command:SQLCOM_UPDATE_MULTI )
       
      then 
      connection 2:
      update test1 set key2=9994;
      ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
      
      

      store_lock
       
      		if (sql_command == SQLCOM_CHECKSUM
      		    || sql_command == SQLCOM_CREATE_SEQUENCE
      		    || (sql_command == SQLCOM_ANALYZE && lock_type == TL_READ)
      		    || ((srv_locks_unsafe_for_binlog
      			|| trx->isolation_level <= TRX_ISO_READ_COMMITTED)
      			&& trx->isolation_level != TRX_ISO_SERIALIZABLE
      			&& (lock_type == TL_READ
      			    || lock_type == TL_READ_NO_INSERT)
      			&& (sql_command == SQLCOM_INSERT_SELECT
      			    || sql_command == SQLCOM_REPLACE_SELECT
      			    || sql_command == SQLCOM_UPDATE
      			    || sql_command == SQLCOM_CREATE_SEQUENCE
      			    || sql_command == SQLCOM_CREATE_TABLE))) {
       
      			/* If we either have innobase_locks_unsafe_for_binlog
      			option set or this session is using READ COMMITTED
      			isolation level and isolation level of the transaction
      			is not set to serializable and MySQL is doing
      			INSERT INTO...SELECT or REPLACE INTO...SELECT
      			or UPDATE ... = (SELECT ...) or CREATE  ...
      			SELECT... without FOR UPDATE or IN SHARE
      			MODE in select, then we use consistent read
      			for select. */
       
      			m_prebuilt->select_lock_type = LOCK_NONE;
      			m_prebuilt->stored_select_lock_type = LOCK_NONE;
      		} else {
      			m_prebuilt->select_lock_type = LOCK_S;
      			m_prebuilt->stored_select_lock_type = LOCK_S;
      		}
      
      

      why SQLCOM_UPDATE_MULTI can‘t be LOCK_NONE ?

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              cstarc chu huaxing
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.