Small improvement to alloc_root

Add support for LIMIT # OFFSET #
Changed lock handling:  Now all locks should be stored in TABLE_LIST instead of passed to functions.
Don't call query_cache_invalidate() twice in some cases
mysql_change_user() now clears states to be equal to close + connect.
Fixed a bug with multi-table-update and multi-table-delete when used with LOCK TABLES
Fixed a bug with replicate-do and UPDATE


BitKeeper/etc/ignore:
  added autom4te.cache/* bdb/dist/autom4te.cache/* innobase/autom4te.cache/*
include/my_alloc.h:
  Small improvement to alloc_root
libmysql/libmysql.c:
  Removed compiler warning
myisam/mi_page.c:
  Better DBUG message
mysql-test/r/multi_update.result:
  Added test with lock tables
mysql-test/r/rpl_replicate_do.result:
  Update results
mysql-test/r/rpl_rotate_logs.result:
  Make test independent of if t1 exists
mysql-test/t/multi_update.test:
  Added test with lock tables
mysql-test/t/rpl_rotate_logs.test:
  Make test independent of if t1 exists
mysys/my_alloc.c:
  Small imprevement to alloc_root
  (Don't free blocks less than ALLOC_MAX_BLOCK_ROOT (4K)
sql/ha_innodb.cc:
  More debug messages
sql/ha_myisam.cc:
  Safety change
sql/lex.h:
  Add support for LIMIT # OFFSET #
sql/lock.cc:
  Added assertion
sql/mysql_priv.h:
  Change of lock handling
sql/mysqld.cc:
  Added function clear_error_messages()
sql/sql_base.cc:
  Change lock handling by open_ltable() and open_and_lock_tables()
sql/sql_class.cc:
  Split THD::THD to two functions
  Move some code from cleanup() to ~THD:THD
  Add THD::change_user()
sql/sql_class.h:
  Prototype changes in class THD
sql/sql_delete.cc:
  Remove locking argument from mysql_delete()
  Locking type is now stored in TABLE_LIST
  Small code change to not call query_cache_invalidate() twice for transactional tables.
sql/sql_insert.cc:
  Remove locking argument from mysql_insert()
  Locking type is now stored in TABLE_LIST
  Small code change to not call query_cache_invalidate() twice for transactional tables.
  Don't use bulk insert if bulk_insert_buff_size is 0
sql/sql_parse.cc:
  Changes to make mysql_change_user() work as close+connect
  Changed command statistics to use statstics_increment to get more speed
  Update code to handle that locks is now stored in TABLE_LIST
sql/sql_update.cc:
  Remove locking argument from mysql_update()
  Locking type is now stored in TABLE_LIST
  Small code change to not call query_cache_invalidate() twice for transactional tables.
sql/sql_yacc.yy:
  Locking type is now stored in TABLE_LIST
  Added support for LIMIT # OFFSET # syntax
  Removed some wrong (never true) checks for SQLCOM_MULTI_UPDATE
mysql-test/t/rpl_replicate_do-slave.opt:
  Changed tables to use t1,t2,...
mysql-test/t/rpl_replicate_do.test:
  Changed tables to use t1,t2,...

14 files changed, 393 insertions, 204 deletions

diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc
index d4e5d0e43cf..2ce7736001b 100644
--- a/sql/ha_innodb.cc
+++ b/sql/ha_innodb.cc
@@ -3785,6 +3785,7 @@ ha_innobase::external_lock(
 	trx_t*		trx;
 
   	DBUG_ENTER("ha_innobase::external_lock");
+	DBUG_PRINT("enter",("lock_type: %d", lock_type));
 
 	update_thd(thd);
 
diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc
index b4ca822784a..088bad9a84b 100644
--- a/sql/ha_myisam.cc
+++ b/sql/ha_myisam.cc
@@ -912,7 +912,7 @@ void ha_myisam::info(uint flag)
     if (table->key_parts)
       memcpy((char*) table->key_info[0].rec_per_key,
 	     (char*) info.rec_per_key,
-	     sizeof(ulong)*table->key_parts);
+	     sizeof(table->key_info[0].rec_per_key)*table->key_parts);
     raid_type=info.raid_type;
     raid_chunks=info.raid_chunks;
     raid_chunksize=info.raid_chunksize;
diff --git a/sql/lex.h b/sql/lex.h
index dba919ce366..49b6a3811e5 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -255,6 +255,7 @@ static SYMBOL symbols[] = {
   { "NOT",		SYM(NOT),0,0},
   { "NULL",		SYM(NULL_SYM),0,0},
   { "NUMERIC",		SYM(NUMERIC_SYM),0,0},
+  { "OFFSET",		SYM(OFFSET_SYM),0,0},
   { "ON",		SYM(ON),0,0},
   { "OPEN",		SYM(OPEN_SYM),0,0},
   { "OPTIMIZE",		SYM(OPTIMIZE),0,0},
diff --git a/sql/lock.cc b/sql/lock.cc
index 9063b1273e0..15878fe7d15 100644
--- a/sql/lock.cc
+++ b/sql/lock.cc
@@ -167,6 +167,7 @@ static int lock_external(THD *thd, TABLE **tables, uint count)
 
   for (i=1 ; i <= count ; i++, tables++)
   {
+    DBUG_ASSERT((*tables)->reginfo.lock_type >= TL_READ);
     lock_type=F_WRLCK;				/* Lock exclusive */
     if ((*tables)->db_stat & HA_READ_ONLY ||
 	((*tables)->reginfo.lock_type >= TL_READ &&
diff --git a/sql/mysql_priv.h b/sql/mysql_priv.h
index a763bdd35ad..db457aa0aa7 100644
--- a/sql/mysql_priv.h
+++ b/sql/mysql_priv.h
@@ -413,14 +413,12 @@ int mysql_drop_index(THD *thd, TABLE_LIST *table_list,
 int mysql_update(THD *thd,TABLE_LIST *tables,List<Item> &fields,
 		 List<Item> &values,COND *conds, 
                  ORDER *order, ha_rows limit,
-		 enum enum_duplicates handle_duplicates,
-		 thr_lock_type lock_type);
+		 enum enum_duplicates handle_duplicates);
 int mysql_insert(THD *thd,TABLE_LIST *table,List<Item> &fields,
-		 List<List_item> &values, enum_duplicates flag,
-		 thr_lock_type lock_type);
+		 List<List_item> &values, enum_duplicates flag);
 void kill_delayed_threads(void);
 int mysql_delete(THD *thd, TABLE_LIST *table, COND *conds, ORDER *order,
-                 ha_rows rows, thr_lock_type lock_type, ulong options);
+                 ha_rows rows, ulong options);
 int mysql_truncate(THD *thd, TABLE_LIST *table_list, bool dont_send_ok=0);
 TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type update);
 TABLE *open_table(THD *thd,const char *db,const char *table,const char *alias,
@@ -496,6 +494,7 @@ TABLE_LIST *add_table_to_list(Table_ident *table,LEX_STRING *alias,
 			      thr_lock_type flags=TL_UNLOCK,
 			      List<String> *use_index=0,
 			      List<String> *ignore_index=0);
+void set_lock_for_tables(thr_lock_type lock_type);
 void add_join_on(TABLE_LIST *b,Item *expr);
 void add_join_natural(TABLE_LIST *a,TABLE_LIST *b);
 bool add_proc_to_list(Item *item);
@@ -586,6 +585,8 @@ bool open_log(MYSQL_LOG *log, const char *hostname,
 	      const char *index_file_name,
 	      enum_log_type type, bool read_append = 0,
 	      bool no_auto_events = 0);
+/* mysqld.cc */
+void clear_error_message(THD *thd);
 
 /*
   External variables
diff --git a/sql/mysqld.cc b/sql/mysqld.cc
index f2a536ada9b..cfb128f6dd6 100644
--- a/sql/mysqld.cc
+++ b/sql/mysqld.cc
@@ -1663,6 +1663,17 @@ extern "C" int my_message_sql(uint error, const char *str,
   DBUG_RETURN(0);
 }
 
+
+/*
+  Forget last error message (if we got one)
+*/
+
+void clear_error_message(THD *thd)
+{
+  thd->net.last_error[0]= 0;
+}
+
+
 #ifdef __WIN__
 
 struct utsname
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 23ba04bd6ed..032882080ff 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -1403,6 +1403,61 @@ int open_tables(THD *thd,TABLE_LIST *start)
 }
 
 
+/*
+  Check that lock is ok for tables; Call start stmt if ok
+
+  SYNOPSIS
+    check_lock_and_start_stmt()
+    thd			Thread handle
+    table_list		Table to check
+    lock_type		Lock used for table
+
+  RETURN VALUES
+  0	ok
+  1	error
+*/
+
+static bool check_lock_and_start_stmt(THD *thd, TABLE *table,
+				      thr_lock_type lock_type)
+{
+  int error;
+  DBUG_ENTER("check_lock_and_start_stmt");
+
+  if ((int) lock_type >= (int) TL_WRITE_ALLOW_READ &&
+      (int) table->reginfo.lock_type < (int) TL_WRITE_ALLOW_READ)
+  {
+    my_printf_error(ER_TABLE_NOT_LOCKED_FOR_WRITE,
+		    ER(ER_TABLE_NOT_LOCKED_FOR_WRITE),
+		    MYF(0),table->table_name);
+    DBUG_RETURN(1);
+  }
+  if ((error=table->file->start_stmt(thd)))
+  {
+    table->file->print_error(error,MYF(0));
+    DBUG_RETURN(1);
+  }
+  DBUG_RETURN(0);
+}
+
+
+/*
+  Open and lock one table
+
+  SYNOPSIS
+    open_ltable()
+    thd			Thread handler
+    table_list		Table to open is first table in this list
+    lock_type		Lock to use for open
+
+  RETURN VALUES
+    table		Opened table
+    0			Error
+  
+    If ok, the following are also set:
+      table_list->lock_type 	lock_type
+      table_list->table		table
+*/
+
 TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
 {
   TABLE *table;
@@ -1415,8 +1470,6 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
 			    &refresh)) && refresh) ;
   if (table)
   {
-    int error;
-
 #if defined( __WIN__) || defined(OS2)
     /* Win32 can't drop a file that is open */
     if (lock_type == TL_WRITE_ALLOW_READ)
@@ -1424,39 +1477,29 @@ TABLE *open_ltable(THD *thd, TABLE_LIST *table_list, thr_lock_type lock_type)
       lock_type= TL_WRITE;
     }
 #endif /* __WIN__ || OS2 */
-
-    table_list->table=table;
+    table_list->lock_type= lock_type;
+    table_list->table=	   table;
     table->grant= table_list->grant;
     if (thd->locked_tables)
     {
-      thd->proc_info=0;
-      if ((int) lock_type >= (int) TL_WRITE_ALLOW_READ &&
-	  (int) table->reginfo.lock_type < (int) TL_WRITE_ALLOW_READ)
-      {
-	my_printf_error(ER_TABLE_NOT_LOCKED_FOR_WRITE,
-			ER(ER_TABLE_NOT_LOCKED_FOR_WRITE),
-			MYF(0),table_list->alias);
-	table=0;
-      }
-      else if ((error=table->file->start_stmt(thd)))
-      {
-	table->file->print_error(error,MYF(0));
-	table=0;
-      }
-      thd->proc_info=0;
-      DBUG_RETURN(table);
+      if (check_lock_and_start_stmt(thd, table, lock_type))
+	table= 0;
+    }
+    else
+    {
+      if ((table->reginfo.lock_type= lock_type) != TL_UNLOCK)
+	if (!(thd->lock=mysql_lock_tables(thd,&table_list->table,1)))
+	  table= 0;
     }
-    if ((table->reginfo.lock_type=lock_type) != TL_UNLOCK)
-      if (!(thd->lock=mysql_lock_tables(thd,&table_list->table,1)))
-	  DBUG_RETURN(0);
   }
   thd->proc_info=0;
   DBUG_RETURN(table);
 }
 
+
 /*
-** Open all tables in list and locks them for read.
-** The lock will automaticly be freed by the close_thread_tables
+  Open all tables in list and locks them for read.
+  The lock will automaticly be freed by close_thread_tables()
 */
 
 int open_and_lock_tables(THD *thd,TABLE_LIST *tables)
@@ -1466,10 +1509,27 @@ int open_and_lock_tables(THD *thd,TABLE_LIST *tables)
   return 0;
 }
 
+
+/*
+  Lock all tables in list
+
+  SYNOPSIS
+    lock_tables()
+    thd			Thread handler
+    tables		Tables to lock
+
+  RETURN VALUES
+   0	ok
+   -1	Error
+*/
+
 int lock_tables(THD *thd,TABLE_LIST *tables)
 {
   TABLE_LIST *table;
-  if (tables && !thd->locked_tables)
+  if (!tables)
+    return 0;
+
+  if (!thd->locked_tables)
   {
     uint count=0;
     for (table = tables ; table ; table=table->next)
@@ -1486,10 +1546,9 @@ int lock_tables(THD *thd,TABLE_LIST *tables)
   {
     for (table = tables ; table ; table=table->next)
     {
-      int error;
-      if ((error=table->table->file->start_stmt(thd)))
+      if (check_lock_and_start_stmt(thd, table->table, table->lock_type))
       {
-	table->table->file->print_error(error,MYF(0));
+	ha_rollback_stmt(thd);
 	return -1;
       }
     }
@@ -1497,10 +1556,11 @@ int lock_tables(THD *thd,TABLE_LIST *tables)
   return 0;
 }
 
+
 /*
-** Open a single table without table caching and don't set it in open_list
-** Used by alter_table to open a temporary table and when creating
-** a temporary table with CREATE TEMPORARY ...
+  Open a single table without table caching and don't set it in open_list
+  Used by alter_table to open a temporary table and when creating
+  a temporary table with CREATE TEMPORARY ...
 */
 
 TABLE *open_temporary_table(THD *thd, const char *path, const char *db,
diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index c30d253828f..a5f14a507f7 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -87,9 +87,6 @@ THD::THD():user_time(0),fatal_error(0),last_insert_id_used(0),
   host_or_ip="unknown ip";
   locked=killed=count_cuted_fields=some_tables_deleted=no_errors=password=
     query_start_used=safe_to_cache_query=0;
-  pthread_mutex_lock(&LOCK_global_system_variables);
-  variables= global_system_variables;
-  pthread_mutex_unlock(&LOCK_global_system_variables);
   db_length=query_length=col_access=0;
   query_error=0;
   next_insert_id=last_insert_id=0;
@@ -129,19 +126,12 @@ THD::THD():user_time(0),fatal_error(0),last_insert_id_used(0),
   server_id = ::server_id;
   slave_net = 0;
   log_pos = 0;
-  server_status= SERVER_STATUS_AUTOCOMMIT;
-  update_lock_default= (variables.low_priority_updates ?
-			TL_WRITE_LOW_PRIORITY :
-			TL_WRITE);
-  options= thd_startup_options;
-  sql_mode=(uint) opt_sql_mode;
-  open_options=ha_open_options;
-  session_tx_isolation= (enum_tx_isolation) variables.tx_isolation;
   command=COM_CONNECT;
   set_query_id=1;
   db_access=NO_ACCESS;
   version=refresh_version;			// For boot
 
+  init();
   /* Initialize sub structures */
   bzero((char*) &mem_root,sizeof(mem_root));
   bzero((char*) &transaction.mem_root,sizeof(transaction.mem_root));
@@ -174,6 +164,48 @@ THD::THD():user_time(0),fatal_error(0),last_insert_id_used(0),
   }
 }
 
+
+/*
+  Init common variables that has to be reset on start and on change_user
+*/
+
+void THD::init(void)
+{
+  server_status= SERVER_STATUS_AUTOCOMMIT;
+  update_lock_default= (variables.low_priority_updates ?
+			TL_WRITE_LOW_PRIORITY :
+			TL_WRITE);
+  options= thd_startup_options;
+  sql_mode=(uint) opt_sql_mode;
+  open_options=ha_open_options;
+  pthread_mutex_lock(&LOCK_global_system_variables);
+  variables= global_system_variables;
+  pthread_mutex_unlock(&LOCK_global_system_variables);
+  session_tx_isolation= (enum_tx_isolation) variables.tx_isolation;
+}
+
+/*
+  Do what's needed when one invokes change user
+
+  SYNOPSIS
+    change_user()
+
+  IMPLEMENTATION
+    Reset all resources that are connection specific
+*/
+
+
+void THD::change_user(void)
+{
+  cleanup();
+  cleanup_done=0;
+  init();
+  hash_init(&user_vars, USER_VARS_HASH_SIZE, 0, 0,
+	    (hash_get_key) get_var_key,
+	    (hash_free_key) free_user_var,0);
+}
+
+
 /* Do operations that may take a long time */
 
 void THD::cleanup(void)
@@ -191,17 +223,21 @@ void THD::cleanup(void)
     close_thread_tables(this);
   }
   close_temporary_tables(this);
-#ifdef USING_TRANSACTIONS
-  if (opt_using_transactions)
+  hash_free(&user_vars);
+  if (global_read_lock)
+    unlock_global_read_lock(this);
+  if (ull)
   {
-    close_cached_file(&transaction.trans_log);
-    ha_close_connection(this);
+    pthread_mutex_lock(&LOCK_user_locks);
+    item_user_lock_release(ull);
+    pthread_mutex_unlock(&LOCK_user_locks);
+    ull= 0;
   }
-#endif
   cleanup_done=1;
   DBUG_VOID_RETURN;
 }
 
+
 THD::~THD()
 {
   THD_CHECK_SENTRY(this);
@@ -218,15 +254,13 @@ THD::~THD()
   }
   if (!cleanup_done)
     cleanup();
-  if (global_read_lock)
-    unlock_global_read_lock(this);
-  if (ull)
+#ifdef USING_TRANSACTIONS
+  if (opt_using_transactions)
   {
-    pthread_mutex_lock(&LOCK_user_locks);
-    item_user_lock_release(ull);
-    pthread_mutex_unlock(&LOCK_user_locks);
+    close_cached_file(&transaction.trans_log);
+    ha_close_connection(this);
   }
-  hash_free(&user_vars);
+#endif
 
   DBUG_PRINT("info", ("freeing host"));
   if (host != localhost)			// If not pointer to constant
diff --git a/sql/sql_class.h b/sql/sql_class.h
index 0f010b9de28..dba2ad130bf 100644
--- a/sql/sql_class.h
+++ b/sql/sql_class.h
@@ -461,6 +461,8 @@ public:
 
   THD();
   ~THD();
+  void init(void);
+  void change_user(void);
   void cleanup(void);
   bool store_globals();
 #ifdef SIGNAL_WITH_VIO_CLOSE
@@ -804,11 +806,9 @@ public:
    ha_rows deleted;
    uint num_of_tables;
    int error;
-   thr_lock_type lock_option;
    bool do_delete, transactional_tables, log_delayed, normal_tables;
  public:
-   multi_delete(THD *thd, TABLE_LIST *dt, thr_lock_type lock_option_arg,
-		uint num_of_tables);
+   multi_delete(THD *thd, TABLE_LIST *dt, uint num_of_tables);
    ~multi_delete();
    int prepare(List<Item> &list);
    bool send_fields(List<Item> &list,
@@ -829,7 +829,6 @@ public:
    ha_rows updated, found;
    List<Item> fields;
    List <Item> **fields_by_tables;
-   thr_lock_type lock_option;
    enum enum_duplicates dupl;
    uint num_of_tables, num_fields, num_updated, *save_time_stamps, *field_sequence;
    int error;
@@ -837,7 +836,7 @@ public:
  public:
    multi_update(THD *thd_arg, TABLE_LIST *ut, List<Item> &fs, 		 
 		enum enum_duplicates handle_duplicates,  
-		thr_lock_type lock_option_arg, uint num);
+		uint num);
    ~multi_update();
    int prepare(List<Item> &list);
    bool send_fields(List<Item> &list,
diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index c40e8a4e947..f21ff345845 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -28,7 +28,7 @@
 #include "sql_select.h"
 
 int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, ORDER *order,
-                 ha_rows limit, thr_lock_type lock_type, ulong options)
+                 ha_rows limit, ulong options)
 {
   int		error;
   TABLE		*table;
@@ -39,15 +39,13 @@ int mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, ORDER *order,
   ha_rows	deleted;
   DBUG_ENTER("mysql_delete");
 
-  if (!table_list->db)
-    table_list->db=thd->db;
   if ((thd->options & OPTION_SAFE_UPDATES) && !conds)
   {
     send_error(&thd->net,ER_UPDATE_WITHOUT_KEY_IN_SAFE_MODE);
     DBUG_RETURN(1);
   }
 
-  if (!(table = open_ltable(thd,table_list, lock_type)))
+  if (!(table = open_ltable(thd, table_list, table_list->lock_type)))
     DBUG_RETURN(-1);
   table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
   thd->proc_info="init";
@@ -176,9 +174,19 @@ cleanup:
     if (!log_delayed)
       thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
   }
-  if (transactional_table && ha_autocommit_or_rollback(thd,error >= 0))
-    error=1;
-  if (deleted)
+  if (transactional_table)
+  {
+    if (ha_autocommit_or_rollback(thd,error >= 0))
+      error=1;
+  }
+  /*
+    Only invalidate the query cache if something changed or if we
+    didn't commit the transacion (query cache is automaticly
+    invalidated on commit)
+  */
+  if (deleted &&
+      (!transactional_table ||
+       thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)))
   {
     query_cache_invalidate3(thd, table_list, 1);
   }
@@ -211,10 +219,9 @@ extern "C" int refposcmp2(void* arg, const void *a,const void *b)
 }
 
 multi_delete::multi_delete(THD *thd_arg, TABLE_LIST *dt,
-			   thr_lock_type lock_option_arg,
 			   uint num_of_tables_arg)
   : delete_tables (dt), thd(thd_arg), deleted(0),
-    num_of_tables(num_of_tables_arg), error(0), lock_option(lock_option_arg),
+    num_of_tables(num_of_tables_arg), error(0),
     do_delete(0), transactional_tables(0), log_delayed(0), normal_tables(0)
 {
   tempfiles = (Unique **) sql_calloc(sizeof(Unique *) * (num_of_tables-1));
@@ -553,8 +560,9 @@ int mysql_truncate(THD *thd, TABLE_LIST *table_list, bool dont_send_ok)
     if (!ha_supports_generate(table_type))
     {
       /* Probably InnoDB table */
-      DBUG_RETURN(mysql_delete(thd,table_list, (COND*) 0, (ORDER*) 0,
-			       HA_POS_ERROR, TL_WRITE, 0));
+      table_list->lock_type= TL_WRITE;
+      DBUG_RETURN(mysql_delete(thd, table_list, (COND*) 0, (ORDER*) 0,
+			       HA_POS_ERROR, 0));
     }
     if (lock_and_wait_for_table_name(thd, table_list))
       DBUG_RETURN(-1);
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index c83e21ddcae..71f570e4798 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -98,8 +98,7 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
 
 
 int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
-		 List<List_item> &values_list,enum_duplicates duplic,
-		 thr_lock_type lock_type)
+		 List<List_item> &values_list,enum_duplicates duplic)
 {
   int error;
   bool log_on= ((thd->options & OPTION_UPDATE_LOG) ||
@@ -114,6 +113,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
   List_iterator_fast<List_item> its(values_list);
   List_item *values;
   char *query=thd->query;
+  thr_lock_type lock_type = table_list->lock_type;
   DBUG_ENTER("mysql_insert");
 
   /*
@@ -200,8 +200,9 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
   {
     table->file->extra_opt(HA_EXTRA_WRITE_CACHE,
 			   thd->variables.read_buff_size);
-    table->file->extra_opt(HA_EXTRA_BULK_INSERT_BEGIN,
-			   thd->variables.bulk_insert_buff_size);
+    if (thd->variables.bulk_insert_buff_size)
+      table->file->extra_opt(HA_EXTRA_BULK_INSERT_BEGIN,
+			     thd->variables.bulk_insert_buff_size);
     table->bulk_insert= 1;
   }
 
@@ -266,10 +267,7 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
       info.copied=values_list.elements;
       end_delayed_insert(thd);
     }
-    if (info.copied || info.deleted)
-    {
-      query_cache_invalidate3(thd, table_list, 1);
-    }
+    query_cache_invalidate3(thd, table_list, 1);
   }
   else
   {
@@ -315,7 +313,15 @@ int mysql_insert(THD *thd,TABLE_LIST *table_list, List<Item> &fields,
     }
     if (transactional_table)
       error=ha_autocommit_or_rollback(thd,error);
-    if (info.copied || info.deleted)
+
+    /*
+      Only invalidate the query cache if something changed or if we
+      didn't commit the transacion (query cache is automaticly
+      invalidated on commit)
+    */
+    if ((info.copied || info.deleted) &&
+	(!transactional_table ||
+	 thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)))
     {
       query_cache_invalidate3(thd, table_list, 1);
     }
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 6424b97b422..0cebde364f7 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -99,7 +99,17 @@ static void init_signals(void)
 }
 #endif
 
-inline bool end_active_trans(THD *thd)
+static void unlock_locked_tables(THD *thd)
+{
+  if (thd->locked_tables)
+  {
+    thd->lock=thd->locked_tables;
+    thd->locked_tables=0;			// Will be automaticly closed
+    close_thread_tables(thd);			// Free tables
+  }
+}
+
+static bool end_active_trans(THD *thd)
 {
   int error=0;
   if (thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN |
@@ -698,7 +708,7 @@ pthread_handler_decl(handle_one_connection,arg)
 			(net->last_errno ? ER(net->last_errno) :
 			 ER(ER_UNKNOWN_ERROR)));
       send_error(net,net->last_errno,NullS);
-      thread_safe_increment(aborted_threads,&LOCK_status);
+      statistic_increment(aborted_threads,&LOCK_status);
     }
     
 end_thread:
@@ -911,7 +921,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
   thd->lex.select_lex.options=0;		// We store status here
   switch (command) {
   case COM_INIT_DB:
-    thread_safe_increment(com_stat[SQLCOM_CHANGE_DB],&LOCK_status);
+    statistic_increment(com_stat[SQLCOM_CHANGE_DB],&LOCK_status);
     if (!mysql_change_db(thd,packet))
       mysql_log.write(thd,command,"%s",thd->db);
     break;
@@ -923,7 +933,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
   }
   case COM_TABLE_DUMP:
     {
-      thread_safe_increment(com_other, &LOCK_status);
+      statistic_increment(com_other, &LOCK_status);
       slow_command = TRUE;
       uint db_len = *(uchar*)packet;
       uint tbl_len = *(uchar*)(packet + db_len + 1);
@@ -940,7 +950,10 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     }
   case COM_CHANGE_USER:
   {
-    thread_safe_increment(com_other,&LOCK_status);
+    thd->change_user();
+    clear_error_message(thd);			// If errors from rollback
+
+    statistic_increment(com_other,&LOCK_status);
     char *user=   (char*) packet;
     char *passwd= strend(user)+1;
     char *db=     strend(passwd)+1;
@@ -1020,7 +1033,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
   {
     char *fields;
     TABLE_LIST table_list;
-    thread_safe_increment(com_stat[SQLCOM_SHOW_FIELDS],&LOCK_status);
+    statistic_increment(com_stat[SQLCOM_SHOW_FIELDS],&LOCK_status);
     bzero((char*) &table_list,sizeof(table_list));
     if (!(table_list.db=thd->db))
     {
@@ -1055,7 +1068,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
 
   case COM_CREATE_DB:				// QQ: To be removed
     {
-      thread_safe_increment(com_stat[SQLCOM_CREATE_DB],&LOCK_status);
+      statistic_increment(com_stat[SQLCOM_CREATE_DB],&LOCK_status);
       char *db=thd->strdup(packet);
       // null test to handle EOM
       if (!db || !strip_sp(db) || check_db_name(db))
@@ -1073,7 +1086,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     }
   case COM_DROP_DB:				// QQ: To be removed
     {
-      thread_safe_increment(com_stat[SQLCOM_DROP_DB],&LOCK_status);
+      statistic_increment(com_stat[SQLCOM_DROP_DB],&LOCK_status);
       char *db=thd->strdup(packet);
       // null test to handle EOM
       if (!db || !strip_sp(db) || check_db_name(db))
@@ -1094,7 +1107,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     }
   case COM_BINLOG_DUMP:
     {
-      thread_safe_increment(com_other,&LOCK_status);
+      statistic_increment(com_other,&LOCK_status);
       slow_command = TRUE;
       if (check_global_access(thd, REPL_SLAVE_ACL))
 	break;
@@ -1118,7 +1131,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     }
   case COM_REFRESH:
     {
-      thread_safe_increment(com_stat[SQLCOM_FLUSH],&LOCK_status);
+      statistic_increment(com_stat[SQLCOM_FLUSH],&LOCK_status);
       ulong options= (ulong) (uchar) packet[0];
       if (check_global_access(thd,RELOAD_ACL))
 	break;
@@ -1130,7 +1143,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
       break;
     }
   case COM_SHUTDOWN:
-    thread_safe_increment(com_other,&LOCK_status);
+    statistic_increment(com_other,&LOCK_status);
     if (check_global_access(thd,SHUTDOWN_ACL))
       break; /* purecov: inspected */
     DBUG_PRINT("quit",("Got shutdown command"));
@@ -1153,7 +1166,7 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
   case COM_STATISTICS:
   {
     mysql_log.write(thd,command,NullS);
-    thread_safe_increment(com_stat[SQLCOM_SHOW_STATUS],&LOCK_status);
+    statistic_increment(com_stat[SQLCOM_SHOW_STATUS],&LOCK_status);
     char buff[200];
     ulong uptime = (ulong) (thd->start_time - start_time);
     sprintf((char*) buff,
@@ -1172,11 +1185,11 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     break;
   }
   case COM_PING:
-    thread_safe_increment(com_other,&LOCK_status);
+    statistic_increment(com_other,&LOCK_status);
     send_ok(net);				// Tell client we are alive
     break;
   case COM_PROCESS_INFO:
-    thread_safe_increment(com_stat[SQLCOM_SHOW_PROCESSLIST],&LOCK_status);
+    statistic_increment(com_stat[SQLCOM_SHOW_PROCESSLIST],&LOCK_status);
     if (!thd->priv_user[0] && check_global_access(thd,PROCESS_ACL))
       break;
     mysql_log.write(thd,command,NullS);
@@ -1185,13 +1198,13 @@ bool dispatch_command(enum enum_server_command command, THD *thd,
     break;
   case COM_PROCESS_KILL:
   {
-    thread_safe_increment(com_stat[SQLCOM_KILL],&LOCK_status);
+    statistic_increment(com_stat[SQLCOM_KILL],&LOCK_status);
     ulong id=(ulong) uint4korr(packet);
     kill_one_thread(thd,id);
     break;
   }
   case COM_DEBUG:
-    thread_safe_increment(com_other,&LOCK_status);
+    statistic_increment(com_other,&LOCK_status);
     if (check_global_access(thd, SUPER_ACL))
       break;					/* purecov: inspected */
     mysql_print_status(thd);
@@ -1291,7 +1304,7 @@ mysql_execute_command(void)
        !tables_ok(thd,tables)))
     DBUG_VOID_RETURN;
 
-  thread_safe_increment(com_stat[lex->sql_command],&LOCK_status);
+  statistic_increment(com_stat[lex->sql_command],&LOCK_status);
   switch (lex->sql_command) {
   case SQLCOM_SELECT:
   {
@@ -1355,6 +1368,8 @@ mysql_execute_command(void)
 	Normal select:
 	Change lock if we are using SELECT HIGH PRIORITY,
 	FOR UPDATE or IN SHARE MODE
+
+	TODO: Delete the following loop when locks is set by sql_yacc
       */
       TABLE_LIST *table;
       for (table = tables ; table ; table=table->next)
@@ -1561,6 +1576,7 @@ mysql_execute_command(void)
 	TABLE_LIST *table;
 	if (check_table_access(thd, SELECT_ACL, tables->next))
 	  goto error;				// Error message is given
+	/* TODO: Delete the following loop when locks is set by sql_yacc */
 	for (table = tables->next ; table ; table=table->next)
 	  table->lock_type= lex->lock_option;
       }
@@ -1802,18 +1818,13 @@ mysql_execute_command(void)
     }
     if (select_lex->table_list.elements == 1)
     {
-      res = mysql_update(thd,tables,
-			 select_lex->item_list,
-			 lex->value_list,
-			 select_lex->where,
-			 (ORDER *) select_lex->order_list.first,
-			 select_lex->select_limit,
-			 lex->duplicates,
-			 lex->lock_option);
-
-#ifdef DELETE_ITEMS
-      delete select_lex->where;
-#endif
+      res= mysql_update(thd,tables,
+			select_lex->item_list,
+			lex->value_list,
+			select_lex->where,
+			(ORDER *) select_lex->order_list.first,
+			select_lex->select_limit,
+			lex->duplicates);
     }
     else 
     {
@@ -1824,10 +1835,7 @@ mysql_execute_command(void)
 
       lex->sql_command=SQLCOM_MULTI_UPDATE;
       for (auxi=(TABLE_LIST*) tables, table_count=0 ; auxi ; auxi=auxi->next)
-      {
 	table_count++;
-	auxi->lock_type=TL_WRITE;
-      }
       if (select_lex->order_list.elements)
 	msg="ORDER BY";
       else if (select_lex->select_limit && select_lex->select_limit !=
@@ -1848,8 +1856,7 @@ mysql_execute_command(void)
 	  !setup_fields(thd,tables,lex->value_list,0,0,0) &&
 	  ! thd->fatal_error &&
 	  (result=new multi_update(thd,tables,select_lex->item_list,
-				   lex->duplicates, lex->lock_option,
-				   table_count)))
+				   lex->duplicates, table_count)))
       {
 	List <Item> total_list;
 	List_iterator <Item> field_list(select_lex->item_list);
@@ -1880,8 +1887,7 @@ mysql_execute_command(void)
     if (grant_option && check_grant(thd,INSERT_ACL,tables))
       goto error;
     res = mysql_insert(thd,tables,lex->field_list,lex->many_values,
-		       lex->duplicates,
-		       lex->lock_option);
+		       lex->duplicates);
     break;
   case SQLCOM_REPLACE:
     if (check_access(thd,INSERT_ACL | DELETE_ACL,
@@ -1892,8 +1898,7 @@ mysql_execute_command(void)
 
       goto error;
     res = mysql_insert(thd,tables,lex->field_list,lex->many_values,
-		       DUP_REPLACE,
-		       lex->lock_option);
+		       DUP_REPLACE);
     break;
   case SQLCOM_REPLACE_SELECT:
   case SQLCOM_INSERT_SELECT:
@@ -1928,8 +1933,8 @@ mysql_execute_command(void)
       net_printf(&thd->net,ER_INSERT_TABLE_USED,tables->real_name);
       DBUG_VOID_RETURN;
     }
-    tables->lock_type=TL_WRITE;		// update first table
     {
+      /* TODO: Delete the following loop when locks is set by sql_yacc */
       TABLE_LIST *table;
       for (table = tables->next ; table ; table=table->next)
 	table->lock_type= lex->lock_option;
@@ -1972,8 +1977,7 @@ mysql_execute_command(void)
     tables->grant.want_privilege=(SELECT_ACL & ~tables->grant.privilege);
     res = mysql_delete(thd,tables, select_lex->where,
                        (ORDER*) select_lex->order_list.first,
-                       select_lex->select_limit, lex->lock_option,
-                       select_lex->options);
+                       select_lex->select_limit, select_lex->options);
     break;
   }
   case SQLCOM_DELETE_MULTI:
@@ -2009,12 +2013,12 @@ mysql_execute_command(void)
 	net_printf(&thd->net,ER_NONUNIQ_TABLE,auxi->real_name);
 	goto error;
       }
-      auxi->lock_type=walk->lock_type=TL_WRITE;
+      walk->lock_type= auxi->lock_type;
       auxi->table= (TABLE *) walk;		// Remember corresponding table
     }
     if (add_item_to_list(new Item_null()))
     {
-      res = -1;
+      res= -1;
       break;
     }
     tables->grant.want_privilege=(SELECT_ACL & ~tables->grant.privilege);
@@ -2025,7 +2029,6 @@ mysql_execute_command(void)
     for (auxi=(TABLE_LIST*) aux_tables ; auxi ; auxi=auxi->next)
       auxi->table= ((TABLE_LIST*) auxi->table)->table;
     if (!thd->fatal_error && (result= new multi_delete(thd,aux_tables,
-						       lex->lock_option,
 						       table_count)))
     {
       res=mysql_select(thd,tables,select_lex->item_list,
@@ -2212,11 +2215,7 @@ mysql_execute_command(void)
       send_ok(&thd->net);
     break;
   case SQLCOM_UNLOCK_TABLES:
-    if (thd->locked_tables)
-    {
-      thd->lock=thd->locked_tables;
-      thd->locked_tables=0;			// Will be automaticly closed
-    }
+    unlock_locked_tables(thd);
     if (thd->options & OPTION_TABLE_LOCK)
     {
       end_active_trans(thd);
@@ -2227,12 +2226,7 @@ mysql_execute_command(void)
     send_ok(&thd->net);
     break;
   case SQLCOM_LOCK_TABLES:
-    if (thd->locked_tables)
-    {
-      thd->lock=thd->locked_tables;
-      thd->locked_tables=0;			// Will be automaticly closed
-      close_thread_tables(thd);
-    }
+    unlock_locked_tables(thd);
     if (check_db_used(thd,tables) || end_active_trans(thd))
       goto error;
     if (check_table_access(thd, LOCK_TABLES_ACL | SELECT_ACL, tables))
@@ -3258,6 +3252,37 @@ TABLE_LIST *add_table_to_list(Table_ident *table, LEX_STRING *alias,
   DBUG_RETURN(ptr);
 }
 
+/*
+  Set lock for all tables in current select level
+
+  SYNOPSIS:
+    set_lock_for_tables()
+    lock_type			Lock to set for tables
+
+  NOTE:
+    If lock is a write lock, then tables->updating is set 1
+    This is to get tables_ok to know that the table is updated by the
+    query
+*/
+
+void set_lock_for_tables(thr_lock_type lock_type)
+{
+  THD	*thd=current_thd;
+  bool for_update= lock_type >= TL_READ_NO_INSERT;
+  DBUG_ENTER("set_lock_for_tables");
+  DBUG_PRINT("enter", ("lock_type: %d  for_update: %d", lock_type,
+		       for_update));
+
+  for (TABLE_LIST *tables= (TABLE_LIST*) thd->lex.select->table_list.first ;
+       tables ;
+       tables=tables->next)
+  {
+    tables->lock_type= lock_type;
+    tables->updating=  for_update;
+  }
+  DBUG_VOID_RETURN;
+}
+
 
 /*
 ** This is used for UNION to create a new table list of all used tables
@@ -3301,7 +3326,6 @@ static bool create_total_list(THD *thd, LEX *lex, TABLE_LIST **result)
 	if (!cursor)
 	{
 	  /* Add not used table to the total table list */
-	  aux->lock_type= lex->lock_option;
 	  if (!(cursor = (TABLE_LIST *) thd->memdup((char*) aux,
 						    sizeof(*aux))))
 	  {
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index 0b440eb3060..c45ce5b6634 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -50,8 +50,7 @@ int mysql_update(THD *thd,
                  COND *conds,
                  ORDER *order,
 		 ha_rows limit,
-		 enum enum_duplicates handle_duplicates,
-		 thr_lock_type lock_type)
+		 enum enum_duplicates handle_duplicates)
 {
   bool 		using_limit=limit != HA_POS_ERROR;
   bool		used_key_is_modified, transactional_table, log_delayed;
@@ -66,7 +65,7 @@ int mysql_update(THD *thd,
   LINT_INIT(used_index);
   LINT_INIT(timestamp_query_id);
 
-  if (!(table = open_ltable(thd,table_list,lock_type)))
+  if (!(table = open_ltable(thd,table_list,table_list->lock_type)))
     DBUG_RETURN(-1); /* purecov: inspected */
   save_time_stamp=table->time_stamp;
   table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
@@ -316,9 +315,19 @@ int mysql_update(THD *thd,
     if (!log_delayed)
       thd->options|=OPTION_STATUS_NO_TRANS_UPDATE;
   }
-  if (transactional_table && ha_autocommit_or_rollback(thd, error >= 0))
-    error=1;
-  if (updated)
+  if (transactional_table)
+  {
+    if (ha_autocommit_or_rollback(thd, error >= 0))
+      error=1;
+  }
+  /*
+    Only invalidate the query cache if something changed or if we
+    didn't commit the transacion (query cache is automaticly
+    invalidated on commit)
+  */
+  if (updated &&
+      (!transactional_table ||
+       thd->options & (OPTION_NOT_AUTOCOMMIT | OPTION_BEGIN)))
   {
     query_cache_invalidate3(thd, table_list, 1);
   }
@@ -350,10 +359,12 @@ int mysql_update(THD *thd,
   Update multiple tables from join 
 ***************************************************************************/
 
-multi_update::multi_update(THD *thd_arg, TABLE_LIST *ut, List<Item> &fs, 		 
-			   enum enum_duplicates handle_duplicates,  thr_lock_type lock_option_arg, uint num)
-  : update_tables (ut), thd(thd_arg), updated(0), found(0), fields(fs), lock_option(lock_option_arg),
-    dupl(handle_duplicates), num_of_tables(num), num_fields(0), num_updated(0) , error(0),  do_update(false)
+multi_update::multi_update(THD *thd_arg, TABLE_LIST *ut, List<Item> &fs, 
+			   enum enum_duplicates handle_duplicates, 
+			   uint num)
+  : update_tables (ut), thd(thd_arg), updated(0), found(0), fields(fs),
+    dupl(handle_duplicates), num_of_tables(num), num_fields(0), num_updated(0),
+    error(0),  do_update(false)
 {
   save_time_stamps = (uint *) sql_calloc (sizeof(uint) * num_of_tables);
   tmp_tables = (TABLE **)NULL;
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 93532d013b5..92b49549a06 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -66,6 +66,7 @@ inline Item *or_or_concat(Item* A, Item* B)
   enum enum_tx_isolation tx_isolation;
   enum Item_cast cast_type;
   enum Item_udftype udf_type;
+  thr_lock_type lock_type;
   interval_type interval;
 }
 
@@ -263,6 +264,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %token	NO_SYM
 %token	NULL_SYM
 %token	NUM
+%token	OFFSET_SYM
 %token	ON
 %token	OPEN_SYM
 %token	OPTION
@@ -513,7 +515,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	type int_type real_type order_dir opt_field_spec lock_option
 	udf_type if_exists opt_local opt_table_options table_options
 	table_option opt_if_not_exists opt_var_type opt_var_ident_type
-	opt_temporary
+	opt_temporary 
 
 %type <ulong_num>
 	ULONG_NUM raid_types merge_insert_types
@@ -521,6 +523,9 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 %type <ulonglong_number>
 	ulonglong_num
 
+%type <lock_type>
+	replace_lock_option opt_low_priority insert_lock_option load_data_lock
+
 %type <item>
 	literal text_literal insert_ident order_ident
 	simple_ident select_item2 expr opt_expr opt_else sum_expr in_sum_expr
@@ -582,11 +587,11 @@ bool my_yyoverflow(short **a, YYSTYPE **b,int *yystacksize);
 	field_opt_list opt_binary table_lock_list table_lock varchar
 	references opt_on_delete opt_on_delete_list opt_on_delete_item use
 	opt_delete_options opt_delete_option
-	opt_outer table_list table_name opt_option opt_place opt_low_priority
+	opt_outer table_list table_name opt_option opt_place
 	opt_attribute opt_attribute_list attribute column_list column_list_id
 	opt_column_list grant_privileges opt_table user_list grant_option
 	grant_privilege grant_privilege_list
-	flush_options flush_option insert_lock_option replace_lock_option
+	flush_options flush_option
 	equal optional_braces opt_key_definition key_usage_list2
 	opt_mi_check_type opt_to mi_check_types normal_join
 	table_to_table_list table_to_table opt_table_list opt_as
@@ -2249,11 +2254,6 @@ order_clause:
 	ORDER_SYM BY 
         { 
 	  LEX *lex=Lex;
-	  if (lex->sql_command == SQLCOM_MULTI_UPDATE)
-	  {
-	    net_printf(&lex->thd->net, ER_WRONG_USAGE, "UPDATE", "ORDER BY");
-	    YYABORT;
-	  }	
 	  if (lex->select->olap != UNSPECIFIED_OLAP_TYPE)
 	  {
 	    net_printf(&lex->thd->net, ER_WRONG_USAGE,
@@ -2278,7 +2278,7 @@ order_dir:
 
 limit_clause:
 	/* empty */ {}
-	| LIMIT ULONG_NUM
+	| LIMIT 
 	  {
 	    LEX *lex=Lex;
 	    if (lex->select->olap != UNSPECIFIED_OLAP_TYPE)
@@ -2287,33 +2287,35 @@ limit_clause:
 		        "LIMIT");
 	      YYABORT;
 	    }
-	    SELECT_LEX *sel=Select;
-	    sel->select_limit= $2;
-	    sel->offset_limit= 0L;
 	  }
-	| LIMIT ULONG_NUM ',' ULONG_NUM
+	  limit_options
+	  ;
+
+limit_options:
+	ULONG_NUM
 	  {
-	    LEX *lex=Lex;
-	    if (lex->select->olap != UNSPECIFIED_OLAP_TYPE)
-	    {
-	      net_printf(&lex->thd->net, ER_WRONG_USAGE, "CUBE/ROLLUP",
-		        "LIMIT");
-	      YYABORT;
-	    }	      
-	    SELECT_LEX *sel=lex->select;
-	    sel->select_limit= $4;
-	    sel->offset_limit= $2;
-	  };
+            SELECT_LEX *sel= Select;
+            sel->select_limit= $1;
+            sel->offset_limit= 0L;
+	  }
+	| ULONG_NUM ',' ULONG_NUM
+	  {
+	    SELECT_LEX *sel= Select;
+	    sel->select_limit= $3;
+	    sel->offset_limit= $1;
+	  }
+	| ULONG_NUM OFFSET_SYM ULONG_NUM
+	  {
+	    SELECT_LEX *sel= Select;
+	    sel->select_limit= $1;
+	    sel->offset_limit= $3;
+	  }
+	;
 
 delete_limit_clause:
 	/* empty */
 	{
 	  LEX *lex=Lex;
-	  if (lex->sql_command == SQLCOM_MULTI_UPDATE)
-	  {
-	    net_printf(&lex->thd->net, ER_WRONG_USAGE, "DELETE", "LIMIT");
-	    YYABORT;
-	  }
 	  lex->select->select_limit= HA_POS_ERROR;
 	}
 	| LIMIT ulonglong_num
@@ -2448,7 +2450,13 @@ opt_temporary:
 */
 
 insert:
-	INSERT { Lex->sql_command = SQLCOM_INSERT; } insert_lock_option opt_ignore insert2 insert_field_spec;
+	INSERT { Lex->sql_command = SQLCOM_INSERT; } insert_lock_option
+	opt_ignore insert2 
+	{
+	  set_lock_for_tables($3);
+	}
+	insert_field_spec
+	;
 
 replace:
 	REPLACE
@@ -2457,17 +2465,23 @@ replace:
 	  lex->sql_command = SQLCOM_REPLACE;
 	  lex->duplicates= DUP_REPLACE;
 	}
-	replace_lock_option insert2 insert_field_spec;
+	replace_lock_option insert2
+	{
+	  set_lock_for_tables($3);
+	}
+	insert_field_spec
+	;
 
 insert_lock_option:
-	/* empty */	{ Lex->lock_option= TL_WRITE_CONCURRENT_INSERT; }
-	| LOW_PRIORITY	{ Lex->lock_option= TL_WRITE_LOW_PRIORITY; }
-	| DELAYED_SYM	{ Lex->lock_option= TL_WRITE_DELAYED; }
-	| HIGH_PRIORITY { Lex->lock_option= TL_WRITE; };
+	/* empty */	{ $$= TL_WRITE_CONCURRENT_INSERT; }
+	| LOW_PRIORITY	{ $$= TL_WRITE_LOW_PRIORITY; }
+	| DELAYED_SYM	{ $$= TL_WRITE_DELAYED; }
+	| HIGH_PRIORITY { $$= TL_WRITE; }
+	;	
 
 replace_lock_option:
-	opt_low_priority {}
-	| DELAYED_SYM	{ Lex->lock_option= TL_WRITE_DELAYED; };
+	opt_low_priority { $$= $1; }
+	| DELAYED_SYM	 { $$= TL_WRITE_DELAYED; };
 
 insert2:
 	INTO insert_table {}
@@ -2588,7 +2602,12 @@ update:
           lex->select->order_list.first=0;
           lex->select->order_list.next= (byte**) &lex->select->order_list.first;
         }
-        opt_low_priority opt_ignore join_table_list SET update_list where_clause opt_order_clause delete_limit_clause;
+        opt_low_priority opt_ignore join_table_list
+	SET update_list where_clause opt_order_clause delete_limit_clause
+	{
+	  set_lock_for_tables($3);
+	}
+	;
 
 update_list:
 	update_list ',' simple_ident equal expr
@@ -2603,8 +2622,8 @@ update_list:
 	  };
 
 opt_low_priority:
-	/* empty */	{ Lex->lock_option= current_thd->update_lock_default; }
-	| LOW_PRIORITY	{ Lex->lock_option= TL_WRITE_LOW_PRIORITY; };
+	/* empty */	{ $$= current_thd->update_lock_default; }
+	| LOW_PRIORITY	{ $$= TL_WRITE_LOW_PRIORITY; };
 
 /* Delete rows from a table */
 
@@ -2618,13 +2637,20 @@ delete:
 	  lex->select->order_list.first=0;
 	  lex->select->order_list.next= (byte**) &lex->select->order_list.first;
 	}
-	opt_delete_options single_multi	{};
+	opt_delete_options single_multi {}
+	;
 
 single_multi:
- 	FROM table_name where_clause opt_order_clause delete_limit_clause {}
+ 	FROM table_ident
+	{
+	  if (!add_table_to_list($2, NULL, 1, Lex->lock_option))
+	    YYABORT;
+	}
+	where_clause opt_order_clause
+	delete_limit_clause
 	| table_wild_list
 	  { mysql_init_multi_delete(Lex); }
-	   FROM join_table_list where_clause
+          FROM join_table_list where_clause
 	| FROM table_wild_list
 	  { mysql_init_multi_delete(Lex); }
 	  USING join_table_list where_clause;
@@ -2636,14 +2662,17 @@ table_wild_list:
 table_wild_one:
 	 ident opt_wild
 	 {
-	    if (!add_table_to_list(new Table_ident($1),NULL,1,TL_WRITE))
-	      YYABORT;
+	   if (!add_table_to_list(new Table_ident($1), NULL, 1,
+				  Lex->lock_option))
+	     YYABORT;
          }
 	 | ident '.' ident opt_wild
 	   {
-	     if (!add_table_to_list(new Table_ident($1,$3,0),NULL,1,TL_WRITE))
+	     if (!add_table_to_list(new Table_ident($1,$3,0), NULL, 1,
+				    Lex->lock_option))
 	      YYABORT;
-	   };
+	   }
+	;
 
 opt_wild:
 	/* empty */	{} 
@@ -2667,7 +2696,8 @@ truncate:
 	  lex->select->order_list.elements=0;
           lex->select->order_list.first=0;
           lex->select->order_list.next= (byte**) &lex->select->order_list.first;
-	  lex->lock_option= current_thd->update_lock_default; };
+	}
+	;
 
 opt_table_sym:
 	/* empty */
@@ -2916,7 +2946,8 @@ load:	LOAD DATA_SYM load_data_lock opt_local INFILE TEXT_STRING
 	{
 	  LEX *lex=Lex;
 	  lex->sql_command= SQLCOM_LOAD;
-	  lex->local_file= $4;
+	  lex->lock_option= $3;
+	  lex->local_file=  $4;
 	  if (!(lex->exchange= new sql_exchange($6.str,0)))
 	    YYABORT;
 	  lex->field_list.empty();
@@ -2946,9 +2977,9 @@ opt_local:
 	| LOCAL_SYM	{ $$=1;};
 
 load_data_lock:
-	/* empty */	{ Lex->lock_option= current_thd->update_lock_default; }
-	| CONCURRENT	{ Lex->lock_option= TL_WRITE_CONCURRENT_INSERT ; }
-	| LOW_PRIORITY	{ Lex->lock_option= TL_WRITE_LOW_PRIORITY; };
+	/* empty */	{ $$= current_thd->update_lock_default; }
+	| CONCURRENT	{ $$= TL_WRITE_CONCURRENT_INSERT ; }
+	| LOW_PRIORITY	{ $$= TL_WRITE_LOW_PRIORITY; };
 
 
 opt_duplicate:
@@ -3201,6 +3232,7 @@ keyword:
 	| NEW_SYM		{}
 	| NO_SYM		{}
 	| NONE_SYM		{}
+	| OFFSET_SYM		{}
 	| OPEN_SYM		{}
 	| PACK_KEYS_SYM		{}
 	| PASSWORD		{}