Devart Forums

Hello,

I have 2 queries, one TMyQuery and one TMyScript

MyQuery.SQL.Text := 'LOCK TABLES history WRITE;'

MyScript.SQL.Add('INSERT history SET.... blah blah');
MyScript.SQL.Add('SET @last_id=LAST_INSERT_ID();';
MyScript.SQL.Add('INSERT users SET blah blah blah';

MyQuery.Execute;
MyScript.Execute;

MyQuery.SQL.Text := 'UNLOCK TABLES;';


this code returns me "TABLE 'users' was not locked with LOCK TABLES';

why I have to lock a table that I dont want to lock? Any ideas?

Thanks

This is MySQL-specific question but not MyDAC. Please try to ask this question on forums dedicated to MySQL Server.

Doens't make really much sense to lock a table if your purpose is to insert records... Locking is more oriented to update a record and make sure noone else updates or uses it at that moment.

the example was simple, if you see Im getting LAST_INSERT_ID I need it to store in another table. if another user adds a record, i will lose the last inserted id.

Hi,

Last_insert_id is on connection based, so each user will have his own last_insert_id after doing an insert. Don't worry about this (It would be a pain if it wasn't done like this!!).

thats good thanks

but I have another question,

if I have an application with one connection to mysql and I have 2 background threads adding records (INSERT) how can I prevent that I will have collisions between LAST_INSERT_ID of 2 threads? LOCK TABLE works also per connection, correct?

The solution is to have 2 connections?

Hello Ben,

MyDAC components are not thread safe, it will be safer to use for each thread a new MyConnection.

> I will have collisions between LAST_INSERT_ID of 2 threads?
You should always use one MyConnection per thread, because using one MyConnection in different threads may cause errors. We mean that thread-safe components are components that can work in multithread application. You can read about it in corresponding topic of MyDAC FAQ.

> LOCK TABLE works also per connection, correct?
LOCK TABLE works for whole database. Other sessions will "know" that the table is locked. Read MySQL Reference for detailed information.

> The solution is to have 2 connections?
Yes.

yes, but if my connection is on internet DB not localhost, this will make a very slow app. because every query will connect to the internet.... am I wrong?

You are right. From TCP/IP point of view these are different connections.