Professional Web Applications Themes

Problem with subquery and LOCK TABLES - MySQL

Hi all, a few days ago Adam posted the following solution to my question about how to find the lowest free number in an incomplete list of integer keys:   It does exactly what I wanted it to do, but: When I lock the table with "LOCK TABLES test WRITE" beforehand, and then execute the above query, I get an error saying (from memory): "...Error HY000: tables were not locked with LOCK TABLES". Trying to find out about the cause of this, I found that the following part of the query executes successfully (again from memory): (select id+1 as incid ...

  1. #1

    Default Problem with subquery and LOCK TABLES

    Hi all,

    a few days ago Adam posted the following solution to my question about
    how to find the lowest free number in an incomplete list of integer
    keys:
     

    It does exactly what I wanted it to do, but: When I lock the table
    with "LOCK TABLES test WRITE" beforehand, and then execute the above
    query, I get an error saying (from memory): "...Error HY000: tables
    were not locked with LOCK TABLES".
    Trying to find out about the cause of this, I found that the following
    part of the query executes successfully (again from memory):

    (select id+1 as incid from test) a where a.incid not in (select id
    from test);

    Perhaps this is a stupid test, but as soon as the first part "select
    min(a.incid) as newid from" is inserted again, the above error
    occurs.
    Even a new start of the mysql server (to be sure no locks where left
    over somehow) did not change this behaviour.
    Am I wrong if I expect table locking to work while subqueries are
    executed? What is happening here?

    Thanks,

    Pascal.

    anschouwe@yahoo.com Guest

  2. #2

    Default Re: Problem with subquery and LOCK TABLES

    On 13 Jul, 08:02, com wrote: 
    >
    > It does exactly what I wanted it to do, but: When I lock the table
    > with "LOCK TABLES test WRITE" beforehand, and then execute the above
    > query, I get an error saying (from memory): "...Error HY000: tables
    > were not locked with LOCK TABLES".
    > Trying to find out about the cause of this, I found that the following
    > part of the query executes successfully (again from memory):
    >
    > (select id+1 as incid from test) a where a.incid not in (select id
    > from test);
    >
    > Perhaps this is a stupid test, but as soon as the first part "select
    > min(a.incid) as newid from" is inserted again, the above error
    > occurs.
    > Even a new start of the mysql server (to be sure no locks where left
    > over somehow) did not change this behaviour.
    > Am I wrong if I expect table locking to work while subqueries are
    > executed? What is happening here?
    >
    > Thanks,
    >
    > Pascal.[/ref]

    Try giving each instance of the table an alias and lock he aliases.

    Captain Guest

  3. #3

    Default Re: Problem with subquery and LOCK TABLES

    Am 13.07.2007, 10:24 Uhr, schrieb Captain Paralytic
    <com>:
     
    >> where a.incid not in (select id from test); 
    >> number.
    >>
    >> It does exactly what I wanted it to do, but: When I lock the table
    >> with "LOCK TABLES test WRITE" beforehand, and then execute the above
    >> query, I get an error saying (from memory): "...Error HY000: tables
    >> were not locked with LOCK TABLES".
    >> Trying to find out about the cause of this, I found that the following
    >> part of the query executes successfully (again from memory):
    >>
    >> (select id+1 as incid from test) a where a.incid not in (select id
    >> from test);
    >>
    >> Perhaps this is a stupid test, but as soon as the first part "select
    >> min(a.incid) as newid from" is inserted again, the above error
    >> occurs.
    >> Even a new start of the mysql server (to be sure no locks where left
    >> over somehow) did not change this behaviour.
    >> Am I wrong if I expect table locking to work while subqueries are
    >> executed? What is happening here?
    >>
    >> Thanks,
    >>
    >> Pascal.[/ref]
    >
    > Try giving each instance of the table an alias and lock he aliases.
    >[/ref]

    I tried it in the following way:

    lock tables test write, test as a write;

    but I still get:

    "ERROR 1100 (HY000): table test was not locked with lock tables"
    (translated from german)

    What am I getting wrong here? :-/

    Pascal.
    P.A. Guest

  4. #4

    Default Re: Problem with subquery and LOCK TABLES

    P.A. wrote: 
    >>
    >> Try giving each instance of the table an alias and lock he aliases.
    >>[/ref]
    >
    > I tried it in the following way:
    >
    > lock tables test write, test as a write;
    >
    > but I still get:
    >
    > "ERROR 1100 (HY000): table test was not locked with lock tables"
    > (translated from german)
    >
    > What am I getting wrong here? :-/
    >
    > Pascal.[/ref]

    Did you change your query to use the aliases instead of the table names?
    http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html


    Paul Guest

  5. #5

    Default Re: Problem with subquery and LOCK TABLES

    Am 14.07.2007, 11:09 Uhr, schrieb Paul Lautman
    <com>:
     
    >>
    >> I tried it in the following way:
    >>
    >> lock tables test write, test as a write;
    >>
    >> but I still get:
    >>
    >> "ERROR 1100 (HY000): table test was not locked with lock tables"
    >> (translated from german)
    >>
    >> What am I getting wrong here? :-/
    >>
    >> Pascal.[/ref]
    >
    > Did you change your query to use the aliases instead of the table names?
    > http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html
    >
    >[/ref]
    Ok, got it, but don't understand exactly:

    Locking: LOCK TABLES test WRITE, test AS a WRITE;
    Query: SELECT min(a.incid) AS newid FROM (SELECT id+1 AS incid FROM test)
    a WHERE a.incid NOT IN (SELECT id FROM test a);

    It seems to work, but also does this ('a' replaced by 'b'):

    Locking: LOCK TABLES test WRITE, test AS a WRITE, test AS b WRITE;
    Query: SELECT min(a.incid) AS newid FROM (SELECT id+1 AS incid FROM test)
    a WHERE a.incid NOT IN (SELECT id FROM test b);

    Of course I read the manual before, but from the example I concluded that
    the table base name ('t') can still be used:

    (From manual):
    mysql> LOCK TABLE t WRITE, t AS t1 WRITE;
    mysql> INSERT INTO t SELECT * FROM t;
    ERROR 1100: Table 't' was not locked with LOCK TABLES
    mysql> INSERT INTO t SELECT * FROM t AS t1;

    So is this something about the use of subqueries combined with aliases in
    my example?

    Thanks a lot anyway!

    Pascal.

    --
    Erstellt mit Operas revolutionärem E-Mail-Modul: http://www.opera.com/mail/
    P.A. Guest

Similar Threads

  1. Problem with JOIN and LOCK TABLES
    By Andre in forum MySQL
    Replies: 3
    Last Post: April 12th, 11:45 AM
  2. correlated subquery problem
    By SethosLA@gmail.com in forum MySQL
    Replies: 3
    Last Post: October 9th, 07:41 PM
  3. mySQL subquery problem
    By rmorgan in forum Coldfusion Database Access
    Replies: 7
    Last Post: October 30th, 07:06 AM
  4. problem with file lock
    By Ralf in forum Windows Server
    Replies: 3
    Last Post: August 25th, 08:14 PM
  5. Union as subquery problem
    By Dror in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 30th, 06:57 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139