[BUG]: Async SQLite transaction will not rollback

### What version of `drizzle-orm` are you using?

0.28.6

### What version of `drizzle-kit` are you using?

0.19.13

### Describe the Bug

When using a synchronous `sqlite` driver with transactions, only a synchronous callback would apply the rollback.

Example:
```ts

// rollback will not undo the delete because of async callback
db.transaction(async (tx) => {
tx.delete(example).where(eq(example.id, exampleId)).run();
tx.rollback();
})

// rollback will undo
db.transaction((tx) => {
tx.delete(example).where(eq(example.id, exampleId)).run();
tx.rollback();
})
```

this was particularly difficult to debug because [the docs](https://orm.drizzle.team/docs/transactions) describe that this is a valid syntax for any driver (or rather does not specify a difference).

### Expected behavior

Either:
- (acceptable) Get a type error for the Promise callback
- (not ideal) Make the async callback able to rollback despite the synchronous behaviour of `sqlite`
- (best solution) Make async `sqlite` API work with transactions