locking table issues (Again)

locking table issues (Again)

Postby 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
ben
 
Posts: 119
Joined: Wed 17 Nov 2004 19:48

Postby 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.
Antaeus
 
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Postby 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.
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby 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.
ben
 
Posts: 119
Joined: Wed 17 Nov 2004 19:48

Postby 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!!).
GEswin
 
Posts: 186
Joined: Wed 03 Nov 2004 16:57
Location: Spain

Postby ben » Sat 25 Feb 2006 18:57

thats good :) thanks
ben
 
Posts: 119
Joined: Wed 17 Nov 2004 19:48

Postby 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?
ben
 
Posts: 119
Joined: Wed 17 Nov 2004 19:48

Postby 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.
swierzbicki
 
Posts: 451
Joined: Wed 19 Jan 2005 09:59

Postby 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.
Antaeus
 
Posts: 2098
Joined: Tue 14 Feb 2006 10:14

Postby 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?
ben
 
Posts: 119
Joined: Wed 17 Nov 2004 19:48

Postby Antaeus » Wed 01 Mar 2006 09:34

You are right. From TCP/IP point of view these are different connections.
Antaeus
 
Posts: 2098
Joined: Tue 14 Feb 2006 10:14


Return to MySQL Data Access Components