MySQL Multi-User / Table Locking

[TURKINATOR]

Hi guys, Im using MySQL 5.0.

Here is my two tables.

CREATE TABLE IF NOT EXISTS Invoice
(
InvoiceID	             MEDIUMINT	NOT NULL	AUTO_INCREMENT,
InvoiceDate	DATE		NOT NULL,
ClientID	             VARCHAR(7)	NOT NULL,
Total		INTEGER(10,2)	NOT NULL,
AmountPaid	INTEGER(10,2)	NOT NULL,
PRIMARY KEY	(InvoiceID),
FOREIGN KEY	(ClientID)	             REFERENCES	Clients(ClientID)
);

CREATE TABLE IF NOT EXISTS Transaction
(
InvoiceID	             MEDIUMINT	NOT NULL,
ProductID	VARCHAR(7)	NOT NULL,
QTY		INTEGER(2)	NOT NULL,
FOREIGN KEY	(InvoiceID) 	REFERENCES	Invoice(InvoiceID),
FOREIGN KEY	(ProductID)	REFERENCES	Products(ProductID)
);

The purpose of this is to record 1 InvoiceID to refer to multipul transaction items. So heres the problem. What if 2 transactions occured, then the below code will not work.

INSERT INTO Invoice (InvoiceDate, ClientID, Total, AmountPaid)
VALUES (NOW(), 'OZT0001', 130.00, 50.00);

INSERT INTO Transaction (InvoiceID, ClientID, ProductID, QTY, Total)
VALUES (LAST_INSERT_ID(), 'RAM0001', 2);

Is there a better way to construct this or any advise on how to go about this?

[TURKINATOR]

Heres an idea, why don't you lock the table and then allow the transaction to complete then unlock it for the next invoice to process. Champion.

Never mind me! Just trying to cover up my stupidity.

[TURKINATOR]

Ok, here is what I have come up with.

INSERT INTO Invoice (InvoiceDate, ClientID, Total, AmountPaid)
VALUES (NOW(), 'OZT0001', 130.00, 50.00);

LOCK TABLES Invoice READ, Transaction WRITE;

INSERT INTO Transaction (InvoiceID, ProductID, QTY)
VALUES (LAST_INSERT_ID(), 'RAM0001', 2);

UNLOCK TABLES;

INSERT INTO Invoice (InvoiceDate, ClientID, Total, AmountPaid)
VALUES (NOW(), 'OZT0002', 180.00, 20.00);

LOCK TABLES Invoice READ, Transaction WRITE;

INSERT INTO Transaction (InvoiceID, ProductID, QTY)
VALUES (LAST_INSERT_ID(), 'CPU0001', 3);

UNLOCK TABLES;

Although this works great, now my question is, what if when someone encounters the locked table for inserting, then that becomes wasted. I know DELAYED is an option, but because I am using LAST_INSERT_ID() I thought that it may be a problem.

Once again I ask if there is a better way to approch what I am trying to do or anything that may helpme in this situation.

Thanks in advance.

[TURKINATOR]

No response? If more information is required I can provide it. MySQL DB is the back-end and VB .NET 2005 is used for the front end.

[szlamany]

Maybe we all don't have a lot of experience with MySQL or with MySQL in a multi-user environment.

If you asked about MS SQL - I could answer your questions

Maybe edit your first post and change the TITLE to

MySQL LOCKING with MULTI-USERS

and see if anyone comes along...

[TURKINATOR]

Thanks for the tip. Changed the topic, hope it works.

So how do you go about it in MS SQL? Create a procedure?

[szlamany]

In MS SQL there is an @@IDENTITY and a SCOPE_IDENTITY function available.

These return the "last identity value" inserted by the "connection" itself - so multi-user concerns go away.

No locking required for this - which of course is the best method. Locking causes problems in a multi-user system so it's avoided at all costs.

Also - look at post #6 in this thread

http://www.vbforums.com/showthread.p...93#post2771093

interesting way to do this without an identity column - in a multi-user environment - and without locking.

[TURKINATOR]

When I searched up @@IDENTITY with MySQL it is said that MySQL uses the function LAST_INSERT_ID() *Which I was using* and that its based on the connection.

I put it to the test, opened up my application twice, one each with different user. Inserted into records in app1 then app2. After running the query SELECT LAST_INSERT_ID() I found that it differed from each user.

What I am getting to is that LAST_INSERT_ID() is based on that users insert and not the database. So, no table locking is required and now my headache is gone.

I hope this may in future be a lesson for all. Test before questioning.

Thanks for pointing me in the right direction szlamany