I'm looking to add about 7 million rows to a live production database table that gets 1-2 writes per second. Can I do this without locking the database for writes? I think so because the table uses InnoDB?
我希望向一个实时生产数据库表添加大约700万行，该表每秒写1-2次。我可以不锁定数据库进行写操作吗?因为这个表格使用了InnoDB?
Are there other considerations or do I just write the insert statement and let it rip?
还有其他需要考虑的问题吗?还是我只是写个insert语句然后让它被撕掉?
1 个解决方案
#1
3
If you're using InnoDB, you don't need to do anything special. Just run your inserts. InnoDB uses row level locking for these situations, it will not lock the entire table.
如果您正在使用InnoDB，则不需要做任何特别的事情。运行您的插入。InnoDB在这些情况下使用行级别锁定，它不会锁定整个表。
Of course your performance could still take a hit due to the parallel work.
当然，由于并行工作，您的性能仍然会受到影响。
To answer your other question:
回答你的另一个问题:
"One confusion about transactions: If I am working on transaction A and a stack of writes B come in, do those writes get processed after I commit my transaction"
关于事务的一个困惑是:如果我正在处理事务A而一堆写B进来了，那么在我提交事务后这些写会被处理吗?
In general, no. It will not need to wait. This does depend if you are working within the same keyspace or not, and also what isolation level you are working within.
一般来说,没有。它不需要等待。这取决于您是否在同一个密钥空间内工作，以及您在内部工作的隔离级别。
#1
3
If you're using InnoDB, you don't need to do anything special. Just run your inserts. InnoDB uses row level locking for these situations, it will not lock the entire table.
如果您正在使用InnoDB，则不需要做任何特别的事情。运行您的插入。InnoDB在这些情况下使用行级别锁定，它不会锁定整个表。
Of course your performance could still take a hit due to the parallel work.
当然，由于并行工作，您的性能仍然会受到影响。
To answer your other question:
回答你的另一个问题:
"One confusion about transactions: If I am working on transaction A and a stack of writes B come in, do those writes get processed after I commit my transaction"
关于事务的一个困惑是:如果我正在处理事务A而一堆写B进来了，那么在我提交事务后这些写会被处理吗?
In general, no. It will not need to wait. This does depend if you are working within the same keyspace or not, and also what isolation level you are working within.
一般来说,没有。它不需要等待。这取决于您是否在同一个密钥空间内工作，以及您在内部工作的隔离级别。