MySQL transaction and triggers

Hey guys, here is one I am not able to figure out. We have a table in database, where PHP inserts records. I created a trigger to compute a value to be inserted as well. The computed value should be unique. However it happens from time to time that I have exact same number for few rows in the table. The number is combination of year, month and day and a number of the order for that day. I thought that single operation of insert is atomic and table is locked while transaction is in progress. I need the computed value to be unique...The server is version 5.0.88. Server is Linux CentOS 5 with dual core processor.

Here is the trigger:

CREATE TRIGGER bi_order_data BEFORE INSERT ON order_data
FOR EACH ROW BEGIN
  SET NEW.auth_code = get_auth_code();
END;

Corresponding routine looks like this:

CREATE FUNCTION `get_auth_code`() RETURNS bigint(20)
BEGIN
    DECLARE my_auth_code, acode BIGINT;
    SELECT MAX(d.auth_code) INTO my_auth_code
        FROM orders_data d
        JOIN orders o ON (o.order_id = d.order_id)
        WHERE DATE(NOW()) = DATE(o.date);

    IF my_auth_code IS NULL THEN
        SET acode = ((DATE_FORMAT(NOW(), "%y%m%d")) + 100000) * 10000 + 1;
    ELSE
        SET acode = my_auth_code 开发者_如何学运维+ 1;
    END IF;
    RETURN acode;
END

---

I thought that single operation of insert is atomic and table is locked while transaction is in progress

Either table is locked (MyISAM is used) or records may be locked (InnoDB is used), not both.

Since you mentioned "transaction", I assume that InnoDB is in use. One of InnoDB advantages is absence of table locks, so nothing will prevent many triggers' bodies to be executed simultaneously and produce the same result.