locking mysql table

[Anon]

This is the situation.

The user enters some details in the form and submits the same.While submitting i am selecting a control id from the table called control.Here i am storing this id value in the transaction table in addition to the user entries.After inserting this record in the transaction table i am updating the id value in the control table as id+1.

Is there any chance of getting the duplicate id values in the transaction table when multiple users submits at a same time.Does mysql maintains table level lock internally during updation.If not what will be the best way to have lock on table during updation.

Your help will be much appreciated.

Thanks in advance.
srini

[[deleted]]

If you want unique numbers, use a unique or primary key.

[[deleted]]

... and make it an auto_increment column so you allways get a new value automatically.

[Anon]

Thanks for your suggestion.
In general is it adviceable to lock a table during transaction.If so could you explain the way to do that.

Thanks
srini

[[deleted]]

Note, There is a big difference between doing a transactionon a database and "using transactions"
MySQL does not (really) support transactions, so I'll continue to talk about "queries"

It depends on what you want to do with a table.

A lock is only used to make sure nobody else can change the records untill you are finished doing your queries, and you release the lock.

This also means that if you take a long time to finish your queries, everybody else will have to wait. Not good in a web environment.

Locking is good if you want to insert a lot of records at once. It can be much faster with a lock than without a lock.

Locking should not be used to generate unique numbers (as you originally asked)
But it can be used to mark a record as "in use"
You'd lock the table for writing, choose a record that's not in use, mark it as "used" and release the lock. That way you can be sure nobody else can choose that same record and use it.

[Anon]

Good informations.
Many thanks.

srini