You can insert into an auto-increment column and specify a value. This is fine; it simply overrides the auto-increment generator.
If you try to insert a value of NULL or 0 or DEFAULT
, or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator.
So, it's fine to INSERT INTO table1 SELECT * FROM table2
(by the way, you don't need the parentheses). This means that the id values in table2
will be copied verbatim, and table1
will not generate new values.
If you want table1
to generate new values, you can't do SELECT *
. Either you use null or 0 for the id column:
INSERT INTO table1 SELECT 0, col1, col2, col3, ... FROM table2;
Or else you omit the column from both the INSERT statement's column list and the SELECT statement's select-list:
-- No id in either case:
INSERT INTO table1 (col1, col2, col3) SELECT col1, col2, col3, ... FROM table2;
Before you ask, there is no syntax in SQL for "select * except for one column". You have to spell out the full list of column names you want to insert.
This is not a deadlock.
One transaction will simply block -- waiting to acquire the lock. The other transaction will proceed. As soon as the other transaction is done -- either by commit
or rollback
, the first transaction will proceed.
A deadlock happens when a transaction has acquired a lock on object A, and attempts to acquire a lock on object B at the same time as another transaction has already acquired a lock on object B and is attempting to acquire a lock on object A. Both transactions will then block, waiting on each other. That's the definition of a deadlock: two transactions blocked waiting on a lock that the other has.
Best Answer
UPDATEs and DELETEs will at very least apply a row level lock that prevents other transactions from affecting that row until the locking transaction is complete. If the LOCK takes 'too' long to resolve the transaction it can result in a TIMEOUT or a DEADLOCK scenario. Transactions are applied in a synchronous manner based upon when the command reaches the database.