locking table issues (Again)

ben · Post by **ben** » Fri 24 Feb 2006 12:33

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

Antaeus · Post by **Antaeus** » Fri 24 Feb 2006 15:53

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

GEswin · Post by **GEswin** » Fri 24 Feb 2006 21:26

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.

ben · Post by **ben** » Sat 25 Feb 2006 03:37

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.

GEswin · Post by **GEswin** » Sat 25 Feb 2006 10:41

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!!).

ben · Post by **ben** » Sat 25 Feb 2006 18:57

thats good

thanks

ben · Post by **ben** » Sun 26 Feb 2006 17:23

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?

swierzbicki · Post by **swierzbicki** » Mon 27 Feb 2006 08:41

Hello Ben,

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

Antaeus · Post by **Antaeus** » Mon 27 Feb 2006 10:12

> 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.

ben · Post by **ben** » Tue 28 Feb 2006 21:08

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?

Antaeus · Post by **Antaeus** » Wed 01 Mar 2006 09:34

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