Transactions in MySql

[maliary]

 

Ok,

 

I am using MySQL 4.0.26 and I have this problem.

 

I have an intranet application running in a hospital setting, a user can make a malaria,flu or chicken pox test.All these tests are in their own respective tables.

 

1.User A wants to make 2 tests for malaria and flu

2.User B wants to make one test for flu only.

 

User A makes a test for malaria then proceeds to making one for flu, before user A is finished with the Flu test, User B goes ahead and makes a Flu test for a different patient.

 

The patient lab number is generated in code outside MySQL, is  unique and is the same in all the 3 test tables for one patient.

 

So, User A's patient number is 1001, it gets saved as this on the malaria table, however before its saved on the Flu table due to  User A's delay, User B inserts patient number 1002 in the flu table.

 

1001 is saved after 1002 in the Flu table. And since the lab number is generated by incrementing from the last row,then it picks from 1001 and the next is 1002 which brings an error during insert due to two similar patient numbers which are supposed to be unique.

 

So, is it possible to lock the insert statement from executing until the first instance is inserted and complete then now save next row to prevent this kind of errors ?

[bluejay002]

i could hardly get what you want to say. but in terms of inserting rows in mysql with an autoincrement, it will be incremented for you so you do not need to track it which might cause ambiguity and the same trouble you had.

 

i mean why generate that patient number later based on the autoincrement number assigned wen saved? if not possible, then you might want to retain that autoincrement, use a different field to store that manually (or whatever source it has other than the database engine) generated patient number. otherwise, its update thatyou might want.

[PFMaBiSmAd]

When using an auto_increment, get the value that was actually used after you perform an insert.

 

It sounds like you are attempting to retrieve the last value and add one to it and as you have found, this won't work when there are concurrent operations.

 

If you are expecting the "lab numbers" to be consecutive for any user, insert all the rows you need in one query when the first row is inserted (or when the patient number is created). Then UPDATE the 2nd, 3rd... rows when the data becomes available.

 

But, I think you are making this harder than it needs to be. If user A gets lab number 1001 and 1003 and user B get lab number 1002, the database does not care (they are just numbers) and neither should you or your script.

[maliary]

But, I think you are making this harder than it needs to be. If user A gets lab number 1001 and 1003 and user B get lab number 1002, the database does not care (they are just numbers) and neither should you or your script.

 

this is the script that picks the last row from the flu_table, it compares this with the other two tables, gets the largest lab number and increments from there.

 

$sql3="SELECT patient_nr,create_time FROM flu_table WHERE batch_nr LIKE '%".$branchID."' ORDER BY batch_nr DESC "; 

 

So, if 1002 is inserted after 1003 the script generating the next lab number will get 1003 which will conflict with the previous 1003 as it's already been inserted.

 

[bluejay002]

i prefer making it an autoincrement provided by mysql... no hassle at all. if you are still on the development phase, you can change some rules early on that can make things easier on your side.