Controlling a Transaction 1

[Slippenos]

I have a question concerning general syntax of a Transaction block. My application is written in ColdFusion and my table in question is InnoDB.

Currently we have a non-auto-incrementing PK. My DBA instructed me this cannot change for some time.

So to retrieve the next available key to use, I run a SELECT:

SELECT (id + 1) AS id
FROM tradingPartners
ORDER BY id DESC

I have code that uses this selection as the id to be inserted.

This will produce an error when two people attempt to insert a record into this table with identical keys.

The pseudo code for my transaction block should flow like so:

1.  Retrieve Max available ID (unique)
2.  Insert record using this ID
3.  Rollback if error occurs
4.  Alert user what happened

If there is a better way (perhaps looping until a successful INSERT- please call me out)

Does anyone know how to present this in logical SQL? Thanks in advance.

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[TonyGroves]

Your general approach is right. A few points though:

Your select statement should be something like:[tt]
SELECT MAX(id)+1 AS id FROM tradingpartners[/tt]

Regarding the possibility of two inserts getting the same id, you have a couple of options:

1. Put a write lock on the table before retrieving the id number, and release the lock after doing the insert. This ensures that two inserts cannot clash.

2. If an insert fails due to a duplicate key, then increase the id and try again. There's no need to inform the user of this technicality; it will only make them think that either it's their fault or there's something wrong with the system.

Another option might be to forget about reading the next id from the table; just pick a random number and see if it works; if not, try again. However, that might be a bit radical for your DBA.

 

[Slippenos]

Sounds good.

If in fact a user attempts to insert a record while the table is locked, will this produce an error?

Can you still run select statements against the locked table?

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[TonyGroves]

Attempting to access a table when another session has it locked won't produce an error, it will just delay the operation until the lock is released. You should of course release a lock as quickly as possible.

There are read locks and write locks. Read locks only block writes but allow reads. Write locks block reading and writing, which is what you would need. The syntax is described in the manual.

 

[Slippenos]

It sounds like the best alternative is a write-lock on the table, and release it when its completed the INSERT.

Will it work when two users attempt an INSERT at the same time, still? How will MySQL add one more to the 'id' if two users INSERT at once?

[blue]Go to work to learn. Don't go to work to earn.[/blue]

 

[TonyGroves]

Any program that uses the locking system described won't have that problem. You would be guaranteed that the id you have got will still be valid when the insert is done.

 

[Bertiethedog]

I had this problem when I did a load of code for a courier recently.

My technique was to create a blank record with just the ID field & an issue date.

If the record was subsequently abandoned the ID would be reused after 2 days.

It would also be possible to create a semaphore file.

Richard